-- =====================================================================
-- Prudas Technologies — initial schema
-- All tables use bigint identity keys, explicit NOT NULL where the domain
-- requires it, and indexes on the columns the public API actually filters
-- or sorts by. No column ever stores plaintext secrets.
-- =====================================================================

CREATE TABLE admin_users (
    id                     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username               VARCHAR(100)  NOT NULL UNIQUE,
    email                  VARCHAR(255)  NOT NULL UNIQUE,
    password_hash          VARCHAR(255)  NOT NULL,
    role                   VARCHAR(20)   NOT NULL,
    failed_login_attempts  INT           NOT NULL DEFAULT 0,
    locked_until           TIMESTAMPTZ,
    enabled                BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at             TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at             TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE TABLE services (
    id             BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    slug           VARCHAR(160)  NOT NULL UNIQUE,
    title          VARCHAR(200)  NOT NULL,
    tagline        VARCHAR(255),
    summary        TEXT,
    content        TEXT,
    hero_image_url VARCHAR(500),
    contact_email  VARCHAR(255),
    display_order  INT           NOT NULL DEFAULT 0,
    published      BOOLEAN       NOT NULL DEFAULT TRUE,
    show_in_nav    BOOLEAN       NOT NULL DEFAULT FALSE,
    show_on_home   BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at     TIMESTAMPTZ   NOT NULL DEFAULT now()
);
CREATE INDEX idx_services_published_order ON services (published, display_order);

CREATE TABLE service_process_steps (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    service_id  BIGINT       NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    step_order  INT          NOT NULL,
    title       VARCHAR(150) NOT NULL,
    description TEXT,
    icon_url    VARCHAR(500)
);
CREATE INDEX idx_service_steps_service ON service_process_steps (service_id, step_order);

CREATE TABLE blog_posts (
    id              BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    slug            VARCHAR(220)  NOT NULL UNIQUE,
    title           VARCHAR(250)  NOT NULL,
    excerpt         TEXT,
    content         TEXT,
    cover_image_url VARCHAR(500),
    author_name     VARCHAR(150),
    published       BOOLEAN       NOT NULL DEFAULT TRUE,
    published_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT now()
);
CREATE INDEX idx_blog_published_date ON blog_posts (published, published_at DESC);

CREATE TABLE team_members (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name          VARCHAR(150) NOT NULL,
    title         VARCHAR(200) NOT NULL,
    photo_url     VARCHAR(500),
    linkedin_url  VARCHAR(500),
    display_order INT          NOT NULL DEFAULT 0
);

CREATE TABLE core_values (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title         VARCHAR(100) NOT NULL,
    description   TEXT,
    display_order INT          NOT NULL DEFAULT 0
);

CREATE TABLE testimonials (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    quote         TEXT         NOT NULL,
    author_name   VARCHAR(150) NOT NULL,
    author_title  VARCHAR(200),
    display_order INT          NOT NULL DEFAULT 0
);

CREATE TABLE case_studies (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title         VARCHAR(200) NOT NULL,
    summary       TEXT,
    image_url     VARCHAR(500),
    video_url     VARCHAR(500),
    display_order INT          NOT NULL DEFAULT 0
);

CREATE TABLE tie_up_partners (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name          VARCHAR(150) NOT NULL,
    logo_url      VARCHAR(500) NOT NULL,
    display_order INT          NOT NULL DEFAULT 0
);

CREATE TABLE gallery_images (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    image_url     VARCHAR(500) NOT NULL,
    caption       VARCHAR(255),
    display_order INT          NOT NULL DEFAULT 0
);

CREATE TABLE job_postings (
    id              BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title           VARCHAR(200) NOT NULL,
    department      VARCHAR(150),
    location        VARCHAR(150),
    employment_type VARCHAR(50),
    description     TEXT,
    apply_email     VARCHAR(255),
    published       BOOLEAN      NOT NULL DEFAULT TRUE,
    posted_at       TIMESTAMPTZ  NOT NULL DEFAULT now()
);
CREATE INDEX idx_jobs_published_date ON job_postings (published, posted_at DESC);

CREATE TABLE contact_submissions (
    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name       VARCHAR(150) NOT NULL,
    email      VARCHAR(255) NOT NULL,
    phone      VARCHAR(30),
    subject    VARCHAR(255),
    message    TEXT         NOT NULL,
    ip_address VARCHAR(64),
    read       BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT now()
);
CREATE INDEX idx_contact_created ON contact_submissions (created_at DESC);

CREATE TABLE newsletter_subscribers (
    id                  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email               VARCHAR(255) NOT NULL UNIQUE,
    confirmed           BOOLEAN      NOT NULL DEFAULT FALSE,
    confirmation_token  VARCHAR(100),
    subscribed_at       TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE TABLE office_locations (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    label         VARCHAR(100) NOT NULL,
    address       TEXT         NOT NULL,
    phone         VARCHAR(30),
    display_order INT          NOT NULL DEFAULT 0
);

CREATE TABLE social_links (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    platform      VARCHAR(50)  NOT NULL,
    url           VARCHAR(500) NOT NULL,
    display_order INT          NOT NULL DEFAULT 0
);