-- Legacy shodh-forms registries: supervisors, doctoral and post-doctoral
-- research registers (php_* tables in the production dump). Public display
-- is "same as legacy": approved entries with all fields the old site showed.
CREATE TABLE registry_entries (
    id             UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    type           VARCHAR(20)  NOT NULL
                   CHECK (type IN ('SUPERVISOR','DOCTORAL','POSTDOCTORAL')),
    legacy_id      INT          NOT NULL,
    name           VARCHAR(255),
    designation    VARCHAR(255),
    address        VARCHAR(500),
    email          VARCHAR(320),
    phone          VARCHAR(50),
    mobile         VARCHAR(50),
    interest       VARCHAR(300),
    subject        VARCHAR(300),
    university     VARCHAR(300),
    -- research-register fields (null for supervisors)
    department     VARCHAR(300),
    medium         VARCHAR(20),
    topic          VARCHAR(500),
    work_status    VARCHAR(300),
    supervisor     VARCHAR(300),
    research_year  VARCHAR(20),
    funding        VARCHAR(300),
    -- visibility mirrors the legacy approval flag (sstatus/status = Yes)
    visible        BOOLEAN      NOT NULL DEFAULT FALSE,
    registered_at  DATE,
    created_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (type, legacy_id)
);

CREATE INDEX idx_registry_entries_type_visible ON registry_entries(type, visible);
CREATE INDEX idx_registry_entries_subject      ON registry_entries(LOWER(subject));