-- Notices for the Information desk's News page.
--
-- The old site carried these in a Joomla sidebar module — a short list of
-- circulars, each a heading over a link to the document, with a Hindi copy
-- beside the English one. The module was not part of the article import, so
-- the page arrived in this application with a route, a menu entry and nothing
-- behind it.
--
-- Stored as rows rather than as another block of CMS HTML. The editor posts
-- these regularly and each one is the same shape every time, so a form with a
-- date and a file beats pasting markup, and a reader gets a list that sorts
-- itself.

CREATE TABLE news_items (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    title_hi      VARCHAR(500) NOT NULL,
    title_en      VARCHAR(500),
    body_hi       TEXT,
    body_en       TEXT,

    -- The date the notice carries, which is what readers see and what orders
    -- the list. Deliberately not created_at: a circular is often posted after
    -- the date it is issued under.
    posted_on     DATE NOT NULL,

    -- Where the notice points, if anywhere. Any combination is valid: a notice
    -- may be text alone, a link, one document, or an English and a Hindi copy.
    link_url      VARCHAR(1000),
    doc_en_key    VARCHAR(500),
    doc_en_name   VARCHAR(300),
    doc_en_size   BIGINT,
    doc_hi_key    VARCHAR(500),
    doc_hi_name   VARCHAR(300),
    doc_hi_size   BIGINT,

    -- Drafts stay off the public site until the editor is ready.
    published     BOOLEAN NOT NULL DEFAULT FALSE,

    created_by    UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- The public page's only query: published notices, newest first.
CREATE INDEX idx_news_items_public ON news_items (published, posted_on DESC, created_at DESC);

COMMENT ON TABLE news_items IS
    'Notices shown on the Information desk News page; replaces the legacy Joomla news module.';