-- Reference / lookup table
CREATE TABLE IF NOT EXISTS licence_type (
    licence_type_cd      TEXT PRIMARY KEY,
    licence_type_desc_fr TEXT,
    licence_type_desc_en TEXT
);

-- Companies
CREATE TABLE IF NOT EXISTS company (
    company_id      INTEGER PRIMARY KEY,
    company_name    TEXT,
    addr_line_1     TEXT,
    addr_line_2     TEXT,
    addr_line_3     TEXT,
    postal_code     TEXT,
    city            TEXT,
    country_cd      TEXT,
    region_cd       TEXT,
    company_status  TEXT
);

-- Licences with bilingual names merged from /licence/?lang=fr and lang=en
CREATE TABLE IF NOT EXISTS licence (
    original_licence_no     INTEGER PRIMARY KEY,
    licence_status          TEXT,
    appl_risk_class         INTEGER,
    licence_name_fr         TEXT,
    licence_name_en         TEXT,
    first_licence_status_dt TEXT,
    last_refresh_dt         TEXT,
    end_date                TEXT,
    licence_type_cd         TEXT,
    company_id              INTEGER
);
CREATE INDEX IF NOT EXISTS idx_licence_company ON licence(company_id);
CREATE INDEX IF NOT EXISTS idx_licence_type    ON licence(licence_type_cd);
CREATE INDEX IF NOT EXISTS idx_licence_class   ON licence(appl_risk_class);
CREATE INDEX IF NOT EXISTS idx_licence_status  ON licence(licence_status);
CREATE INDEX IF NOT EXISTS idx_licence_date    ON licence(first_licence_status_dt);

-- Devices
CREATE TABLE IF NOT EXISTS device (
    device_id            INTEGER PRIMARY KEY,
    original_licence_no  INTEGER,
    first_licence_dt     TEXT,
    end_date             TEXT,
    trade_name           TEXT
);
CREATE INDEX IF NOT EXISTS idx_device_licence ON device(original_licence_no);
CREATE INDEX IF NOT EXISTS idx_device_date    ON device(first_licence_dt);

-- Device identifiers (composite PK; could be millions of rows)
CREATE TABLE IF NOT EXISTS device_identifier (
    device_id            INTEGER,
    device_identifier    TEXT,
    original_licence_no  INTEGER,
    first_licence_dt     TEXT,
    end_date             TEXT,
    PRIMARY KEY (device_id, device_identifier)
);
CREATE INDEX IF NOT EXISTS idx_di_licence ON device_identifier(original_licence_no);
CREATE INDEX IF NOT EXISTS idx_di_date     ON device_identifier(first_licence_dt);

-- (sbd_location table deferred to v0.2+; the upstream /sbdlocation/ endpoint
-- is empty for medical devices in v0.1.0 — see Spike A notes.)

-- Regulatory Decision Summaries (Décisions). Sourced from a separate Health
-- Canada API at health-products.canada.ca/api/dhpr/controller/... — not from
-- the MDALL endpoints. ~6,800 device decisions, growing ~1k/year.
CREATE TABLE IF NOT EXISTS rds_decision (
    link_id              TEXT PRIMARY KEY,   -- e.g. 'RDS10001'
    control_number       TEXT,
    drug_name_fr         TEXT,                -- product/device brand name (FR)
    drug_name_en         TEXT,                -- product/device brand name (EN)
    manufacturer         TEXT,                -- single value (the API does not
                                              -- localise manufacturer names)
    date_decision        TEXT,                -- ISO YYYY-MM-DD
    decision             TEXT,                -- 'Approved' (only value seen)
    type_submission_fr   TEXT,
    type_submission_en   TEXT
);
CREATE INDEX IF NOT EXISTS idx_rds_date ON rds_decision(date_decision);
CREATE INDEX IF NOT EXISTS idx_rds_manuf ON rds_decision(manufacturer);

-- Full-text search index (label concatenates FR + EN names for licences)
CREATE VIRTUAL TABLE IF NOT EXISTS fts_search USING fts5(
    entity_type UNINDEXED,
    entity_id   UNINDEXED,
    label,
    secondary,
    tokenize = 'unicode61 remove_diacritics 1'
);

-- Metadata
CREATE TABLE IF NOT EXISTS _meta (
    key   TEXT PRIMARY KEY,
    value TEXT
);

INSERT OR IGNORE INTO _meta (key, value) VALUES ('schema_version', '1');
