schema.sql

-- ============================================================
-- PharmaCast PostgreSQL Schema — 7-Table MVP
-- Team CritiCo | AITHON 2026 | Hemas Pharmaceuticals
-- ============================================================

-- ============================================================
-- SECTION 0: ENUM types   (DO blocks safe across all PG versions)
-- ============================================================

DO $$ BEGIN
    CREATE TYPE province_t AS ENUM (
        'Western', 'Central', 'Southern', 'Northern', 'Eastern',
        'North Western', 'North Central', 'Uva', 'Sabaragamuwa'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE pharmacy_type_t AS ENUM (
        'Chain', 'Independent', 'Hospital-Attached', 'Community', 'NGO-Supported'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE risk_level_t AS ENUM ('low', 'medium', 'high', 'critical');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE model_name_t AS ENUM ('LightGBM', 'TabNet', 'TimesFM');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- Climate/monsoon season (4 fixed values — perfect ENUM candidate)
DO $$ BEGIN
    CREATE TYPE climate_season_t AS ENUM (
        'NE_MONSOON', 'FIM', 'SW_MONSOON', 'SIM'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- Disease names that appear in disease_signal rows
DO $$ BEGIN
    CREATE TYPE disease_t AS ENUM (
        'Dengue Fever', 'Influenza A (H1N1)', 'Chikungunya',
        'Leptospirosis', 'Cholera', 'Typhoid'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ATC category ENUM (avoids storing repeated text strings in multi-million rows)
DO $$ BEGIN
    CREATE TYPE atc_t AS ENUM (
        'M01AB', 'M01AE', 'N02BA', 'N02BE',
        'N05B',  'N05C',
        'R03',   'R06',
        'J01CA', 'J01FA', 'J01MA', 'J01XD', 'J01AA', 'J01DB',
        'A10BA', 'A10BB', 'A10BH',
        'C08CA', 'C07AB', 'C09CA', 'C09AA', 'C03AA', 'C10AA',
        'A02BC', 'A02BA', 'A03FA', 'A07CA',
        'A11GA', 'A11CC', 'A12CB',
        'D01AC', 'D04AX', 'D07AC',
        'DIAG'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ============================================================
-- SECTION 1: Core Reference Tables
-- ============================================================

CREATE TABLE IF NOT EXISTS drug (
    drug_id        INT           PRIMARY KEY,                   -- max 57 drugs → INT is fine
    drug_name      TEXT          NOT NULL,
    atc_category   atc_t         NOT NULL,                      -- ENUM: ~4 B vs 8 B text overhead
    unit_price_lkr NUMERIC(10,2) NOT NULL,                      -- 10 sig digits is plenty
    is_chronic     BOOLEAN       NOT NULL DEFAULT FALSE,
    is_seasonal    BOOLEAN       NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS pharmacy (
    pharmacy_id   INT              PRIMARY KEY,                  -- max ~2 300 → INT saves 4 B/row
    pharmacy_name TEXT             NOT NULL,
    province      province_t       NOT NULL,
    district      TEXT             NOT NULL,
    is_urban      BOOLEAN          NOT NULL,
    pharmacy_type pharmacy_type_t  NOT NULL,
    lat           REAL             NOT NULL,                     -- REAL (4 B) vs NUMERIC(9,6) (18 B)
    lon           REAL             NOT NULL
);

-- ============================================================
-- SECTION 2: Operational Tables
-- ============================================================

-- Daily aggregated sales (one row per pharmacy × drug × date)
CREATE TABLE IF NOT EXISTS sales_daily (
    sales_date      DATE             NOT NULL,
    pharmacy_id     INT              NOT NULL REFERENCES pharmacy(pharmacy_id),
    province        province_t       NOT NULL,                   -- denormalised for analytics GROUP BY
    drug_id         INT              NOT NULL REFERENCES drug(drug_id),
    units_sold      SMALLINT         NOT NULL DEFAULT 0,         -- max 32 767; 2 B vs 4 B
    sales_value_lkr NUMERIC(10,2)    NOT NULL DEFAULT 0,
    climate_season  climate_season_t NOT NULL,
    is_zero_sale    BOOLEAN          NOT NULL DEFAULT FALSE,
    PRIMARY KEY (sales_date, pharmacy_id, drug_id)
);

-- Daily inventory snapshot (replaces non-dated inventory table)
CREATE TABLE IF NOT EXISTS inventory_daily (
    snapshot_date       DATE    NOT NULL,
    pharmacy_id         INT     NOT NULL REFERENCES pharmacy(pharmacy_id),
    drug_id             INT     NOT NULL REFERENCES drug(drug_id),
    current_units       INT     NOT NULL DEFAULT 0,
    reorder_point_units INT     NOT NULL,
    stock_days_remaining REAL   NOT NULL DEFAULT 0,              -- REAL: 4 B vs NUMERIC(10,2) 18 B
    last_restock_date   DATE,
    expiry_date         DATE,                                    -- batch expiry from last restock
    PRIMARY KEY (snapshot_date, pharmacy_id, drug_id)
);

-- ============================================================
-- SECTION 3: External Signal Table
-- ============================================================

CREATE TABLE IF NOT EXISTS disease_signal (
    signal_id             BIGSERIAL         PRIMARY KEY,         -- auto-inc
    signal_date           DATE              NOT NULL,
    disease_name          disease_t         NOT NULL,            -- ENUM
    province              province_t        NOT NULL,
    district              TEXT,
    reported_cases        INT,
    affected_atc_category atc_t,                                 -- ENUM (nullable)
    shock_multiplier      REAL              NOT NULL DEFAULT 1.0, -- REAL: 4 B vs NUMERIC(8,4) 18 B
    source_name           TEXT
);

-- ============================================================
-- SECTION 4: ML Output Tables
-- ============================================================

CREATE TABLE IF NOT EXISTS forecast (
    forecast_id    BIGSERIAL      PRIMARY KEY,
    pharmacy_id    INT            NOT NULL REFERENCES pharmacy(pharmacy_id),
    drug_id        INT            NOT NULL REFERENCES drug(drug_id),
    forecast_date  DATE           NOT NULL,
    model_name     model_name_t   NOT NULL,
    forecast_units REAL           NOT NULL,                      -- REAL: sufficient precision
    output_json    JSONB
);

CREATE TABLE IF NOT EXISTS risk_assessment (
    risk_id                   BIGSERIAL    PRIMARY KEY,
    pharmacy_id               INT          NOT NULL REFERENCES pharmacy(pharmacy_id),
    drug_id                   INT          NOT NULL REFERENCES drug(drug_id),
    assessment_date           DATE         NOT NULL,
    risk_score                REAL         NOT NULL DEFAULT 0,   -- continuous 0–1 score
    risk_level                risk_level_t NOT NULL,
    recommended_reorder_units INT
);

-- ============================================================
-- SECTION 5: Indexes
-- ============================================================

-- pharmacy lookups
CREATE INDEX IF NOT EXISTS idx_pharmacy_province_district
    ON pharmacy (province, district);

-- drug lookups
CREATE INDEX IF NOT EXISTS idx_drug_atc
    ON drug (atc_category);

-- sales_daily — primary analytics path
CREATE INDEX IF NOT EXISTS idx_sales_daily_date
    ON sales_daily (sales_date DESC);

CREATE INDEX IF NOT EXISTS idx_sales_daily_province
    ON sales_daily (province, sales_date DESC);

CREATE INDEX IF NOT EXISTS idx_sales_daily_pharm_drug
    ON sales_daily (pharmacy_id, drug_id);

CREATE INDEX IF NOT EXISTS idx_sales_daily_zero
    ON sales_daily (sales_date DESC) WHERE is_zero_sale = TRUE;

-- inventory_daily
CREATE INDEX IF NOT EXISTS idx_inv_daily_snapshot
    ON inventory_daily (snapshot_date DESC, pharmacy_id, drug_id);

CREATE INDEX IF NOT EXISTS idx_inv_daily_expiry
    ON inventory_daily (expiry_date) WHERE expiry_date IS NOT NULL;

-- disease signal
CREATE INDEX IF NOT EXISTS idx_disease_signal_date_prov
    ON disease_signal (signal_date, province);

-- forecast
CREATE INDEX IF NOT EXISTS idx_forecast_date
    ON forecast (forecast_date, pharmacy_id, drug_id);

-- risk assessment
CREATE INDEX IF NOT EXISTS idx_risk_level_date
    ON risk_assessment (risk_level, assessment_date);

CREATE INDEX IF NOT EXISTS idx_risk_critical_high
    ON risk_assessment (assessment_date DESC)
    WHERE risk_level IN ('high', 'critical');