-- Ratatouille local DB schema (SQLite)
-- Design principles:
--   * stable integer PKs everywhere; never join on display names
--   * append-only inventory ledger (full history, derive state by replay)
--   * people (customers + prospects) unified, separated by lifecycle stage
PRAGMA foreign_keys = ON;

-- ----------------------------------------------------------------------------
-- Reference: products
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS product (
    id                INTEGER PRIMARY KEY,
    code              TEXT UNIQUE NOT NULL,        -- e.g. TR60
    name              TEXT NOT NULL,               -- Tirzepatide
    unit_label        TEXT DEFAULT 'vial',
    strength_mg       REAL,                        -- mg per vial (60)
    cost_per_vial     REAL DEFAULT 0,
    price_unit        REAL DEFAULT 0,              -- single-vial price
    price_half_kit    REAL DEFAULT 0,              -- per-vial price, half-kit tier
    price_full_kit    REAL DEFAULT 0,              -- per-vial price, full-kit tier
    reorder_lead_days INTEGER DEFAULT 14,
    shelf_life_days   INTEGER,                     -- after constitution/open
    active            INTEGER DEFAULT 1,
    tier              INTEGER DEFAULT 2,           -- 1 primary | 2 offered | 3 occasional (selection priority)
    notes             TEXT
);

-- ----------------------------------------------------------------------------
-- Reference: referrers / sources (makes referral track-record queryable)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS referrer (
    id    INTEGER PRIMARY KEY,
    name  TEXT UNIQUE NOT NULL
);

-- ----------------------------------------------------------------------------
-- Titration ladders: a reusable dose-escalation schedule attachable to a person.
-- A person's effective weekly dose at a date is the step whose week_start is the
-- latest one <= the weeks elapsed since their titration_start_date.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dose_template (
    id    INTEGER PRIMARY KEY,
    name  TEXT UNIQUE NOT NULL,
    notes TEXT
);

