-- Phase 2 of the shodh.net integration (see docs/legacy-site/legacy-site-inventory.md):
-- a dedicated home for the legacy Vol.1-17 archive, and typed guest inquiries
-- replacing the legacy email-only forms.

-- ===== Legacy journal issues =====
CREATE TABLE archive_issues (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    volume              INT          NOT NULL,
    issue_label         VARCHAR(20)  NOT NULL,          -- "1", "2", "1&2"
    publish_date        DATE,
    issn_print          VARCHAR(20),
    issn_online         VARCHAR(20),
    cover_image_key     VARCHAR(255),
    legacy_category_id  INT UNIQUE,                     -- phocadownload category id
    sort_order          INT          NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (volume, issue_label)
);

-- ===== Legacy articles =====
CREATE TABLE archive_articles (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    issue_id        UUID         NOT NULL REFERENCES archive_issues(id) ON DELETE CASCADE,
    title_hi        VARCHAR(500),
    title_en        VARCHAR(500),
    authors_hi      VARCHAR(500),
    authors_en      VARCHAR(500),
    abstract_hi     TEXT,
    abstract_en     TEXT,
    keywords        TEXT[],
    page_range      VARCHAR(30),
    pdf_key         VARCHAR(255),
    pdf_size_bytes  BIGINT,
    download_count  INT          NOT NULL DEFAULT 0,
    legacy_file_id  INT UNIQUE,                         -- phocadownload file id
    legacy_slug     VARCHAR(500),
    article_order   INT          NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_archive_articles_issue     ON archive_articles(issue_id);
CREATE INDEX idx_archive_articles_title_en  ON archive_articles(LOWER(title_en));
CREATE INDEX idx_archive_articles_title_hi  ON archive_articles(title_hi);

-- ===== Guest inquiries =====
CREATE TABLE public_inquiries (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    type          VARCHAR(20)  NOT NULL
                  CHECK (type IN ('CONTACT','FEEDBACK','SEMINAR','WORKSHOP','TRAINING',
                                  'BOOK_SUGGESTION','TOPIC_SUGGESTION','ABSTRACT',
                                  'SUBSCRIPTION','FORUM')),
    name          VARCHAR(200) NOT NULL,
    email         VARCHAR(320) NOT NULL,
    subject       VARCHAR(300),
    message       TEXT         NOT NULL,
    details_json  TEXT,
    status        VARCHAR(20)  NOT NULL DEFAULT 'NEW'
                  CHECK (status IN ('NEW','REVIEWED','CLOSED')),
    ip_address    VARCHAR(45),
    admin_note    TEXT,
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_public_inquiries_status     ON public_inquiries(status);
CREATE INDEX idx_public_inquiries_created_at ON public_inquiries(created_at DESC);