-- =====================================================================
-- Bangladesh Tariff & Trade Intelligence Platform (Beta) — PostgreSQL schema
-- Copyright (c) 2026 Mohammed Mahraj ul Alam Samrat, TRACE Consulting Ltd. All rights reserved.
-- Target: PostgreSQL 15+
-- Seed data: db/seed/*.json loaded by api/load_data.py
-- =====================================================================

CREATE EXTENSION IF NOT EXISTS pg_trgm;   -- trigram search on descriptions

-- ---------- tariff ----------
CREATE TABLE tariff_line (
  code        VARCHAR(8) PRIMARY KEY,          -- 8-digit BD tariff line
  heading     VARCHAR(4) NOT NULL,
  chapter     VARCHAR(2) NOT NULL,
  description TEXT NOT NULL,
  unit        VARCHAR(16),
  saf         TEXT,                            -- SAFTA/APTA note as published
  is_specific BOOLEAN NOT NULL DEFAULT FALSE   -- specific/compound duty line
);
CREATE INDEX idx_line_heading ON tariff_line(heading);
CREATE INDEX idx_line_desc_trgm ON tariff_line USING gin (description gin_trgm_ops);

CREATE TABLE fiscal_year (
  fy          VARCHAR(7) PRIMARY KEY,          -- '2026-27'
  is_current  BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE tariff_rate (
  code  VARCHAR(8) REFERENCES tariff_line(code) ON DELETE CASCADE,
  fy    VARCHAR(7) REFERENCES fiscal_year(fy),
  cd NUMERIC(6,2) NOT NULL DEFAULT 0,
  rd NUMERIC(6,2) NOT NULL DEFAULT 0,
  sd NUMERIC(6,2) NOT NULL DEFAULT 0,
  vat NUMERIC(6,2) NOT NULL DEFAULT 0,
  ait NUMERIC(6,2) NOT NULL DEFAULT 0,
  at NUMERIC(6,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (code, fy)
);

CREATE TABLE import_stat (
  code VARCHAR(8) REFERENCES tariff_line(code) ON DELETE CASCADE,
  fy   VARCHAR(7),
  import_value_cr NUMERIC(14,3),               -- crore BDT
  revenue_cr      NUMERIC(14,3),
  published_tti   NUMERIC(7,2),
  PRIMARY KEY (code, fy)
);

-- ---------- SRO repository ----------
CREATE TABLE sro (
  id          SERIAL PRIMARY KEY,
  sro_key     TEXT UNIQUE,                     -- normalised 'N/YYYY'
  number      TEXT,
  year        INT,
  title       TEXT,
  category    TEXT,
  status      TEXT,                            -- active/amended/data-required...
  amends      TEXT,
  source_url  TEXT,
  confidence  TEXT,
  raw         JSONB NOT NULL                   -- full record as embedded
);
CREATE TABLE sro_hs (
  sro_id  INT REFERENCES sro(id) ON DELETE CASCADE,
  hs_code VARCHAR(8),
  ccd NUMERIC(6,2), csd NUMERIC(6,2),
  conditions TEXT,
  PRIMARY KEY (sro_id, hs_code)
);
CREATE INDEX idx_sro_hs ON sro_hs(hs_code);

-- ---------- IPO 2021-24 compliance layer ----------
CREATE TABLE ipo_provision (
  id       SERIAL PRIMARY KEY,
  lvl      TEXT NOT NULL CHECK (lvl IN ('hs','heading','chapter','general')),
  k        TEXT,                               -- code key ('' for general)
  scope    TEXT NOT NULL,
  status   TEXT NOT NULL CHECK (status IN
           ('prohibited','restricted','controlled','conditional','free-with-conditions')),
  condition TEXT,
  ipo_ref   TEXT NOT NULL,
  remarks   TEXT,
  docs      JSONB NOT NULL DEFAULT '[]',       -- required permits/documents
  auth      JSONB NOT NULL DEFAULT '[]',       -- competent agencies
  kws       JSONB NOT NULL DEFAULT '[]',       -- description keywords
  inc       JSONB NOT NULL DEFAULT '[]',       -- scope-limited: applies only to
  exc       JSONB NOT NULL DEFAULT '[]'        -- stated exceptions
);
CREATE INDEX idx_ipo_k ON ipo_provision(k);

-- ---------- Explanatory Notes (LICENSED CONTENT — control access) ----------
CREATE TABLE en_heading (heading VARCHAR(4) PRIMARY KEY, body TEXT NOT NULL);
CREATE TABLE en_chapter (chapter VARCHAR(2) PRIMARY KEY, body TEXT NOT NULL);
CREATE INDEX idx_en_body_trgm ON en_heading USING gin (body gin_trgm_ops);

-- ---------- ruling register ----------
CREATE TABLE ruling (
  id SERIAL PRIMARY KEY,
  hs_code TEXT, jurisdiction TEXT, ref TEXT, ruling_date DATE,
  description TEXT, reasoning TEXT, source_url TEXT,
  added_by INT, added_at TIMESTAMPTZ DEFAULT now()
);

-- ---------- users / auth / audit ----------
CREATE TABLE app_user (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  full_name TEXT, organisation TEXT,
  role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user','editor','admin')),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE calc_log (            -- saved duty calculations (audit trail)
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES app_user(id),
  hs_code VARCHAR(8), fy VARCHAR(7),
  inputs JSONB NOT NULL, result JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  user_id INT, action TEXT NOT NULL, entity TEXT, entity_id TEXT,
  detail JSONB, at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE content_version (     -- provenance of every published data pack
  id SERIAL PRIMARY KEY,
  pack TEXT NOT NULL,              -- tariff|sro|ipo|en|rulings
  version TEXT NOT NULL,
  effective_fy VARCHAR(7),
  source_note TEXT,
  published_by INT REFERENCES app_user(id),
  published_at TIMESTAMPTZ DEFAULT now()
);