CREATE TABLE IF NOT EXISTS dose_step (
    id             INTEGER PRIMARY KEY,
    template_id    INTEGER NOT NULL REFERENCES dose_template(id),
    week_start     INTEGER NOT NULL,                       -- 1-based week the step begins
    weekly_dose_mg REAL NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_dosestep_template ON dose_step(template_id);

-- ----------------------------------------------------------------------------
-- People: customers AND prospects in one table, separated by `stage`.
-- This is the fix for the two-list duplication problem.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS person (
    id                  INTEGER PRIMARY KEY,
    name                TEXT NOT NULL,
    external_ref        TEXT,                              -- stable ID from a CSV/source system
    product_id          INTEGER REFERENCES product(id),
    referrer_id         INTEGER REFERENCES referrer(id),

    -- lifecycle
    stage               TEXT NOT NULL DEFAULT 'prospect',  -- prospect | active | paused | archived
    pipeline_status     TEXT,                              -- cold | warm | hot | closing  (manual)
    expected_start_date TEXT,                              -- ISO date, optional
    referred_by_person_id INTEGER REFERENCES person(id),   -- the customer who referred this person

    -- dosing / consumption
    weekly_dose_mg      REAL,                              -- flat dose, or manual override of a ladder
    kit_tier            TEXT DEFAULT 'Unit',               -- Unit | Half | Full
    share_count         REAL DEFAULT 1,                    -- household sharing a vial
    usage_weeks_override REAL,                             -- manual weeks/vial override
    dose_template_id    INTEGER REFERENCES dose_template(id),  -- titration ladder, if any
    titration_start_date TEXT,                             -- ISO date the ladder started

    -- supply snapshot (the few genuinely-manual fields)
    current_vials       REAL DEFAULT 0,
    last_opened_date    TEXT,                              -- ISO date last vial opened

    -- commission
    commission_eligible INTEGER DEFAULT 0,
    commission_pct      REAL DEFAULT 0.15,

    -- contact / delivery
    street              TEXT,
    city                TEXT,
    state               TEXT,
    zip                 TEXT,
    phone               TEXT,
    email               TEXT,
    venmo               TEXT,                              -- @handle for Venmo requests/notes
    preferred_channel   TEXT,                              -- text | email | venmo (how they like to be reached)
    last_contacted      TEXT,                              -- ISO date of the last outreach (campaign nudge)
    household           TEXT,                              -- label grouping people who share an address/supply

    -- bookkeeping
    notes               TEXT,
    created_at          TEXT DEFAULT (datetime('now')),
    updated_at          TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_person_stage ON person(stage);
CREATE INDEX IF NOT EXISTS idx_person_product ON person(product_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_person_external_ref ON person(external_ref)
    WHERE external_ref IS NOT NULL;

-- ----------------------------------------------------------------------------
-- Append-only inventory ledger. State (on-hand, reserved, owed, borrowable)
-- is DERIVED by replaying these rows. Nothing is ever updated destructively.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS inventory_event (
    id            INTEGER PRIMARY KEY,
    event_date    TEXT NOT NULL,                  -- ISO date
    product_id    INTEGER NOT NULL REFERENCES product(id),
    movement_type TEXT NOT NULL,                  -- starting_balance | restock | reserve_owed
                                                  -- | deliver_use | borrowed_transfer
                                                  -- | adjustment_in | adjustment_out
    person_id     INTEGER REFERENCES person(id),  -- null for supplier/on-hand events
    source_label  TEXT,                           -- free text when no person (e.g. "Supplier")
    qty_in        REAL DEFAULT 0,
    qty_out       REAL DEFAULT 0,
    qty_reserved  REAL DEFAULT 0,
    qty_released  REAL DEFAULT 0,
    due_date      TEXT,                            -- expected/needed date
    status        TEXT DEFAULT 'Open',             -- Open | Closed | Pending
    borrowable    INTEGER DEFAULT 0,
    notes         TEXT,
    created_at    TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_invevent_product ON inventory_event(product_id);
CREATE INDEX IF NOT EXISTS idx_invevent_person ON inventory_event(person_id);
CREATE INDEX IF NOT EXISTS idx_invevent_status ON inventory_event(status);

-- ----------------------------------------------------------------------------
-- Pipeline activity log: every stage/status change, for conversion analytics.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pipeline_event (
    id          INTEGER PRIMARY KEY,
    person_id   INTEGER NOT NULL REFERENCES person(id),
    event_date  TEXT DEFAULT (datetime('now')),
    from_stage  TEXT,
    to_stage    TEXT,
    from_status TEXT,
    to_status   TEXT,
    note        TEXT
);
CREATE INDEX IF NOT EXISTS idx_pipeevent_person ON pipeline_event(person_id);

-- ----------------------------------------------------------------------------
-- Purchase orders: what you bought from a supplier channel, and when it arrived.
-- Receiving an order writes restock events into the inventory ledger, and the
-- ordered->delivered gap teaches the app real per-channel lead time.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS purchase_order (
    id             INTEGER PRIMARY KEY,
    channel        TEXT,                              -- China Warehouse | US Warehouse | ...
    status         TEXT DEFAULT 'Draft',              -- Draft | Ordered | Shipped | Received
    ordered_date   TEXT,
    delivered_date TEXT,
    shipping_cost  REAL DEFAULT 0,
    payment_ref    TEXT,                              -- e.g. BTC txid / amount (bookkeeping only)
    notes          TEXT,
    created_at     TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS order_line (
    id            INTEGER PRIMARY KEY,
    order_id      INTEGER NOT NULL REFERENCES purchase_order(id),
    product_id    INTEGER REFERENCES product(id),
    qty_kits      REAL DEFAULT 0,
    vials_per_kit INTEGER DEFAULT 10,
    unit_cost     REAL DEFAULT 0,                     -- cost per kit
    line_total    REAL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_orderline_order ON order_line(order_id);

-- ----------------------------------------------------------------------------
-- Settings: single-row key/value, mirrors the sheet's Settings tab.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS setting (
    key    TEXT PRIMARY KEY,
    value  TEXT,
    label  TEXT
);

-- ----------------------------------------------------------------------------
-- Goals: lets the "franchisee" track plan vs actual.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS goal (
    id          INTEGER PRIMARY KEY,
    period      TEXT NOT NULL,                 -- e.g. '2026-06' (month) or '2026-Q3'
    metric      TEXT NOT NULL,                 -- active_clients | net_profit | new_clients
    target      REAL NOT NULL,
    note        TEXT,
    created_at  TEXT DEFAULT (datetime('now')),
    UNIQUE(period, metric)
);

-- ----------------------------------------------------------------------------
-- Operator-authored time-phased inventory demand plans.
-- These are business planning assumptions expressed only in vials and time. The
-- app ships no default curves and stores no dose, strength, route, or protocol.
-- A product can have several named models; one can be the forecast default.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS demand_plan (
    id          INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL REFERENCES product(id),
    name        TEXT NOT NULL,
    repeat_plan INTEGER NOT NULL DEFAULT 0,
    is_default  INTEGER NOT NULL DEFAULT 0,
    notes       TEXT,
    created_at  TEXT DEFAULT (datetime('now')),
    updated_at  TEXT DEFAULT (datetime('now')),
    UNIQUE(product_id, name)
);
CREATE INDEX IF NOT EXISTS idx_demandplan_product ON demand_plan(product_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_demandplan_one_default ON demand_plan(product_id)
    WHERE is_default = 1;

CREATE TABLE IF NOT EXISTS demand_phase (
    id                         INTEGER PRIMARY KEY,
    plan_id                    INTEGER NOT NULL REFERENCES demand_plan(id) ON DELETE CASCADE,
    phase_order                INTEGER NOT NULL,
    phase_type                 TEXT NOT NULL,          -- run | pause
    duration_days              INTEGER NOT NULL,
    planned_vials_per_30_days  REAL NOT NULL DEFAULT 0,
    planned_units_per_30_days  REAL,                   -- optional generic inventory-yield basis
    units_per_vial             REAL,                   -- optional generic inventory-yield basis
    notes                      TEXT,
    UNIQUE(plan_id, phase_order)
);
CREATE INDEX IF NOT EXISTS idx_demandphase_plan ON demand_phase(plan_id);
