SET FOREIGN_KEY_CHECKS=0;

-- ============================================================================
-- CA AUTOMATOR — DATABASE SCHEMA v1.0
-- Target: SQLite (default) / PostgreSQL (see notes at end)
-- Conventions:
--   * all timestamps stored as ISO-8601 UTC strings
--   * money stored as DOUBLE; presentation rounding happens in the app layer
--   * Nepali (Bikram Sambat) dates stored as TEXT 'YYYY-MM-DD' alongside AD
--   * every rate/threshold lives in rate_master, never in code
-- ============================================================================


-- ---------------------------------------------------------------- 1. TENANCY

CREATE TABLE IF NOT EXISTS firm (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            TEXT NOT NULL,
    short_name      TEXT,
    pan             TEXT,
    address         TEXT,
    phone           TEXT,
    email           TEXT,
    website         TEXT,
    letterhead_path TEXT,
    logo_path       TEXT,
    base_currency   VARCHAR(100) NOT NULL DEFAULT 'NPR',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS app_user (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    firm_id       INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    full_name     TEXT NOT NULL,
    email         VARCHAR(100) NOT NULL UNIQUE,
    role          VARCHAR(100) NOT NULL DEFAULT 'staff',   -- partner | manager | staff | readonly
    membership_no TEXT,                            -- ICAN membership number
    pwd_hash      TEXT NOT NULL,
    pwd_salt      TEXT NOT NULL,
    is_active     INT NOT NULL DEFAULT 1,
    last_login_at TEXT,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_user_firm ON app_user(firm_id);

CREATE TABLE IF NOT EXISTS auth_session (
    token       VARCHAR(100) PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    device_id   TEXT,
    issued_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at  TEXT NOT NULL,
    revoked_at  TEXT
);
CREATE INDEX ix_session_user ON auth_session(user_id);

-- Which modules a non-admin user can see and use. A super admin needs no rows
-- here — access is checked "is_super_admin, else has a grant row" everywhere
-- this is read. Presence of a row is the grant; there is no notion of a
-- module being explicitly denied.
CREATE TABLE IF NOT EXISTS user_module_grant (
    user_id     INT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    module_key  VARCHAR(100) NOT NULL,
    granted_by  INT REFERENCES app_user(id),
    granted_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at  TEXT,       -- 'YYYY-MM-DD'; NULL = permanent (the original, still-default behaviour)
    PRIMARY KEY (user_id, module_key)
);

-- The ceiling above user_module_grant: which modules a FIRM's own plan
-- includes at all, set only by a platform admin (see server/app.py's
-- /api/platform/* routes). A firm's own super admin can only grant a
-- staff member a module that's in this set — this is what makes "we
-- control how many employees of that client can access which modules"
-- real rather than a UI-only suggestion: admin_create_user/
-- admin_update_user reject any module_key not present here for that firm.
CREATE TABLE IF NOT EXISTS firm_module_grant (
    firm_id     INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    module_key  VARCHAR(100) NOT NULL,
    granted_by  INT REFERENCES app_user(id),
    granted_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (firm_id, module_key)
);

-- Reusable role presets ("HR gets Career, Accountant gets Billing, Content
-- creator gets Blog") so granting a new employee's access is picking a
-- role, not ticking two dozen boxes from scratch every time — the picked
-- template just pre-fills user_module_grant rows, which stay individually
-- editable afterward same as always. firm_id NULL = a global, built-in
-- template (seeded once, managed only by a platform admin, visible to
-- every firm as a starting point); firm_id set = that firm's own custom
-- template, managed by their own super admin/manager, seen only by them.
CREATE TABLE IF NOT EXISTS role_template (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    firm_id       INT REFERENCES firm(id) ON DELETE CASCADE,
    `key`           TEXT NOT NULL,
    name          TEXT NOT NULL,
    description   TEXT,
    module_grants VARCHAR(100) NOT NULL DEFAULT '[]',   -- JSON array of module_key
    is_builtin    INT NOT NULL DEFAULT 0,
    created_by    INT REFERENCES app_user(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_role_template_firm ON role_template(firm_id);

-- One-time tokens for the account-entry flow: a super admin creates a user
-- with no usable password, this table's token is how that person sets their
-- own first password. Same shape reused by password_reset below rather than
-- one polymorphic table, since the two flows have different admin-side
-- affordances (re-issuing an invite vs. re-issuing a reset).
CREATE TABLE IF NOT EXISTS user_invite (
    token       VARCHAR(100) PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    expires_at  TEXT NOT NULL,
    used_at     TEXT
);

CREATE TABLE IF NOT EXISTS password_reset (
    token       VARCHAR(100) PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    expires_at  TEXT NOT NULL,
    used_at     TEXT
);

-- ------------------------------------------------------- 2. CLIENTS & ENGAGEMENTS

CREATE TABLE IF NOT EXISTS client (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    firm_id        INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    code           VARCHAR(100) NOT NULL,                  -- internal client code, e.g. CL-0007
    name           VARCHAR(100) NOT NULL,
    entity_type    TEXT,                           -- pvt_ltd | public_ltd | firm | proprietorship | ngo | coop | branch
    pan            TEXT,
    vat_registered INT NOT NULL DEFAULT 0,
    industry       TEXT,
    special_industry INT NOT NULL DEFAULT 0,   -- drives concession logic
    ird_office     TEXT,
    address        TEXT,
    contact_person TEXT,
    contact_phone  TEXT,
    contact_email  TEXT,
    fy_end_bs      VARCHAR(100) DEFAULT '03-31',           -- Ashadh end by default
    status         VARCHAR(100) NOT NULL DEFAULT 'active',
    notes          TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (firm_id, code)
);
CREATE INDEX ix_client_firm ON client(firm_id);
CREATE INDEX ix_client_name ON client(name);

-- Sharing on top of client.assigned_to (the owner). A client is visible only
-- to its owner, its collaborators and super admins — the owner is the one
-- who grants collaboration, never a request/approve flow the other way.
CREATE TABLE IF NOT EXISTS client_collaborator (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    user_id     INT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    granted_by  INT REFERENCES app_user(id),
    granted_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (client_id, user_id)
);

CREATE TABLE IF NOT EXISTS fiscal_year (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    label_bs  VARCHAR(100) NOT NULL UNIQUE,                -- '2082-83'
    start_bs  TEXT NOT NULL,                       -- '2082-04-01'
    end_bs    TEXT NOT NULL,                       -- '2083-03-31'
    start_ad  TEXT,
    end_ad    TEXT,
    is_current INT NOT NULL DEFAULT 0
);

-- See schema.sql's comment on this table for why it's separate from
-- fiscal_year rather than a migration of it. VARCHAR (not TEXT) on every
-- column that's part of an index/unique key below — MySQL requires an
-- explicit key length for TEXT/BLOB columns used that way.
CREATE TABLE IF NOT EXISTS financial_period (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    country_code VARCHAR(10) NOT NULL,
    label        VARCHAR(50) NOT NULL,
    start_ad     VARCHAR(20) NOT NULL,
    end_ad       VARCHAR(20) NOT NULL,
    is_current   INT NOT NULL DEFAULT 0,
    UNIQUE (country_code, label)
);
CREATE INDEX ix_financial_period_country ON financial_period(country_code, start_ad, end_ad);

CREATE TABLE IF NOT EXISTS engagement (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fiscal_year_id INT REFERENCES fiscal_year(id),
    type          TEXT NOT NULL,                   -- statutory_audit | tax | internal_audit | dd | ipo | projection | advisory | payroll
    title         TEXT,
    partner_id    INT REFERENCES app_user(id),
    manager_id    INT REFERENCES app_user(id),
    status        VARCHAR(100) NOT NULL DEFAULT 'planning',-- planning | fieldwork | review | reporting | closed
    start_date    TEXT,
    target_date   TEXT,
    fee_amount    DOUBLE,
    fee_status    VARCHAR(100) DEFAULT 'unbilled',
    scope         VARCHAR(100) NOT NULL DEFAULT 'standalone',  -- standalone | integrated — see Master Data Hub notes
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_eng_client ON engagement(client_id);
CREATE INDEX ix_eng_status ON engagement(status);

-- Client-directory columns (vat_filing_frequency, stage, urgent, service_types,
-- assigned_to) are added to the existing client table by db.py's migration
-- step, not here — schema.sql's CREATE TABLE IF NOT EXISTS never touches a
-- table that already exists, so a fresh column on a live database has to go
-- through ALTER TABLE, which isn't itself idempotent in SQLite. See
-- db._migrate_client_columns().

-- Document/information follow-ups against a client — "waiting on the signed
-- management representation letter", "chasing the bank statements for Poush"
-- — independent of the document register (which is for files actually
-- received), and independent of any one engagement, since a follow-up like
-- this often predates the workfile it will eventually feed.
CREATE TABLE IF NOT EXISTS client_followup (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    item          TEXT NOT NULL,
    status        VARCHAR(100) NOT NULL DEFAULT 'pending',   -- pending | received | not_applicable
    note          TEXT,
    due_date      TEXT,
    created_by    INT REFERENCES app_user(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by    INT REFERENCES app_user(id),
    updated_at    TEXT
);
CREATE INDEX ix_followup_client ON client_followup(client_id, status);

-- Portal credentials (IRD, OCR, excise, or any custom system) kept against a
-- client so the firm can log in on the client's behalf to file returns.
-- secret_enc is never plaintext — see db.encrypt_secret()/decrypt_secret();
-- the key lives in a local file (data/cred_enc.key), never in this database
-- and never in a value returned to the browser except through the one
-- explicit "reveal" endpoint, which is audit-logged.
CREATE TABLE IF NOT EXISTS client_credential (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    `system`        TEXT NOT NULL,                     -- IRD | OCR | Excise | <custom label>
    username      TEXT,
    secret_enc    TEXT,
    notes         TEXT,
    created_by    INT REFERENCES app_user(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by    INT REFERENCES app_user(id),
    updated_at    TEXT
);
CREATE INDEX ix_credential_client ON client_credential(client_id);

-- --------------------------------------------------- 3. WORKFILES, STATE, AUTOSAVE

-- One workfile = one module instance for one engagement (e.g. "Depreciation FY2082-83")
CREATE TABLE IF NOT EXISTS workfile (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    engagement_id INT NOT NULL REFERENCES engagement(id) ON DELETE CASCADE,
    module_key    VARCHAR(100) NOT NULL,                   -- depreciation | salary_tds | biz_tax | penalty | emi | lease | ...
    title         TEXT NOT NULL,
    status        VARCHAR(100) NOT NULL DEFAULT 'open',    -- open | review | signed_off | archived
    prepared_by   INT REFERENCES app_user(id),
    reviewed_by   INT REFERENCES app_user(id),
    reviewed_at   TEXT,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_wf_eng ON workfile(engagement_id);
CREATE INDEX ix_wf_module ON workfile(module_key);

-- Current authoritative state (one row per workfile). payload = module JSON.
CREATE TABLE IF NOT EXISTS workfile_state (
    workfile_id   INT PRIMARY KEY REFERENCES workfile(id) ON DELETE CASCADE,
    payload       TEXT NOT NULL,                   -- JSON
    version       INT NOT NULL DEFAULT 1,      -- optimistic-lock counter
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by    INT REFERENCES app_user(id),
    device_id     TEXT,
    checksum      TEXT
);

-- Rolling autosave beats. The 5-minute heartbeat writes here; state table is upserted.
CREATE TABLE IF NOT EXISTS autosave_snapshot (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    workfile_id  INT NOT NULL REFERENCES workfile(id) ON DELETE CASCADE,
    payload      TEXT NOT NULL,
    origin       VARCHAR(100) NOT NULL DEFAULT 'auto',     -- auto | manual | sync | pre_import
    client_seq   INT,                          -- browser-side sequence, for offline ordering
    device_id    TEXT,
    saved_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    byte_size    INT
);
CREATE INDEX ix_snap_wf_time ON autosave_snapshot(workfile_id, saved_at DESC);

-- Offline edits that arrived out of order or conflicted, kept for the merge screen.
CREATE TABLE IF NOT EXISTS sync_conflict (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    workfile_id  INT NOT NULL REFERENCES workfile(id) ON DELETE CASCADE,
    incoming     TEXT NOT NULL,
    server_state TEXT NOT NULL,
    base_version INT,
    device_id    TEXT,
    detected_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at  TEXT,
    resolution   TEXT                              -- keep_server | keep_incoming | merged
);

-- ------------------------------------------------------------ 4. DOCUMENTS & REPORTS

CREATE TABLE IF NOT EXISTS document (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    -- SET NULL, not CASCADE: a document must survive its client or engagement
    -- being deleted — see db._fix_document_report_client_delete(). The file
    -- itself is only ever removed by an explicit document-delete action.
    engagement_id INT REFERENCES engagement(id) ON DELETE SET NULL,
    client_id     INT REFERENCES client(id) ON DELETE SET NULL,
    category      TEXT,                            -- tb | gl | bank | tax_return | agreement | other
    filename      TEXT NOT NULL,
    mime          TEXT,
    byte_size     INT,
    sha256        TEXT,
    storage_path  TEXT NOT NULL,
    uploaded_by   INT REFERENCES app_user(id),
    uploaded_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_doc_eng ON document(engagement_id);

CREATE TABLE IF NOT EXISTS report_template (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    module_key  VARCHAR(100) NOT NULL,
    format      VARCHAR(100) NOT NULL,                     -- xlsx | docx
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    spec        TEXT,                              -- JSON: sheets/sections, columns, headings
    is_default  INT NOT NULL DEFAULT 0,
    UNIQUE (module_key, format, name)
);

CREATE TABLE IF NOT EXISTS report (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    workfile_id   INT REFERENCES workfile(id) ON DELETE SET NULL,
    -- SET NULL, not CASCADE: a generated report survives its engagement (and
    -- so its client) being deleted — see db._fix_document_report_client_delete().
    engagement_id INT REFERENCES engagement(id) ON DELETE SET NULL,
    module_key    TEXT NOT NULL,
    format        TEXT NOT NULL,                   -- xlsx | docx
    template_id   INT REFERENCES report_template(id),
    filename      TEXT NOT NULL,
    storage_path  TEXT,
    params        TEXT,                            -- JSON snapshot of inputs used
    status        VARCHAR(100) NOT NULL DEFAULT 'ready',   -- queued | ready | failed
    error_text    TEXT,
    generated_by  INT REFERENCES app_user(id),
    generated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    byte_size     INT
);
CREATE INDEX ix_report_eng ON report(engagement_id, generated_at DESC);

-- ------------------------------------------------------------- 5. RATE MASTER
-- Nothing regulatory is hard-coded anywhere in the application. Every rate,
-- slab, ceiling, fee and interest percentage is read from here at run time and
-- carries its own provenance and last-verified date.

CREATE TABLE IF NOT EXISTS rate_master (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    domain              VARCHAR(100) NOT NULL,             -- income_tax | tds | vat | depreciation | penalty | payroll
    code                VARCHAR(100) NOT NULL,             -- machine key used by engines
    label               TEXT NOT NULL,
    value_num           DOUBLE,
    value_text          TEXT,
    unit                TEXT,                      -- percent | amount | months | ratio
    applies_to          VARCHAR(100),                      -- individual | couple | company | pool_a ...
    fiscal_year_label   VARCHAR(100),                      -- '2082-83'
    effective_from      TEXT,
    effective_to        TEXT,
    source              TEXT,                      -- 'Income Tax Act 2058'
    clause              TEXT,                      -- 'Sec 117(1)(a)'
    last_verified_on    TEXT,
    verification_status VARCHAR(100) NOT NULL DEFAULT 'UNVERIFIED', -- UNVERIFIED | SECONDARY | PRIMARY
    verified_by         INT REFERENCES app_user(id),
    notes               TEXT,
    UNIQUE (domain, code, fiscal_year_label, applies_to)
);
CREATE INDEX ix_rate_lookup ON rate_master(domain, fiscal_year_label);
-- ux_rate_master_dedup is created by db.py's _fix_rate_master_dedup(), not
-- here — see schema.sql's comment on this same table for why.

-- Rate Master itself is edit-locked to super admins (a Finance-Act figure is
-- firm-wide truth, not something any one user should be able to silently
-- change). Anyone can flag a rate they think is wrong here instead — a
-- one-shot remark, not a discussion thread; the admin resolves it and can
-- leave a resolution_note the submitter sees on their own remark.
CREATE TABLE IF NOT EXISTS rate_remark (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    rate_id          INT NOT NULL REFERENCES rate_master(id) ON DELETE CASCADE,
    user_id          INT NOT NULL REFERENCES app_user(id),
    remark           TEXT NOT NULL,
    status           VARCHAR(100) NOT NULL DEFAULT 'open',   -- open | resolved
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at      TEXT,
    resolved_by      INT REFERENCES app_user(id),
    resolution_note  TEXT
);
CREATE INDEX ix_rate_remark_rate ON rate_remark(rate_id);
CREATE INDEX ix_rate_remark_status ON rate_remark(status);

-- ------------------------------------------------------- 5a. INDUSTRY DEFAULTS
-- Indicative project-appraisal planning defaults for the Projection module, by
-- sector. Not statutory (no Finance Act verification workflow applies) so this
-- is deliberately a separate table from rate_master, not FY-keyed. Array-shaped
-- fields (util_ramp, cost_pct, wc_days, capex_split) are stored as JSON text.

CREATE TABLE IF NOT EXISTS industry_default (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(100) NOT NULL UNIQUE,
    category            TEXT,
    asset_turnover      DOUBLE,                      -- sales at 100% utilisation / total project cost
    util_ramp           TEXT NOT NULL,              -- JSON array, utilisation years 1-5
    cost_pct            TEXT NOT NULL,              -- JSON array [rm, labour, power, mfgoh, admin, selling] as % of sales
    repairs_pct         DOUBLE,                       -- % of gross block
    wc_days             TEXT NOT NULL,              -- JSON array [debtors, RM stock, FG stock, creditors]
    tax_rate            DOUBLE,
    hol_years           DOUBLE,
    hol_rate            DOUBLE,
    impl_months         DOUBLE,
    life_years          DOUBLE,
    tenure_years        DOUBLE,
    moratorium_years    DOUBLE,
    capex_split         TEXT NOT NULL,              -- JSON array [land,building,plant,vehicles,furniture,preop,WC margin], normalised to 1.0
    uom                 TEXT,
    revenue_mode        TEXT,                       -- turnover | capacity
    gearing_norm        DOUBLE,
    note                TEXT
);

-- --------------------------------------------------------------- 5b. FDI SECTORS
-- Foreign-equity ceiling, income-tax rate and special-industry flag by sector,
-- for the FDI module's route/eligibility check and tax computation. Structured
-- multi-column reference data (like industry_default), not a single numeric
-- rate, so it is its own table rather than rate_master rows. Not FY-keyed —
-- caps and rates here change by individual notice, not on a Finance Act cycle,
-- so each row is corrected in place when a notice changes it.

CREATE TABLE IF NOT EXISTS fdi_sector (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL UNIQUE,
    cap_pct     DOUBLE NOT NULL,
    tax_rate    DOUBLE NOT NULL,
    special     INT NOT NULL DEFAULT 0,
    note        TEXT
);

-- --------------------------------------------------------------- 5c. AUDIT
-- Two extensible catalogs behind the Internal Audit module (and, later, the
-- statutory-audit programs-by-area phase — same tables, same "Other"/
-- add-your-own pattern, no rebuild needed when that phase lands).
--
-- audit_entity_type: the list a workfile picks its entity type from. "Other"
-- is always present as a built-in row so an uncatalogued entity type never
-- blocks starting an audit — the free-text label for an "Other" pick lives
-- on the workfile's own payload, not here. Firms add their own rows the same
-- way they add a Rate Master row.
--
-- audit_criterion: the master checklist bank an Internal Audit program draws
-- its criteria from, one row per checklist item. applies_to is a JSON array
-- of audit_entity_type.name values the criterion is specific to, or the
-- literal 'all' for a generic criterion that applies regardless of entity
-- type. This is a starting bank covering the standard audit areas, not a
-- claim of exhaustiveness — the module's own UI can add more rows, the same
-- extensibility discipline as Rate Master.

CREATE TABLE IF NOT EXISTS audit_entity_type (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL UNIQUE,
    note        TEXT,
    is_builtin  INT NOT NULL DEFAULT 0,
    sort_order  INT NOT NULL DEFAULT 100
);

CREATE TABLE IF NOT EXISTS audit_criterion (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    area        VARCHAR(100) NOT NULL,
    criterion   TEXT NOT NULL,
    applies_to  VARCHAR(100) NOT NULL DEFAULT 'all',
    sort_order  INT NOT NULL DEFAULT 100,
    is_builtin  INT NOT NULL DEFAULT 0
);
CREATE INDEX ix_audit_criterion_area ON audit_criterion(area);

-- ------------------------------------------------- 6. COMPLIANCE CALENDAR & TASKS
-- This is the "works day and night" layer: the background worker sweeps these.

CREATE TABLE IF NOT EXISTS obligation_type (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    code          VARCHAR(100) NOT NULL UNIQUE,
    title         TEXT NOT NULL,
    statute       TEXT,
    clause        TEXT,
    recurrence    TEXT,                            -- monthly | quarterly | annual | one_off
    due_rule      TEXT,                            -- human-readable rule, e.g. '25th of following month'
    applies_when  TEXT,                            -- JSON predicate over client attributes
    last_verified_on TEXT,
    verification_status VARCHAR(100) NOT NULL DEFAULT 'UNVERIFIED'
);

CREATE TABLE IF NOT EXISTS deadline (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    client_id        INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    obligation_code  VARCHAR(100) REFERENCES obligation_type(code),
    title            TEXT NOT NULL,
    period_label     TEXT,                         -- 'Shrawan 2082'
    due_date_bs      TEXT,
    due_date_ad      VARCHAR(100) NOT NULL,
    status           VARCHAR(100) NOT NULL DEFAULT 'pending', -- pending | filed | waived | late
    filed_on         TEXT,
    reference_no     TEXT,
    assigned_to      INT REFERENCES app_user(id),
    reminder_days    VARCHAR(100) DEFAULT '7,3,1',
    last_notified_at TEXT
);
CREATE INDEX ix_deadline_due ON deadline(due_date_ad, status);

CREATE TABLE IF NOT EXISTS task (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    engagement_id INT REFERENCES engagement(id) ON DELETE CASCADE,
    client_id     INT REFERENCES client(id) ON DELETE CASCADE,
    title         TEXT NOT NULL,
    detail        TEXT,
    assignee_id   INT REFERENCES app_user(id),
    priority      VARCHAR(100) DEFAULT 'normal',
    due_date      VARCHAR(100),
    status        VARCHAR(100) NOT NULL DEFAULT 'open',
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at  TEXT
);
CREATE INDEX ix_task_status ON task(status, due_date);

CREATE TABLE IF NOT EXISTS time_entry (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    engagement_id INT REFERENCES engagement(id) ON DELETE CASCADE,
    user_id       INT REFERENCES app_user(id),
    work_date     TEXT NOT NULL,
    hours         DOUBLE NOT NULL,
    narrative     TEXT,
    billable      INT NOT NULL DEFAULT 1,
    rate          DOUBLE,
    invoice_id    INT REFERENCES invoice(id) ON DELETE SET NULL   -- NULL = not yet billed
);

-- --------------------------------------------------------------- 6a. BILLING

CREATE TABLE IF NOT EXISTS invoice (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    firm_id       INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    invoice_no    VARCHAR(100) NOT NULL,
    invoice_date  TEXT NOT NULL,
    due_date      TEXT,
    status        VARCHAR(100) NOT NULL DEFAULT 'draft',    -- draft | sent | paid | void
    subtotal      DOUBLE NOT NULL DEFAULT 0,
    vat_rate      DOUBLE NOT NULL DEFAULT 0,
    vat_amount    DOUBLE NOT NULL DEFAULT 0,
    total         DOUBLE NOT NULL DEFAULT 0,
    paid_amount   DOUBLE NOT NULL DEFAULT 0,
    paid_date     TEXT,
    notes         TEXT,
    created_by    INT REFERENCES app_user(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (firm_id, invoice_no)
);
CREATE INDEX ix_invoice_client ON invoice(client_id, status);

CREATE TABLE IF NOT EXISTS invoice_line (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id    INT NOT NULL REFERENCES invoice(id) ON DELETE CASCADE,
    engagement_id INT REFERENCES engagement(id),
    description   TEXT NOT NULL,
    hours         DOUBLE,
    rate          DOUBLE,
    amount        DOUBLE NOT NULL
);
CREATE INDEX ix_invoice_line_invoice ON invoice_line(invoice_id);

-- Background jobs: overnight report runs, deadline sweeps, backup, blog publishing.
CREATE TABLE IF NOT EXISTS job_queue (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    job_type     TEXT NOT NULL,                    -- report | deadline_sweep | backup | reminder
    payload      TEXT,
    run_after    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    attempts     INT NOT NULL DEFAULT 0,
    status       VARCHAR(100) NOT NULL DEFAULT 'queued',   -- queued | running | done | failed
    last_error   TEXT,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at  TEXT
);
CREATE INDEX ix_job_pick ON job_queue(status, run_after);

CREATE TABLE IF NOT EXISTS notification (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT REFERENCES app_user(id) ON DELETE CASCADE,
    kind       TEXT NOT NULL,
    title      TEXT NOT NULL,
    body       TEXT,
    link       TEXT,
    read_at    TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------------- 7. AUDIT TRAIL

CREATE TABLE IF NOT EXISTS audit_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    actor_id   INT REFERENCES app_user(id),
    action     TEXT NOT NULL,                      -- create | update | delete | export | login | sync
    entity     VARCHAR(100) NOT NULL,
    entity_id  VARCHAR(100),
    summary    TEXT,
    ip         TEXT,
    at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_audit_entity ON audit_log(entity, entity_id);

-- ------------------------------------------------------------ 7a. CA ASSISTANT
-- One row per chat turn — an audit trail of what the assistant said, when,
-- and against what <knowledge_context> snapshot, so a conclusion can be
-- reconstructed later if a rate it relied on turns out to have been stale.
-- The live conversation itself is stateless per the Messages API (the
-- browser resends full history each request); this table is the durable
-- record, not the source of truth for an in-progress chat.

CREATE TABLE IF NOT EXISTS ca_assistant_message (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    user_id            INT REFERENCES app_user(id),
    client_id          INT REFERENCES client(id),
    engagement_id      INT REFERENCES engagement(id),
    user_message       TEXT NOT NULL,
    assistant_reply    TEXT,
    knowledge_context  TEXT,
    client_context     TEXT,
    model              TEXT,
    input_tokens       INT,
    output_tokens      INT,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ca_assistant_user ON ca_assistant_message(user_id, created_at DESC);

-- ------------------------------------------------------------ 8. CONTENT / SEO
-- The blog engine that makes the practice findable.

CREATE TABLE IF NOT EXISTS blog_category (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    slug   VARCHAR(100) NOT NULL UNIQUE,
    name   TEXT NOT NULL,
    intro  TEXT
);

CREATE TABLE IF NOT EXISTS blog_post (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    slug             VARCHAR(100) NOT NULL UNIQUE,
    title            TEXT NOT NULL,
    h1               TEXT,
    meta_description TEXT,
    focus_keyword    TEXT,
    secondary_keywords TEXT,
    category_id      INT REFERENCES blog_category(id),
    body_md          TEXT NOT NULL,
    reading_minutes  INT,
    author_id        INT REFERENCES app_user(id),
    status           VARCHAR(100) NOT NULL DEFAULT 'draft', -- draft | scheduled | published
    published_at     VARCHAR(100),
    updated_at       TEXT,
    canonical_url    TEXT,
    og_image_path    TEXT,
    schema_type      VARCHAR(100) DEFAULT 'Article',
    review_due_on    TEXT,                          -- tax content must be re-checked each FY
    views            INT NOT NULL DEFAULT 0
);
CREATE INDEX ix_post_status ON blog_post(status, published_at DESC);

CREATE TABLE IF NOT EXISTS blog_faq (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    post_id  INT NOT NULL REFERENCES blog_post(id) ON DELETE CASCADE,
    question TEXT NOT NULL,
    answer   TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS blog_internal_link (
    from_post_id INT NOT NULL REFERENCES blog_post(id) ON DELETE CASCADE,
    to_post_id   INT NOT NULL REFERENCES blog_post(id) ON DELETE CASCADE,
    anchor_text  TEXT,
    PRIMARY KEY (from_post_id, to_post_id)
);

CREATE TABLE IF NOT EXISTS `lead` (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       TEXT,
    email      TEXT,
    phone      TEXT,
    message    TEXT,
    source_post_id INT REFERENCES blog_post(id),
    utm_source TEXT,
    status     VARCHAR(100) NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS setting (
    `key`        VARCHAR(100) PRIMARY KEY,
    value      TEXT,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ------------------------------------------------------- 9. TRANSFER PRICING
-- A genuinely different shape from every module above: those store one JSON
-- workfile payload per engagement and compute offline. Transfer pricing needs
-- real cross-row querying — benchmarking a set of comparables, aggregating
-- risk across a client's whole transaction register, an entity-to-entity
-- relationship graph — so it gets real tables instead. Every regulatory rule
-- here follows the same discipline as rate_master: UNVERIFIED until a human
-- confirms it against the actual law, never treated as settled fact.

CREATE TABLE IF NOT EXISTS tp_country (
    code         VARCHAR(100) PRIMARY KEY,               -- 'NP' | 'IN' | 'AE'
    name         TEXT NOT NULL,
    currency     TEXT NOT NULL,
    tax_authority TEXT,
    is_active    INT NOT NULL DEFAULT 1
);

-- The Jurisdiction Configuration Engine. One row = one rule, scoped to a
-- country and (optionally) a financial year, carrying its own legal
-- provenance so a study prepared for FY2081-82 can still be reproduced with
-- the rules that actually applied then, even after later rows supersede them.
CREATE TABLE IF NOT EXISTS tp_regulatory_rule (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    country             VARCHAR(100) NOT NULL REFERENCES tp_country(code),
    domain              VARCHAR(100) NOT NULL,             -- threshold | method | documentation | penalty | filing | definition | deadline
    code                VARCHAR(100) NOT NULL,             -- machine key, e.g. 'related_party_ownership_threshold'
    label               TEXT NOT NULL,
    value_num           DOUBLE,
    value_text          TEXT,
    unit                TEXT,
    applies_to          VARCHAR(100),                      -- entity type / transaction type scoping, optional
    financial_year_label VARCHAR(100),                     -- NULL = standing rule until superseded
    effective_from      TEXT,
    effective_to        TEXT,
    law_name            TEXT,                      -- 'Income Tax Act 2058', 'Income-tax Act 1961', 'UAE CT Law (Federal Decree-Law No. 47 of 2022)'
    section_article      TEXT,                      -- 'Sec 33', 'Rule 10D', 'Article 34'
    source_authority     TEXT,                      -- 'IRD' | 'CBDT' | 'FTA'
    interpretation       TEXT,
    verification_status VARCHAR(100) NOT NULL DEFAULT 'UNVERIFIED',
    verified_by          INT REFERENCES app_user(id),
    last_verified_on     TEXT,
    version              INT NOT NULL DEFAULT 1,
    notes                TEXT,
    UNIQUE (country, domain, code, financial_year_label, applies_to)
);
CREATE INDEX ix_tp_rule_lookup ON tp_regulatory_rule(country, domain, financial_year_label);
-- The inline UNIQUE above never actually dedupes a "standing rule" (NULL
-- financial_year_label) against itself, because SQL treats every NULL as
-- distinct from every other NULL for uniqueness purposes — most rows here
-- ARE standing rules, so re-running seed.sql's INSERT OR IGNORE block (e.g.
-- via init_db(force_seed=True), used to recover accidentally-deleted rows)
-- silently duplicated every one of them instead of being ignored. This
-- expression index uses COALESCE to give NULL a real, comparable value, so
-- it dedupes correctly and INSERT OR IGNORE becomes actually idempotent.
CREATE UNIQUE INDEX ux_tp_rule_dedup
    ON tp_regulatory_rule(country, domain, code, (COALESCE(financial_year_label, '')), (COALESCE(applies_to, '')));

-- One row per party in a client's own group structure — most rows here are
-- NOT the CA firm's own clients, just counterparties named in the client's
-- related-party transactions (a UAE parent, an Indian subsidiary, etc.).
CREATE TABLE IF NOT EXISTS tp_entity (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    name          TEXT NOT NULL,
    country       VARCHAR(100) REFERENCES tp_country(code),
    entity_type   TEXT,                            -- distributor | manufacturer | service_provider | ip_owner | financing_entity | holding | other
    pan_or_tin    TEXT,
    is_the_client INT NOT NULL DEFAULT 0,       -- marks the tested-party entity that IS this CA client
    notes         TEXT,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_entity_client ON tp_entity(client_id);

CREATE TABLE IF NOT EXISTS tp_relationship (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    client_id           INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    entity_a_id         INT NOT NULL REFERENCES tp_entity(id) ON DELETE CASCADE,
    entity_b_id         INT NOT NULL REFERENCES tp_entity(id) ON DELETE CASCADE,
    relationship_type   TEXT,                       -- parent | subsidiary | sister_company | joint_venture | branch | pe | common_control | other
    ownership_pct       DOUBLE,
    voting_rights_pct   DOUBLE,
    control_basis       TEXT,                       -- free text: what fact pattern established related-party/associated-enterprise status
    country_test_basis  VARCHAR(100) REFERENCES tp_country(code), -- which country's definition this was evaluated under
    notes               TEXT,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_reln_client ON tp_relationship(client_id);

-- The Transaction Register.
CREATE TABLE IF NOT EXISTS tp_transaction (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    client_id             INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    financial_year_label  VARCHAR(100) NOT NULL,
    tested_entity_id      INT REFERENCES tp_entity(id),
    counterparty_entity_id INT REFERENCES tp_entity(id),
    source_country        VARCHAR(100) REFERENCES tp_country(code),
    destination_country   VARCHAR(100) REFERENCES tp_country(code),
    transaction_category  TEXT NOT NULL,            -- goods | services | financing | royalty | guarantee | cost_allocation | lease | restructuring | other
    transaction_subtype   TEXT,                     -- free text, e.g. 'contract manufacturing', 'management fee'
    description           TEXT,
    currency              TEXT,
    amount                DOUBLE,
    direction              TEXT,                     -- inbound | outbound
    method_selected        TEXT,                     -- cached from tp_method_selection, for dashboard speed
    risk_level             TEXT,                     -- cached from tp_risk_assessment
    detail_json             TEXT,                     -- type-specific facts: loan rate/tenure, royalty rate/base, guarantee fee %, cost-allocation basis
    created_by              INT REFERENCES app_user(id),
    created_at               DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_txn_client ON tp_transaction(client_id, financial_year_label);

CREATE TABLE IF NOT EXISTS tp_far_analysis (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id     INT REFERENCES tp_transaction(id) ON DELETE CASCADE,
    entity_id          INT REFERENCES tp_entity(id) ON DELETE CASCADE,
    functions          TEXT,                         -- JSON
    assets             TEXT,                         -- JSON
    risks              TEXT,                         -- JSON
    economic_substance TEXT,
    functional_profile TEXT,                         -- routine_distributor | limited_risk_distributor | full_risk_distributor |
                                                       -- contract_manufacturer | full_risk_manufacturer | service_provider |
                                                       -- limited_risk_service_provider | financing_entity | ip_owner | ip_licensee | entrepreneurial
    conclusion         TEXT,
    prepared_by        INT REFERENCES app_user(id),
    updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_far_txn ON tp_far_analysis(transaction_id);

CREATE TABLE IF NOT EXISTS tp_method_selection (
    id                     INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id         INT NOT NULL REFERENCES tp_transaction(id) ON DELETE CASCADE,
    recommended_method     TEXT,
    recommendation_basis   TEXT,
    selected_method        TEXT,
    override_justification TEXT,
    pli                    TEXT,                     -- operating_margin | berry_ratio | roa | roce | full_cost_markup | other
    created_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_method_txn ON tp_method_selection(transaction_id);

CREATE TABLE IF NOT EXISTS tp_comparable (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id        INT NOT NULL REFERENCES tp_transaction(id) ON DELETE CASCADE,
    company_name          TEXT,
    country               VARCHAR(100) REFERENCES tp_country(code),
    financial_year_label  TEXT,
    pli_value             DOUBLE,
    financials            TEXT,                      -- JSON: revenue/operating_profit/operating_cost/total_assets/... flexible across PLI types
    source                TEXT,                      -- 'manual_upload' etc.
    include_in_set        INT NOT NULL DEFAULT 1,
    exclusion_reason      TEXT,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_comp_txn ON tp_comparable(transaction_id);

CREATE TABLE IF NOT EXISTS tp_benchmark_result (
    id                            INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id                INT NOT NULL REFERENCES tp_transaction(id) ON DELETE CASCADE,
    stats_json                    TEXT,               -- min/max/mean/median/q1/q3/iqr/weighted_avg/weighted_median/adjusted_range
    working_capital_adjustment_json TEXT,
    computed_by                   INT REFERENCES app_user(id),
    computed_at                   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_bench_txn ON tp_benchmark_result(transaction_id);

CREATE TABLE IF NOT EXISTS tp_risk_assessment (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL REFERENCES tp_transaction(id) ON DELETE CASCADE,
    factors_json   TEXT,                              -- the scored factors and their individual contributions
    risk_level     TEXT,                               -- Low | Medium | High | Very High
    explanation    TEXT,
    computed_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_risk_txn ON tp_risk_assessment(transaction_id);

CREATE TABLE IF NOT EXISTS tp_adjustment (
    id                     INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id         INT NOT NULL REFERENCES tp_transaction(id) ON DELETE CASCADE,
    actual_result          DOUBLE,
    arm_length_result      DOUBLE,
    adjustment_amount      DOUBLE,
    taxable_income_impact  DOUBLE,
    tax_impact             DOUBLE,
    withholding_impact     DOUBLE,
    interest_exposure      DOUBLE,
    penalty_exposure       DOUBLE,
    total_exposure         DOUBLE,
    basis_notes            TEXT,
    computed_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_adj_txn ON tp_adjustment(transaction_id);

CREATE TABLE IF NOT EXISTS tp_document_checklist_item (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    financial_year_label VARCHAR(100) NOT NULL,
    rule_id       INT REFERENCES tp_regulatory_rule(id),
    label         TEXT NOT NULL,
    status        VARCHAR(100) NOT NULL DEFAULT 'missing',    -- missing | in_progress | complete
    document_id   INT REFERENCES document(id) ON DELETE SET NULL,
    notes         TEXT,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_doc_client ON tp_document_checklist_item(client_id, financial_year_label);

CREATE TABLE IF NOT EXISTS tp_working_paper (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    client_id       INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    financial_year_label TEXT,
    ref             TEXT NOT NULL,                    -- TP-01, FAR-01, BENCH-01, ADJ-01, DOC-01...
    title           TEXT,
    content         TEXT,                             -- free text / JSON
    prepared_by     INT REFERENCES app_user(id),
    prepared_at     TEXT,
    reviewed_by     INT REFERENCES app_user(id),
    reviewed_at     TEXT,
    review_status   VARCHAR(100) NOT NULL DEFAULT 'open',      -- open | approved | rejected | sent_back
    review_comment  TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tp_wp_client ON tp_working_paper(client_id);

-- The Treaty Analysis Engine (spec). A treaty relationship is genuinely a
-- different shape from a single tp_regulatory_rule row — it's between TWO
-- countries, not scoped to one — so it gets its own small table rather than
-- being forced into the country-scoped rule table. Existence/in-force status
-- of a DTAA is itself a fact that changes (renegotiation, protocols) and
-- must never be asserted from memory — every row here starts UNVERIFIED with
-- in_force left NULL (unknown) until a human confirms it, exactly like every
-- other regulatory fact in this module. This never overrides domestic law:
-- the app's own AI/report layer is instructed to treat treaty relief as a
-- separate question from the domestic position, not an automatic override.
CREATE TABLE IF NOT EXISTS tp_treaty (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    country_a           VARCHAR(100) NOT NULL REFERENCES tp_country(code),
    country_b           VARCHAR(100) NOT NULL REFERENCES tp_country(code),
    treaty_name         TEXT,
    in_force            INT,                          -- NULL = unknown/unconfirmed, 0 = not in force, 1 = in force
    signed_date         TEXT,
    effective_date       TEXT,
    associated_enterprise_article TEXT,                    -- article covering related-party/associated-enterprise adjustments
    business_profits_article      TEXT,
    permanent_establishment_article TEXT,
    interest_article     TEXT,
    royalty_article       TEXT,
    technical_service_article TEXT,
    mutual_agreement_procedure_article TEXT,
    source_authority     TEXT,
    verification_status  VARCHAR(100) NOT NULL DEFAULT 'UNVERIFIED',
    verified_by           INT REFERENCES app_user(id),
    last_verified_on      TEXT,
    notes                 TEXT,
    UNIQUE (country_a, country_b)
);

-- The Multi-Currency Engine (spec) — deliberately a manually-logged rate
-- snapshot, not a live/fetched rate source (this server has no outbound
-- internet access and the app's own discipline is to never assert a
-- figure it can't source). A preparer records the rate they're using and
-- where it came from, exactly like a comparable or a working paper — a
-- fact with provenance, not a computed truth. `rate` converts 1 unit of
-- from_currency into to_currency. Used for reporting-currency roll-ups
-- (e.g. the Global Group Dashboard) — not a substitute for a proper
-- settlement-date/accounting-date exchange-difference ledger, which this
-- Phase still does not attempt.
CREATE TABLE IF NOT EXISTS tp_fx_rate (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    from_currency  VARCHAR(100) NOT NULL,
    to_currency    VARCHAR(100) NOT NULL,
    rate           DOUBLE NOT NULL,
    rate_date      VARCHAR(100) NOT NULL,
    source         TEXT,
    notes          TEXT,
    created_by     INT REFERENCES app_user(id),
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (from_currency, to_currency, rate_date)
);
CREATE INDEX ix_tp_fx_lookup ON tp_fx_rate(from_currency, to_currency, rate_date DESC);

-- ------------------------------------------------------------- 10. APPOINTMENTS
-- A slot only exists to be booked if a staff member deliberately opened it —
-- there is no "everything is bookable unless blocked" default, per the
-- explicit requirement that an unopened time shows as unavailable on the
-- public booking page. Double-booking is prevented at the database level,
-- not just in application logic: a slot can only ever have one booking
-- (UNIQUE on slot_id) and booking flips the slot's own status atomically in
-- the same transaction as the insert (see app.py's public-booking route) —
-- two simultaneous requests for the same slot can't both succeed.
CREATE TABLE IF NOT EXISTS appointment_slot (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    firm_id        INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    date           VARCHAR(100) NOT NULL,                 -- AD date, YYYY-MM-DD
    start_time     VARCHAR(100) NOT NULL,                 -- HH:MM, 24-hour
    end_time       TEXT NOT NULL,
    price_per_hour DOUBLE NOT NULL,
    status         VARCHAR(100) NOT NULL DEFAULT 'open',   -- open | booked | blocked
    notes          TEXT,
    created_by     INT REFERENCES app_user(id),
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (firm_id, date, start_time)
);
CREATE INDEX ix_appt_slot_lookup ON appointment_slot(firm_id, date, status);

-- slot_id is deliberately NOT a plain UNIQUE column: a cancelled booking
-- keeps its row (for history/audit — cancelling never deletes it), and a
-- plain UNIQUE would then permanently block anyone from ever booking that
-- slot again, since the constraint doesn't know "cancelled" means the slot
-- is free again. The partial unique index below enforces the real rule —
-- at most one NON-cancelled booking per slot — while still leaving old
-- cancelled rows in place.
CREATE TABLE IF NOT EXISTS appointment_booking (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    slot_id        INT NOT NULL REFERENCES appointment_slot(id) ON DELETE CASCADE,
    firm_id        INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    name           TEXT NOT NULL,
    email          TEXT NOT NULL,
    phone          TEXT,
    company        TEXT,
    method         VARCHAR(100) NOT NULL DEFAULT 'video',  -- phone | video | in_person
    message        TEXT,
    price          DOUBLE NOT NULL,
    status         VARCHAR(100) NOT NULL DEFAULT 'pending', -- pending | confirmed | cancelled
    cancel_reason  TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    active_slot_id INT GENERATED ALWAYS AS (CASE WHEN status != 'cancelled' THEN slot_id END) VIRTUAL
);
CREATE UNIQUE INDEX ux_appt_booking_active_slot ON appointment_booking(active_slot_id);
CREATE INDEX ix_appt_booking_firm ON appointment_booking(firm_id, status);

-- --------------------------------------------------- 11. PRACTICE MANAGEMENT
-- The eighteen automators requested here are not eighteen separate data
-- models — onboarding/document-request/closure are all "a checklist against
-- a client," KYC + conflict-check + risk assessment are, in real practice,
-- one combined client & engagement acceptance memo, and an engagement
-- letter/proposal/fee quotation are the same {scope, fee, validity, status}
-- shape with a different document template. Deadline Tracker, Compliance
-- Calendar, Fee Billing, Outstanding Fees and Client Follow-up are NOT
-- rebuilt here — they already exist (deadline, invoice, client_followup)
-- and this module's UI surfaces them rather than duplicating the data.
-- That leaves five genuinely new tables.

CREATE TABLE IF NOT EXISTS pm_checklist_item (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    engagement_id INT REFERENCES engagement(id) ON DELETE CASCADE,
    stage         VARCHAR(100) NOT NULL,                     -- onboarding | document_request | closure
    label         TEXT NOT NULL,
    status        VARCHAR(100) NOT NULL DEFAULT 'pending',    -- pending | received | done | na
    due_date      TEXT,
    notes         TEXT,
    created_by    INT REFERENCES app_user(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TEXT
);
CREATE INDEX ix_pm_checklist_client ON pm_checklist_item(client_id, stage);

-- One combined acceptance record per client (or per client+engagement, for
-- a firm that re-runs acceptance each engagement) — KYC, conflict check,
-- risk assessment and the final go/no-go decision travel together because
-- that is how an acceptance decision actually gets made and reviewed.
CREATE TABLE IF NOT EXISTS pm_acceptance (
    id                       INT AUTO_INCREMENT PRIMARY KEY,
    client_id                INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    engagement_id            INT REFERENCES engagement(id) ON DELETE CASCADE,
    kyc_identity_verified    INT NOT NULL DEFAULT 0,
    kyc_beneficial_ownership INT NOT NULL DEFAULT 0,
    kyc_source_of_funds      INT NOT NULL DEFAULT 0,
    kyc_pep_check            INT NOT NULL DEFAULT 0,
    kyc_notes                TEXT,
    conflict_search_names    TEXT,
    conflict_matches_json    TEXT,
    conflict_decision        TEXT,                    -- none_found | cleared_with_safeguards | declined
    conflict_notes           TEXT,
    risk_factors_json        TEXT,
    risk_level               TEXT,                    -- Low | Medium | High
    risk_notes               TEXT,
    acceptance_decision      VARCHAR(100) NOT NULL DEFAULT 'pending', -- pending | accepted | accepted_with_conditions | declined
    acceptance_conditions    TEXT,
    decided_by               INT REFERENCES app_user(id),
    decided_at               TEXT,
    created_at               DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_pm_acceptance_client ON pm_acceptance(client_id);

-- Proposal, fee quotation and engagement letter share this one shape; the
-- report layer picks the document template from `type`.
CREATE TABLE IF NOT EXISTS pm_proposal (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    client_id         INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    engagement_id     INT REFERENCES engagement(id) ON DELETE CASCADE,
    type              VARCHAR(100) NOT NULL,                  -- proposal | fee_quote | engagement_letter
    title             TEXT,
    scope_of_services TEXT,
    fee_structure     TEXT,                           -- free text: fixed / hourly / retainer, and the basis
    fee_amount        DOUBLE,
    validity_date     TEXT,
    status            VARCHAR(100) NOT NULL DEFAULT 'draft',   -- draft | sent | accepted | declined | expired
    notes             TEXT,
    created_by        INT REFERENCES app_user(id),
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_pm_proposal_client ON pm_proposal(client_id, type);

CREATE TABLE IF NOT EXISTS pm_assignment (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    engagement_id INT REFERENCES engagement(id) ON DELETE CASCADE,
    task          TEXT NOT NULL,
    assigned_to   INT REFERENCES app_user(id),
    due_date      TEXT,
    priority      VARCHAR(100) NOT NULL DEFAULT 'normal',      -- low | normal | high | urgent
    status        VARCHAR(100) NOT NULL DEFAULT 'pending',     -- pending | in_progress | done
    notes         TEXT,
    created_by    INT REFERENCES app_user(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_pm_assignment_staff ON pm_assignment(assigned_to, status);
CREATE INDEX ix_pm_assignment_client ON pm_assignment(client_id);

-- Actual hours are never duplicated here — they're read live from the
-- existing time_entry table and compared against the budget at query time.
CREATE TABLE IF NOT EXISTS pm_time_budget (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    engagement_id  INT NOT NULL REFERENCES engagement(id) ON DELETE CASCADE,
    task           TEXT NOT NULL,
    staff_id       INT REFERENCES app_user(id),    -- NULL = budget for the whole engagement, not one person
    budgeted_hours DOUBLE NOT NULL,
    notes          TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_pm_budget_eng ON pm_time_budget(engagement_id);

-- ------------------------------------------------- 12. FORENSIC AUDIT & INVESTIGATION
-- Sixteen named detectors, not sixteen data models: every detector reads
-- the same normalized transaction row shape (a journal entry, payment,
-- invoice, payroll line, vendor master row or expense claim all reduce to
-- date/party/amount/reference/account/actor + a handful of type-specific
-- extras), and "Fraud Red Flag Detector" / "Forensic Transaction Analyzer"
-- are the same feature — run every applicable check on a dataset and browse
-- the combined findings register. Financial Statement Fraud Analyzer
-- (Beneish M-Score) is the one exception: it works on two periods of
-- aggregate figures, not transaction rows, so it's compute-only and its
-- result is stored as a single fa_finding if the preparer chooses to keep it.
CREATE TABLE IF NOT EXISTS fa_dataset (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    name          TEXT NOT NULL,
    dataset_type  TEXT NOT NULL,     -- journal_entries | payments | invoices | payroll | vendors | expenses
    period_label  TEXT,
    row_count     INT NOT NULL DEFAULT 0,
    uploaded_by   INT REFERENCES app_user(id),
    uploaded_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_fa_dataset_client ON fa_dataset(client_id);

CREATE TABLE IF NOT EXISTS fa_transaction (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    dataset_id    INT NOT NULL REFERENCES fa_dataset(id) ON DELETE CASCADE,
    row_index     INT NOT NULL,
    date          TEXT,
    reference_no  TEXT,
    party         TEXT,               -- vendor / employee / counterparty name, depending on dataset_type
    description   TEXT,
    amount        DOUBLE,
    account       TEXT,
    actor         TEXT,                -- posted_by / approver / employee, depending on dataset_type
    extra_json    TEXT                 -- type-specific extras: pan_no, bank_account, address, department...
);
CREATE INDEX ix_fa_txn_dataset ON fa_transaction(dataset_id);

CREATE TABLE IF NOT EXISTS fa_finding (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    dataset_id     INT REFERENCES fa_dataset(id) ON DELETE CASCADE,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    transaction_id INT REFERENCES fa_transaction(id) ON DELETE CASCADE,
    detector       VARCHAR(100) NOT NULL,      -- benford | duplicate_payment | duplicate_invoice | round_number |
                                        -- related_party | unusual_je | ghost_employee | payroll_fraud |
                                        -- vendor_fraud | expense_fraud | revenue_manipulation | cash_leakage |
                                        -- beneish_m_score
    severity       VARCHAR(100) NOT NULL DEFAULT 'Medium',  -- Low | Medium | High
    title          TEXT NOT NULL,
    description    TEXT,
    evidence_json  TEXT,
    status         VARCHAR(100) NOT NULL DEFAULT 'open',    -- open | reviewed | confirmed | false_positive
    reviewed_by    INT REFERENCES app_user(id),
    reviewed_at    TEXT,
    notes          TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_fa_finding_client ON fa_finding(client_id, status);
CREATE INDEX ix_fa_finding_dataset ON fa_finding(dataset_id, detector);

CREATE TABLE IF NOT EXISTS fa_working_paper (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    dataset_id     INT REFERENCES fa_dataset(id) ON DELETE SET NULL,
    ref            TEXT NOT NULL,
    title          TEXT,
    content        TEXT,
    prepared_by    INT REFERENCES app_user(id),
    prepared_at    TEXT,
    reviewed_by    INT REFERENCES app_user(id),
    reviewed_at    TEXT,
    review_status  VARCHAR(100) NOT NULL DEFAULT 'open',     -- open | approved | rejected | sent_back
    review_comment TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_fa_wp_client ON fa_working_paper(client_id);

-- --------------------------------------------------- 13. CORPORATE ADVISORY
-- Company-secretarial / Companies Act compliance — a domain this app has had
-- no data model for at all (client only carries entity_type/PAN/VAT). Fifteen
-- named tools reduce to five real tables: one profile row per client
-- (ca_entity_profile), one shareholder register (ca_shareholder) that alone
-- powers the Shareholding Structure Analyzer, one capital-events register
-- (ca_share_transaction) whose `event_type` column carries Rights Issue,
-- Bonus Share, Private Placement, Capital Reduction, Share Transfer and plain
-- Allotment/Buyback as one shape rather than six near-identical tables (each
-- checker is then just that register filtered to its event_type plus a
-- completeness read on the approval-date columns already on the row — never
-- a fabricated statutory threshold), one meeting/resolution pair
-- (ca_board_meeting + ca_resolution, the latter's `resolution_type` carrying
-- both Board Resolution Generator and Shareholder Resolution Generator), and
-- one general checklist register (ca_checklist_item) seeded from a static
-- template the same way pm_checklist_item is, covering both the Company
-- Incorporation Advisory Tool's tracked steps and the general Company Law
-- Compliance Checklist. The Company Compliance Calendar and Capital Structure
-- Optimizer are NOT rebuilt here — they are, respectively, the existing
-- deadline/obligation_type tables (section 6) seeded with OCR/Companies-Act
-- obligation types, and the already-built Debt vs Equity Funding Analyzer in
-- capital_advisory — both cross-linked from this module's UI instead.
CREATE TABLE IF NOT EXISTS ca_entity_profile (
    client_id            INT PRIMARY KEY REFERENCES client(id) ON DELETE CASCADE,
    registration_no      TEXT,                        -- OCR registration number
    incorporation_date   TEXT,
    entity_class         TEXT,                        -- private | public | listed_public
    authorized_capital   DOUBLE,
    issued_capital       DOUBLE,
    paid_up_capital      DOUBLE,
    share_face_value     DOUBLE DEFAULT 100,
    min_directors        INT,
    max_directors        INT,
    board_meeting_gap_days_max INT,                -- firm's own working assumption, not a cited statute number
    auditor_name         TEXT,
    notes                TEXT,
    updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ca_shareholder (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    name           TEXT NOT NULL,
    pan            TEXT,
    address        TEXT,
    share_class    VARCHAR(100) NOT NULL DEFAULT 'equity',     -- equity | preference
    shares_held    DOUBLE NOT NULL DEFAULT 0,
    is_related_party INT NOT NULL DEFAULT 0,
    is_director    INT NOT NULL DEFAULT 0,
    since_date     TEXT,
    notes          TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ca_shareholder_client ON ca_shareholder(client_id);

CREATE TABLE IF NOT EXISTS ca_share_transaction (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    client_id           INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    event_type          VARCHAR(100) NOT NULL,   -- allotment | rights_issue | bonus_issue | private_placement |
                                          -- share_transfer | capital_reduction | buyback
    shareholder_id      INT REFERENCES ca_shareholder(id) ON DELETE SET NULL,
    from_shareholder_id INT REFERENCES ca_shareholder(id) ON DELETE SET NULL,  -- for share_transfer
    shares               DOUBLE NOT NULL DEFAULT 0,
    price_per_share       DOUBLE,
    transaction_date      TEXT,
    board_approval_date   TEXT,
    agm_approval_date     TEXT,          -- special/ordinary resolution date, where the event needs one
    ocr_filed_date        TEXT,
    status                VARCHAR(100) NOT NULL DEFAULT 'proposed',  -- proposed | approved | filed | completed
    detail_json           TEXT,          -- event-specific figures: rights_ratio/subscription_price, bonus_ratio,
                                          -- reduction_ratio, placement investor name, etc.
    notes                 TEXT,
    created_by            INT REFERENCES app_user(id),
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ca_share_txn_client ON ca_share_transaction(client_id, event_type);

CREATE TABLE IF NOT EXISTS ca_board_meeting (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    client_id       INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    meeting_type    VARCHAR(100) NOT NULL DEFAULT 'board',   -- board | agm | egm
    meeting_date    VARCHAR(100) NOT NULL,
    notice_date     TEXT,
    quorum_present  INT,             -- number of directors/members actually present
    agenda          TEXT,
    attendees       TEXT,                -- free text list
    minutes_recorded INT NOT NULL DEFAULT 0,
    minutes_filed_date TEXT,
    status          VARCHAR(100) NOT NULL DEFAULT 'scheduled',  -- scheduled | held | cancelled
    notes           TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ca_meeting_client ON ca_board_meeting(client_id, meeting_date);

CREATE TABLE IF NOT EXISTS ca_resolution (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    client_id        INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    meeting_id       INT REFERENCES ca_board_meeting(id) ON DELETE SET NULL,
    resolution_type  VARCHAR(100) NOT NULL,      -- board | ordinary | special
    category         TEXT,               -- free text: e.g. 'Dividend declaration', 'Director appointment'
    title            TEXT,
    resolution_text  TEXT,
    resolution_date  TEXT,
    status           VARCHAR(100) NOT NULL DEFAULT 'draft',   -- draft | passed | filed
    filed_with       TEXT,               -- e.g. 'OCR'
    reference_no     TEXT,
    created_by       INT REFERENCES app_user(id),
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ca_resolution_client ON ca_resolution(client_id, resolution_type);

CREATE TABLE IF NOT EXISTS ca_checklist_item (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    area          VARCHAR(100) NOT NULL,         -- 'Incorporation' | 'Statutory registers' | 'AGM' | 'Board meetings' | 'Filings' | ...
    criterion     TEXT NOT NULL,
    status        VARCHAR(100) NOT NULL DEFAULT 'pending',   -- pending | done | na
    due_date      TEXT,
    evidence      TEXT,
    remark        TEXT,
    created_by    INT REFERENCES app_user(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TEXT
);
CREATE INDEX ix_ca_checklist_client ON ca_checklist_item(client_id, area);

CREATE TABLE IF NOT EXISTS ca_working_paper (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    ref            TEXT NOT NULL,
    title          TEXT,
    content        TEXT,
    prepared_by    INT REFERENCES app_user(id),
    prepared_at    TEXT,
    reviewed_by    INT REFERENCES app_user(id),
    reviewed_at    TEXT,
    review_status  VARCHAR(100) NOT NULL DEFAULT 'open',     -- open | approved | rejected | sent_back
    review_comment TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ca_wp_client ON ca_working_paper(client_id);

-- ---------------------------------------------------------- 14. BUSINESS ADVISORY
-- Fourteen named tools reduce to six tables. Product-wise, Customer-wise and
-- Branch-wise Profitability Tools are the SAME register (ba_profitability_line)
-- read three different ways by its `dimension_type` column; Profitability
-- Analyzer and Cost Structure Analyzer are both just aggregations over that
-- same register (margin by dimension vs. direct-cost/overhead split of the
-- same rows), not separate data models. Budget Preparation Automator and
-- Budget vs Actual Analyzer are one register (ba_budget_line) — a budget
-- line with no actual yet IS the budget; add the actual and the variance
-- becomes visible, never a second table. KPI Dashboard Automator and
-- Management Dashboard Generator are the same underlying KPI time series
-- (ba_kpi + ba_kpi_value) — "management dashboard" is just that KPI view
-- combined with the profitability and budget-variance summaries already
-- above, computed live, not stored again. Business Feasibility Study and
-- Financial Projection Generator are NOT rebuilt here — they are the
-- existing `projection` module, cross-linked from this module's UI. Business
-- Model Analyzer is a one-row-per-client canvas (ba_business_model); Business
-- Plan Generator assembles a plan from a small set of named sections
-- (ba_business_plan_section) plus a live pull of the client's latest
-- projection/profitability figures at report time — never duplicated data.
CREATE TABLE IF NOT EXISTS ba_profitability_line (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    client_id         INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    period_label      VARCHAR(100) NOT NULL,
    dimension_type    VARCHAR(100) NOT NULL,     -- product | customer | branch | overall
    dimension_name    TEXT NOT NULL,
    revenue           DOUBLE NOT NULL DEFAULT 0,
    direct_cost       DOUBLE NOT NULL DEFAULT 0,
    allocated_overhead DOUBLE NOT NULL DEFAULT 0,
    notes             TEXT,
    created_by        INT REFERENCES app_user(id),
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ba_profit_client ON ba_profitability_line(client_id, dimension_type, period_label);

CREATE TABLE IF NOT EXISTS ba_pricing_scenario (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    client_id          INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    product_name       TEXT NOT NULL,
    unit_cost          DOUBLE NOT NULL DEFAULT 0,
    target_margin_pct  DOUBLE NOT NULL DEFAULT 0,
    competitor_price   DOUBLE,
    current_price      DOUBLE,
    notes              TEXT,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ba_pricing_client ON ba_pricing_scenario(client_id);

CREATE TABLE IF NOT EXISTS ba_budget_line (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    client_id        INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fiscal_year_label VARCHAR(100) NOT NULL,
    period_label     TEXT,              -- e.g. 'Shrawan 2082', or blank for a whole-year line
    line_type        VARCHAR(100) NOT NULL DEFAULT 'expense',  -- revenue | expense
    category         TEXT NOT NULL,
    budgeted_amount  DOUBLE NOT NULL DEFAULT 0,
    actual_amount    DOUBLE,
    notes            TEXT,
    created_by       INT REFERENCES app_user(id),
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ba_budget_client ON ba_budget_line(client_id, fiscal_year_label);

CREATE TABLE IF NOT EXISTS ba_kpi (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    name          TEXT NOT NULL,
    category      TEXT,                 -- Financial | Operational | Customer | Staff | Other
    unit          TEXT,                 -- '%', 'NPR', 'days', 'count'...
    target_value  DOUBLE,
    higher_is_better INT NOT NULL DEFAULT 1,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ba_kpi_client ON ba_kpi(client_id);

CREATE TABLE IF NOT EXISTS ba_kpi_value (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    kpi_id        INT NOT NULL REFERENCES ba_kpi(id) ON DELETE CASCADE,
    period_label  VARCHAR(100) NOT NULL,
    actual_value  DOUBLE NOT NULL DEFAULT 0,
    UNIQUE (kpi_id, period_label)
);
CREATE INDEX ix_ba_kpiv_kpi ON ba_kpi_value(kpi_id, period_label);

CREATE TABLE IF NOT EXISTS ba_business_model (
    client_id             INT PRIMARY KEY REFERENCES client(id) ON DELETE CASCADE,
    value_proposition      TEXT,
    customer_segments      TEXT,
    channels                TEXT,
    customer_relationships  TEXT,
    revenue_streams         TEXT,
    key_resources           TEXT,
    key_activities          TEXT,
    key_partners            TEXT,
    cost_structure_notes    TEXT,
    updated_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ba_business_plan_section (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    section_key   VARCHAR(100) NOT NULL,   -- executive_summary | company_description | market_analysis |
                                    -- business_model | marketing_sales | operations | management_team |
                                    -- financial_plan | risk_analysis | appendix
    content       TEXT,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (client_id, section_key)
);

CREATE TABLE IF NOT EXISTS ba_working_paper (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    ref            TEXT NOT NULL,
    title          TEXT,
    content        TEXT,
    prepared_by    INT REFERENCES app_user(id),
    prepared_at    TEXT,
    reviewed_by    INT REFERENCES app_user(id),
    reviewed_at    TEXT,
    review_status  VARCHAR(100) NOT NULL DEFAULT 'open',
    review_comment TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ba_wp_client ON ba_working_paper(client_id);

-- ---------------------------------------------------- 15. IPO / CAPITAL MARKET
-- Twelve named tools reduce to seven tables plus two cross-links to modules
-- that already exist. IPO Readiness Assessment, IPO Eligibility Checker and
-- SEBON Compliance Checklist are ONE checklist register (ipo_checklist_item)
-- distinguished by `area` — eligibility rows additionally carry a
-- threshold_value/actual_value pair, everything else just a status. The
-- specific SEBON numeric thresholds (minimum paid-up capital, profitable
-- years of operation, promoter lock-in, etc.) are never hard-coded here —
-- threshold_value is seeded NULL and left for the firm to enter after
-- confirming the currently-effective SEBON regulation, the exact discipline
-- rate_master already uses for every other statutory figure in this app.
-- IPO Financial Due Diligence Tool is NOT rebuilt — it is the existing `dd`
-- module (which already lists "IPO readiness" as a transaction type),
-- cross-linked from this module's UI. IPO Financial Projection Model is the
-- existing `projection` module; IPO Valuation Model is the existing
-- `valuation` module (already produces a "Share Valuation Report" when the
-- subject type is 'share') — both cross-linked, not duplicated. IPO Pricing
-- Analyzer reads its own ipo_pricing_scenario rows AND a valuation-per-share
-- figure the preparer carries over from the Valuation module by hand (no
-- silent cross-module read — the preparer confirms the figure they're using).
CREATE TABLE IF NOT EXISTS ipo_entity_profile (
    client_id              INT PRIMARY KEY REFERENCES client(id) ON DELETE CASCADE,
    target_listing_date    TEXT,
    face_value             DOUBLE DEFAULT 100,
    proposed_issue_size    DOUBLE,
    proposed_price_per_share DOUBLE,
    pricing_method         TEXT,          -- fixed_price | book_building
    lead_manager           TEXT,
    notes                  TEXT,
    updated_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ipo_checklist_item (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    area           VARCHAR(100) NOT NULL,        -- Readiness | Eligibility | SEBON Compliance
    criterion      TEXT NOT NULL,
    status         VARCHAR(100) NOT NULL DEFAULT 'pending',   -- pending | done | na | not_met
    threshold_value TEXT,                 -- eligibility rows only — the firm's own confirmed figure, never assumed
    actual_value    TEXT,
    evidence        TEXT,
    remark          TEXT,
    created_by      INT REFERENCES app_user(id),
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TEXT
);
CREATE INDEX ix_ipo_checklist_client ON ipo_checklist_item(client_id, area);

CREATE TABLE IF NOT EXISTS ipo_restatement_line (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    client_id           INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label            VARCHAR(100) NOT NULL,
    account             TEXT NOT NULL,
    previously_reported DOUBLE NOT NULL DEFAULT 0,
    restated            DOUBLE NOT NULL DEFAULT 0,
    reason              TEXT,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ipo_restate_client ON ipo_restatement_line(client_id, fy_label);

CREATE TABLE IF NOT EXISTS ipo_working_capital_projection (
    id                        INT AUTO_INCREMENT PRIMARY KEY,
    client_id                 INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    period_label              TEXT NOT NULL,
    projected_requirement     DOUBLE NOT NULL DEFAULT 0,
    available_from_operations DOUBLE NOT NULL DEFAULT 0,
    ipo_proceeds_allocated    DOUBLE NOT NULL DEFAULT 0,
    notes                     TEXT,
    created_at                DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ipo_wc_client ON ipo_working_capital_projection(client_id);

CREATE TABLE IF NOT EXISTS ipo_peer (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    peer_name     TEXT NOT NULL,
    listed        INT NOT NULL DEFAULT 1,
    market_price  DOUBLE,
    eps           DOUBLE,
    book_value_per_share DOUBLE,
    roe_pct       DOUBLE,
    notes         TEXT,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ipo_peer_client ON ipo_peer(client_id);

CREATE TABLE IF NOT EXISTS ipo_risk_factor (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    client_id    INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    category     TEXT,          -- Business | Financial | Regulatory | Industry | Management | Other
    description  TEXT NOT NULL,
    mitigation   TEXT,
    severity     VARCHAR(100) NOT NULL DEFAULT 'Medium',   -- Low | Medium | High
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ipo_risk_client ON ipo_risk_factor(client_id);

CREATE TABLE IF NOT EXISTS ipo_pricing_scenario (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    client_id             INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    label                 TEXT NOT NULL,
    face_value            DOUBLE NOT NULL DEFAULT 100,
    proposed_price        DOUBLE NOT NULL DEFAULT 0,
    valuation_per_share    DOUBLE,          -- carried over by hand from the Valuation module's concluded value
    subject_eps           DOUBLE,
    notes                 TEXT,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ipo_pricing_client ON ipo_pricing_scenario(client_id);

CREATE TABLE IF NOT EXISTS ipo_working_paper (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    ref            TEXT NOT NULL,
    title          TEXT,
    content        TEXT,
    prepared_by    INT REFERENCES app_user(id),
    prepared_at    TEXT,
    reviewed_by    INT REFERENCES app_user(id),
    reviewed_at    TEXT,
    review_status  VARCHAR(100) NOT NULL DEFAULT 'open',
    review_comment TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_ipo_wp_client ON ipo_working_paper(client_id);

-- --------------------------------------------------------------- 16. TAX ADVISORY
-- Twenty-five named tools; twelve are already the existing biz_tax,
-- personal_tax, tds, salary_tds, vat, depreciation, and the rate_master +
-- deadline/obligation_type + transfer_pricing modules — not re-derived here
-- (see the module's own docstring/comment for exactly which tool maps to
-- which). The remaining thirteen reduce to eight tables. TDS Reconciliation,
-- VAT Reconciliation, Purchase VAT Eligibility, Sales VAT Reconciliation and
-- Input VAT Matching are FIVE named tools over just two registers
-- (tax_vat_sales_line, tax_vat_purchase_line) plus the existing TDS module's
-- own deduction figures compared against tax_tds_reconciliation_line —
-- "reconciliation" and "matching" and "eligibility" are three different
-- READS of the same rows (declared-in-return flag, matched-with-supplier
-- flag, eligible flag), not three different data models. Tax Audit Report
-- Data Generator, Tax Notice Response Assistant, Tax Assessment Working
-- Paper Generator and Tax Litigation Issue Analyzer are the tax-dispute
-- lifecycle (tax_case + tax_case_issue + tax_working_paper) — a notice, a
-- response, an assessment and an appeal are stages of ONE case, not four
-- tools. Minimum Tax Calculator and the "book profit to taxable income"
-- summary that Tax Audit Report Data Generator needs both live on
-- tax_computation_summary — a carry-over record the preparer confirms from
-- their own biz_tax/personal_tax workfile (the same "carried over by hand,
-- never read silently across modules" discipline already used for IPO
-- pricing's valuation-per-share). No minimum-tax percentage or PE-duration
-- threshold is ever hard-coded — every such figure here is the firm's own
-- confirmed input.
CREATE TABLE IF NOT EXISTS tax_vat_sales_line (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    client_id         INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    period_label      VARCHAR(100) NOT NULL,
    invoice_no        TEXT,
    party             TEXT,
    taxable_sales     DOUBLE NOT NULL DEFAULT 0,
    output_vat        DOUBLE NOT NULL DEFAULT 0,
    declared_in_return INT NOT NULL DEFAULT 0,
    notes             TEXT,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_vsales_client ON tax_vat_sales_line(client_id, period_label);

CREATE TABLE IF NOT EXISTS tax_vat_purchase_line (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    client_id             INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    period_label          VARCHAR(100) NOT NULL,
    invoice_no            TEXT,
    party                 TEXT,
    taxable_purchase      DOUBLE NOT NULL DEFAULT 0,
    input_vat             DOUBLE NOT NULL DEFAULT 0,
    eligible              INT NOT NULL DEFAULT 1,
    ineligibility_reason  TEXT,
    matched_with_supplier INT NOT NULL DEFAULT 0,
    notes                 TEXT,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_vpurch_client ON tax_vat_purchase_line(client_id, period_label);

CREATE TABLE IF NOT EXISTS tax_tds_reconciliation_line (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    client_id         INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label          VARCHAR(100) NOT NULL,
    section           TEXT,
    payee             TEXT,
    base_amount       DOUBLE NOT NULL DEFAULT 0,
    tds_deducted      DOUBLE NOT NULL DEFAULT 0,
    tds_deposited     DOUBLE NOT NULL DEFAULT 0,
    return_filed      INT NOT NULL DEFAULT 0,
    notes             TEXT,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_tdsrec_client ON tax_tds_reconciliation_line(client_id, fy_label);

CREATE TABLE IF NOT EXISTS tax_provision_line (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label      VARCHAR(100) NOT NULL,
    particular    TEXT NOT NULL,
    book_value    DOUBLE NOT NULL DEFAULT 0,
    tax_value     DOUBLE NOT NULL DEFAULT 0,
    notes         TEXT,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_prov_client ON tax_provision_line(client_id, fy_label);

CREATE TABLE IF NOT EXISTS tax_computation_summary (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    client_id             INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label              VARCHAR(100) NOT NULL,
    entity_type           TEXT,
    book_profit           DOUBLE NOT NULL DEFAULT 0,
    total_adjustments     DOUBLE NOT NULL DEFAULT 0,
    taxable_income        DOUBLE NOT NULL DEFAULT 0,
    tax_rate_pct          DOUBLE NOT NULL DEFAULT 0,
    tax_liability         DOUBLE NOT NULL DEFAULT 0,
    turnover              DOUBLE,
    minimum_tax_pct       DOUBLE,          -- firm's own confirmed figure, never assumed
    notes                 TEXT,
    updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (client_id, fy_label)
);

CREATE TABLE IF NOT EXISTS tax_deductibility_check (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label      VARCHAR(100) NOT NULL,
    description   TEXT NOT NULL,
    amount        DOUBLE NOT NULL DEFAULT 0,
    category      TEXT,          -- Domestic/personal | Not wholly for business | Fine or penalty | General provision |
                                  -- Capital nature | Entertainment/gift | Related-party not arm's length | Other
    status        VARCHAR(100) NOT NULL DEFAULT 'under_review',   -- deductible | not_deductible | under_review
    reason        TEXT,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_deduct_client ON tax_deductibility_check(client_id, fy_label);

CREATE TABLE IF NOT EXISTS tax_pe_risk_factor (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    factor        TEXT NOT NULL,
    present       INT NOT NULL DEFAULT 0,
    details       TEXT,
    risk_level    VARCHAR(100) NOT NULL DEFAULT 'Low',   -- Low | Medium | High
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_pe_client ON tax_pe_risk_factor(client_id);

CREATE TABLE IF NOT EXISTS tax_case (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    client_id        INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    case_type        VARCHAR(100) NOT NULL DEFAULT 'notice',   -- notice | assessment | appeal
    reference_no     TEXT,
    authority        TEXT,           -- e.g. Inland Revenue Office, Administrative Review, Revenue Tribunal, Supreme Court
    fy_label         TEXT,
    subject          TEXT,
    amount_demanded  DOUBLE,
    stage            VARCHAR(100) NOT NULL DEFAULT 'notice_received',
                     -- notice_received | response_filed | assessment_order | administrative_review |
                     -- revenue_tribunal | supreme_court | closed
    status           VARCHAR(100) NOT NULL DEFAULT 'open',   -- open | closed
    due_date         TEXT,
    response_text    TEXT,
    notes            TEXT,
    created_by       INT REFERENCES app_user(id),
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_case_client ON tax_case(client_id, status);

CREATE TABLE IF NOT EXISTS tax_case_issue (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    case_id               INT NOT NULL REFERENCES tax_case(id) ON DELETE CASCADE,
    client_id             INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    issue_description     TEXT NOT NULL,
    our_position          TEXT,
    department_position   TEXT,
    amount_involved       DOUBLE,
    likelihood            VARCHAR(100) NOT NULL DEFAULT 'uncertain',   -- favorable | unfavorable | uncertain
    status                VARCHAR(100) NOT NULL DEFAULT 'open',        -- open | resolved
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_issue_case ON tax_case_issue(case_id);

CREATE TABLE IF NOT EXISTS tax_working_paper (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    case_id        INT REFERENCES tax_case(id) ON DELETE SET NULL,
    ref            TEXT NOT NULL,
    title          TEXT,
    content        TEXT,
    prepared_by    INT REFERENCES app_user(id),
    prepared_at    TEXT,
    reviewed_by    INT REFERENCES app_user(id),
    reviewed_at    TEXT,
    review_status  VARCHAR(100) NOT NULL DEFAULT 'open',
    review_comment TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_tax_wp_client ON tax_working_paper(client_id);

-- ------------------------------------------------------------ 17. AUDIT EXECUTION
-- Thirty-five named tools; thirteen are already the existing stat_audit
-- (materiality, sample SIZE, inherent/control risk = RMM) and internal_audit
-- (risk-based planning, RACM = the control questionnaire + testing,
-- walkthroughs, findings) modules, plus forensic_audit and transfer_pricing
-- for journal-entry testing and related-party detection — not re-derived
-- here. The remaining twenty-two reduce to six tables. Vouching, Invoice
-- Verification, Bank Reconciliation, Bank/Debtor/Creditor Confirmation
-- Trackers, Inventory/Fixed Asset/CWIP Verification, Cut-off Testing and
-- Revenue/Expense/Payroll Testing are THIRTEEN named tools over ONE register
-- (aud_test_item) — every one of them is "pick an item, compare book to
-- externally-verified support, note the variance and conclusion"; only the
-- `area` and `test_type` differ, and confirmation trackers are simply rows
-- with confirmation_status populated. Subsequent Events, NFRS Compliance,
-- Financial Statement Disclosure and Audit Completion Checklists are FOUR
-- named tools over one checklist register (aud_checklist_item, area-scoped),
-- the same consolidation already used for corporate_advisory/ipo_advisory's
-- checklists. Audit Sampling Selection is systematic sampling only
-- (deterministic — interval and start point — so it can be verified
-- identically in Python and JS); true random/haphazard/judgmental selection
-- records the METHOD and the resulting item list the preparer actually
-- chose, never a fabricated random-number generator pretending to be one.
CREATE TABLE IF NOT EXISTS aud_sample_selection (
    id                     INT AUTO_INCREMENT PRIMARY KEY,
    client_id              INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label               VARCHAR(100) NOT NULL,
    area                   TEXT NOT NULL,
    population_description TEXT,
    population_size        INT NOT NULL DEFAULT 0,
    population_value       DOUBLE,
    method                 VARCHAR(100) NOT NULL DEFAULT 'systematic',
                           -- systematic | random | haphazard | monetary_unit | judgmental
    sample_size            INT NOT NULL DEFAULT 0,
    start_point             INT,        -- systematic method only
    selected_items_note     TEXT,            -- the item list actually selected — a plain description, not a fake RNG
    notes                   TEXT,
    created_by              INT REFERENCES app_user(id),
    created_at               DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_aud_sample_client ON aud_sample_selection(client_id, fy_label);

CREATE TABLE IF NOT EXISTS aud_test_item (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    client_id             INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label              VARCHAR(100) NOT NULL,
    area                  VARCHAR(100) NOT NULL,
                          -- Bank | Debtors | Creditors | Inventory | FixedAssets | CWIP | Revenue | Expense |
                          -- Payroll | Vouching | Invoice | CutOff | Other
    test_type             VARCHAR(100) NOT NULL DEFAULT 'vouching',
                          -- vouching | confirmation | reconciliation | physical_verification | cutoff | analytical | other
    sample_id             INT REFERENCES aud_sample_selection(id) ON DELETE SET NULL,
    item_ref              TEXT,
    description            TEXT,
    book_amount            DOUBLE NOT NULL DEFAULT 0,
    tested_amount          DOUBLE,
    confirmation_status    TEXT,   -- not_sent | sent | received_agreed | received_discrepancy | n/a
    status                 VARCHAR(100) NOT NULL DEFAULT 'open',   -- open | matched | exception
    detail_json            TEXT,   -- e.g. bank reconciliation's reconciling-items breakdown
    remark                  TEXT,
    created_by              INT REFERENCES app_user(id),
    created_at               DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_aud_testitem_client ON aud_test_item(client_id, fy_label, area);

CREATE TABLE IF NOT EXISTS aud_analytical_procedure (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    client_id             INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label              VARCHAR(100) NOT NULL,
    metric                TEXT NOT NULL,
    prior_period_value    DOUBLE,
    current_period_value  DOUBLE,
    expected_value        DOUBLE,
    threshold_pct         DOUBLE NOT NULL DEFAULT 10,
    explanation           TEXT,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_aud_analytical_client ON aud_analytical_procedure(client_id, fy_label);

CREATE TABLE IF NOT EXISTS aud_going_concern_indicator (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label      VARCHAR(100) NOT NULL,
    category      TEXT,        -- Financial | Operating | Other
    indicator     TEXT NOT NULL,
    present       INT NOT NULL DEFAULT 0,
    details       TEXT,
    mitigating_factor TEXT,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_aud_gc_client ON aud_going_concern_indicator(client_id, fy_label);

CREATE TABLE IF NOT EXISTS aud_going_concern_assessment (
    id                        INT AUTO_INCREMENT PRIMARY KEY,
    client_id                 INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label                  VARCHAR(100) NOT NULL,
    conclusion                TEXT,
    material_uncertainty_exists INT NOT NULL DEFAULT 0,
    notes                     TEXT,
    updated_at                DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (client_id, fy_label)
);

CREATE TABLE IF NOT EXISTS aud_checklist_item (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label      VARCHAR(100) NOT NULL,
    area          VARCHAR(100) NOT NULL,   -- Subsequent Events | NFRS Compliance | FS Disclosure | Completion
    criterion     TEXT NOT NULL,
    status        VARCHAR(100) NOT NULL DEFAULT 'pending',   -- pending | done | na
    evidence      TEXT,
    remark        TEXT,
    created_by    INT REFERENCES app_user(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TEXT
);
CREATE INDEX ix_aud_checklist_client ON aud_checklist_item(client_id, fy_label, area);

CREATE TABLE IF NOT EXISTS aud_working_paper (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label       TEXT,
    ref            TEXT NOT NULL,
    title          TEXT,
    content        TEXT,
    prepared_by    INT REFERENCES app_user(id),
    prepared_at    TEXT,
    reviewed_by    INT REFERENCES app_user(id),
    reviewed_at    TEXT,
    review_status  VARCHAR(100) NOT NULL DEFAULT 'open',
    review_comment TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_aud_wp_client ON aud_working_paper(client_id);

-- ------------------------------------------------------------ 18. ACCOUNTING AUTOMATION
-- Covers the "Accounting & Financial Statement Automators" list not already
-- covered by engines.fin_statements (Automated Financial Statement Generator,
-- Notes to Accounts Generator, TB-to-Financial-Statement Mapping Engine — all
-- already built), engines.fixed_assets (Depreciation Schedule Automator),
-- engines.lease_schedule (Lease Accounting Automator, lessee side), or
-- engines.loan_schedule (period interest/cost-of-borrowing on a loan itself).
-- Trial Balance Analyzer / Suspense Account Detector / Unusual Balance Detector /
-- Debit-Credit Classification Checker are ONE stateless tb_diagnostics() call in
-- acct_engines.py (no table — the preparer pastes the same TB row shape used in
-- Financial Statements, nothing is silently read from that module).
CREATE TABLE IF NOT EXISTS acct_chart_of_accounts (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    code        TEXT,
    name        TEXT NOT NULL,
    fs_head     TEXT,          -- maps to engines.HEAD id, kept as free text here (no FK — HEAD is a code constant)
    normal_side VARCHAR(100) NOT NULL DEFAULT 'debit',   -- debit | credit
    parent_code TEXT,
    is_active   INT NOT NULL DEFAULT 1,
    notes       TEXT,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_coa_client ON acct_chart_of_accounts(client_id);

CREATE TABLE IF NOT EXISTS acct_cash_flow_setting (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    client_id           INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label            VARCHAR(100) NOT NULL,
    method              VARCHAR(100) NOT NULL DEFAULT 'indirect',
    opening_cash        DOUBLE NOT NULL DEFAULT 0,
    closing_cash_actual DOUBLE,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (client_id, fy_label)
);

CREATE TABLE IF NOT EXISTS acct_cash_flow_line (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    client_id  INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label   VARCHAR(100) NOT NULL,
    section    VARCHAR(100) NOT NULL DEFAULT 'operating_adjustment',
              -- operating_pat_start | operating_adjustment | working_capital | operating_other | investing | financing
    label      TEXT NOT NULL,
    amount     DOUBLE NOT NULL DEFAULT 0,   -- signed: +inflow/add-back, -outflow/deduction
    note       TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_cfline_client ON acct_cash_flow_line(client_id, fy_label);

CREATE TABLE IF NOT EXISTS acct_equity_component (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    client_id        INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label         VARCHAR(100) NOT NULL,
    name             TEXT NOT NULL,   -- Share capital | Share premium | General reserve | Retained earnings | ...
    opening_balance  DOUBLE NOT NULL DEFAULT 0,
    profit_for_year  DOUBLE NOT NULL DEFAULT 0,
    oci              DOUBLE NOT NULL DEFAULT 0,
    dividend         DOUBLE NOT NULL DEFAULT 0,
    share_issued     DOUBLE NOT NULL DEFAULT 0,
    share_buyback    DOUBLE NOT NULL DEFAULT 0,
    transfer_in      DOUBLE NOT NULL DEFAULT 0,
    transfer_out     DOUBLE NOT NULL DEFAULT 0,
    other_movement   DOUBLE NOT NULL DEFAULT 0,
    note             TEXT,
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_equity_client ON acct_equity_component(client_id, fy_label);

CREATE TABLE IF NOT EXISTS acct_provision (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    client_id       INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label        VARCHAR(100) NOT NULL,
    category        VARCHAR(100) NOT NULL DEFAULT 'provision',  -- accrual | provision
    item            TEXT NOT NULL,
    basis           TEXT,
    opening_balance DOUBLE NOT NULL DEFAULT 0,
    charge_for_year DOUBLE NOT NULL DEFAULT 0,
    utilized        DOUBLE NOT NULL DEFAULT 0,
    reversed        DOUBLE NOT NULL DEFAULT 0,
    note            TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_provision_client ON acct_provision(client_id, fy_label);

CREATE TABLE IF NOT EXISTS acct_prepayment (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label    VARCHAR(100) NOT NULL,
    item        TEXT NOT NULL,
    total_amount DOUBLE NOT NULL DEFAULT 0,
    start_date  TEXT NOT NULL,
    end_date    TEXT NOT NULL,
    note        TEXT,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_prepay_client ON acct_prepayment(client_id, fy_label);

CREATE TABLE IF NOT EXISTS acct_fx_item (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label       VARCHAR(100) NOT NULL,
    item           TEXT NOT NULL,
    currency       VARCHAR(100) NOT NULL DEFAULT 'USD',
    foreign_amount DOUBLE NOT NULL DEFAULT 0,
    booked_rate    DOUBLE NOT NULL DEFAULT 0,
    closing_rate   DOUBLE NOT NULL DEFAULT 0,
    classification VARCHAR(100) NOT NULL DEFAULT 'asset',  -- asset | liability
    note           TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_fx_client ON acct_fx_item(client_id, fy_label);

CREATE TABLE IF NOT EXISTS acct_interest_accrual (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    client_id       INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label        VARCHAR(100) NOT NULL,
    item            TEXT NOT NULL,
    principal       DOUBLE NOT NULL DEFAULT 0,
    rate_pct        DOUBLE NOT NULL DEFAULT 0,
    from_date       TEXT NOT NULL,
    to_date         TEXT NOT NULL,
    day_count_basis VARCHAR(100) NOT NULL DEFAULT '365',   -- 365 | 360
    compounding     VARCHAR(100) NOT NULL DEFAULT 'simple', -- simple | annual
    note            TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_interest_client ON acct_interest_accrual(client_id, fy_label);

CREATE TABLE IF NOT EXISTS acct_borrowing_capitalization (
    id                            INT AUTO_INCREMENT PRIMARY KEY,
    client_id                     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label                      VARCHAR(100) NOT NULL,
    qualifying_asset              TEXT NOT NULL,
    period_days                   INT NOT NULL DEFAULT 365,
    specific_borrowings_json      VARCHAR(100) NOT NULL DEFAULT '[]',   -- [{principal, rate_pct}]
    general_borrowings_json       VARCHAR(100) NOT NULL DEFAULT '[]',   -- [{principal, rate_pct}]
    capitalizable_expenditure_general DOUBLE NOT NULL DEFAULT 0,
    income_from_temp_investment   DOUBLE NOT NULL DEFAULT 0,
    note                          TEXT,
    created_at                    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_borrowcap_client ON acct_borrowing_capitalization(client_id, fy_label);

CREATE TABLE IF NOT EXISTS acct_closing_checklist_item (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label    VARCHAR(100) NOT NULL,
    area        VARCHAR(100) NOT NULL,   -- Closing
    criterion   TEXT NOT NULL,
    status      VARCHAR(100) NOT NULL DEFAULT 'pending',   -- pending | done | na
    evidence    TEXT,
    remark      TEXT,
    created_by  INT REFERENCES app_user(id),
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TEXT
);
CREATE INDEX ix_acct_closing_client ON acct_closing_checklist_item(client_id, fy_label, area);

CREATE TABLE IF NOT EXISTS acct_working_paper (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label       TEXT,
    ref            TEXT NOT NULL,
    title          TEXT,
    content        TEXT,
    prepared_by    INT REFERENCES app_user(id),
    prepared_at    TEXT,
    reviewed_by    INT REFERENCES app_user(id),
    reviewed_at    TEXT,
    review_status  VARCHAR(100) NOT NULL DEFAULT 'open',
    review_comment TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_acct_wp_client ON acct_working_paper(client_id);

-- ------------------------------------------------------- 19. MASTER DATA HUB
-- Phase 1 foundation only ("Integrated by Design, Independent by Operation").
-- Generalizes the one pull-based cross-module mechanism this app already had
-- (Financial Statements' "Pull from other modules" panel, GET
-- /api/clients/:id/linked-workfiles) into a real, queryable ledger instead of
-- fields embedded ad hoc inside one module's TB rows — same philosophy, wider
-- reach: always pull-based (never live-bound), the source is always
-- recomputed fresh before being offered, and staleness is detected by
-- comparing versions, never assumed. hub_entity_profile / hub_related_party /
-- recommendation tables are deliberately NOT included in this pass — they
-- require reconciling live data already owned by Corporate Advisory and
-- Transfer Pricing, which needs a design review before touching working
-- modules, not something to do unsupervised.
CREATE TABLE IF NOT EXISTS hub_snapshot (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    client_id          INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    fy_label           VARCHAR(100),
    kind               VARCHAR(100) NOT NULL,   -- trial_balance | financial_statements | fixed_assets | debt_schedule | tax_computation | valuation_assumptions | ... (open, not an enum)
    source_module      TEXT NOT NULL,
    source_workfile_id INT REFERENCES workfile(id) ON DELETE SET NULL,
    source_version     INT NOT NULL DEFAULT 1,
    payload            TEXT,            -- JSON — whatever figures the publishing module chose to share
    computed_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    computed_by        INT REFERENCES app_user(id)
);
CREATE INDEX ix_hub_snapshot_client ON hub_snapshot(client_id, kind, fy_label);

CREATE TABLE IF NOT EXISTS hub_link (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    client_id        INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    consuming_module VARCHAR(100) NOT NULL,
    consuming_ref    TEXT,    -- workfile_id or a relational row id, module-interpreted; text so either fits
    snapshot_id      INT NOT NULL REFERENCES hub_snapshot(id) ON DELETE CASCADE,
    version_at_link  INT NOT NULL,
    status           VARCHAR(100) NOT NULL DEFAULT 'synced',  -- synced | outdated | requires_review | overridden | finalized
    linked_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    linked_by        INT REFERENCES app_user(id)
);
CREATE INDEX ix_hub_link_client ON hub_link(client_id, consuming_module);
CREATE INDEX ix_hub_link_snapshot ON hub_link(snapshot_id);

-- --------------------------------------------------------- 20. PUBLIC WEBSITE
-- Phase 1 of the public-facing service site: services, static/legal pages
-- and testimonials are admin-managed content served to visitors who have no
-- session at all (GET /api/public/...), the same way appointment_slot
-- already is. Contact-form and service-enquiry submissions land in the
-- existing `lead` table (source/subject/service_id below) rather than a new
-- table, since `lead` was already built as the generic capture point for
-- "someone off the street told us something" — see web/landing.js's
-- existing "Request access" form and the appointment-booking widget for the
-- established pattern.

CREATE TABLE IF NOT EXISTS service_category (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    firm_id    INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    slug       VARCHAR(100) NOT NULL,
    name       TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    UNIQUE (firm_id, slug)
);

CREATE TABLE IF NOT EXISTS service (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    firm_id               INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    category_id           INT REFERENCES service_category(id) ON DELETE SET NULL,
    slug                  VARCHAR(100) NOT NULL,
    name                  TEXT NOT NULL,
    summary               TEXT,
    description_html      TEXT,
    price_type            VARCHAR(100) NOT NULL DEFAULT 'enquire',  -- fixed | starting_from | enquire
    price                 DOUBLE,
    currency              VARCHAR(100) NOT NULL DEFAULT 'NPR',
    duration_label        TEXT,
    is_buyable            INT NOT NULL DEFAULT 0,
    requires_appointment  INT NOT NULL DEFAULT 1,
    image_path            TEXT,
    meta_title            TEXT,
    meta_description      TEXT,
    is_published          INT NOT NULL DEFAULT 0,
    sort_order            INT NOT NULL DEFAULT 0,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TEXT,
    UNIQUE (firm_id, slug)
);
CREATE INDEX ix_service_firm ON service(firm_id, is_published);

-- Static/legal content (About, Privacy Policy, Terms, Refund Policy, Cookie
-- Policy, ...) — one row per slug, editable from Admin instead of hardcoded
-- into a template.
CREATE TABLE IF NOT EXISTS page (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    firm_id            INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    slug               VARCHAR(100) NOT NULL,
    title              TEXT NOT NULL,
    content_html       TEXT,
    meta_title         TEXT,
    meta_description   TEXT,
    is_published       INT NOT NULL DEFAULT 0,
    updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by         INT REFERENCES app_user(id),
    UNIQUE (firm_id, slug)
);

CREATE TABLE IF NOT EXISTS testimonial (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    firm_id       INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    client_name   TEXT NOT NULL,
    client_title  TEXT,
    company       TEXT,
    content       TEXT NOT NULL,
    rating        INT,
    is_published  INT NOT NULL DEFAULT 0,
    sort_order    INT NOT NULL DEFAULT 0,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- SMTP credentials deliberately live in their own table, never in `setting`
-- (which GET /api/bootstrap broadcasts wholesale to every logged-in browser
-- — see db.py's _CRED_KEY_PATH comment for the same reasoning already
-- applied to client_credential). password_enc uses db.encrypt_secret(), the
-- same at-rest scheme client_credential uses; only a super admin can read or
-- write this row, and the password is never echoed back in a GET response.
CREATE TABLE IF NOT EXISTS smtp_config (
    firm_id      INT PRIMARY KEY REFERENCES firm(id) ON DELETE CASCADE,
    host         TEXT,
    port         INT NOT NULL DEFAULT 587,
    username     TEXT,
    password_enc TEXT,
    from_name    TEXT,
    from_email   TEXT,
    use_tls      INT NOT NULL DEFAULT 1,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by   INT REFERENCES app_user(id)
);

-- Every outbound email attempt, whatever triggered it (invite, password
-- reset, deadline sweep, contact-form acknowledgement, ...) — so Admin can
-- see Sent/Failed and why, per the brief's email-logging requirement.
CREATE TABLE IF NOT EXISTS email_log (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    to_addr      TEXT NOT NULL,
    subject      TEXT NOT NULL,
    status       TEXT NOT NULL,   -- sent | failed | skipped (SMTP not configured)
    error        TEXT,
    related_type TEXT,            -- 'lead' | 'appointment_booking' | 'app_user' | ... (open, not an enum)
    related_id   INT,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_email_log_created ON email_log(created_at);

-- ------------------------------------------------------- 21. CAREERS / RECRUITMENT
-- Phase 2. A published job_posting is publicly visible; an application
-- moves through a fixed pipeline of stages, each transition logged in
-- application_stage_history and (via email_template, below) triggering an
-- admin-editable email to the applicant — see server/app.py's
-- _CAREER_STAGE_TEMPLATE map for which stage sends which template key.

CREATE TABLE IF NOT EXISTS job_posting (
    id                     INT AUTO_INCREMENT PRIMARY KEY,
    firm_id                INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    slug                   VARCHAR(100) NOT NULL,
    title                  TEXT NOT NULL,
    department             TEXT,
    location               TEXT,
    employment_type        VARCHAR(100) NOT NULL DEFAULT 'full_time',  -- full_time | part_time | contract | internship
    salary_range           TEXT,
    experience_required    TEXT,
    education_required     TEXT,
    skills                 TEXT,
    description_html       TEXT,
    responsibilities_html  TEXT,
    requirements_html      TEXT,
    deadline               TEXT,          -- 'YYYY-MM-DD', nullable = open until closed by hand
    is_published            INT NOT NULL DEFAULT 0,
    created_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TEXT,
    UNIQUE (firm_id, slug)
);
CREATE INDEX ix_job_posting_firm ON job_posting(firm_id, is_published);

CREATE TABLE IF NOT EXISTS job_application (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    job_id             INT NOT NULL REFERENCES job_posting(id) ON DELETE CASCADE,
    name               TEXT NOT NULL,
    email              TEXT NOT NULL,
    phone              TEXT,
    address            TEXT,
    cv_path            TEXT,
    cv_filename        TEXT,
    cover_letter       TEXT,
    education          TEXT,
    experience         TEXT,
    portfolio_url      TEXT,
    consent            INT NOT NULL DEFAULT 0,
    stage              VARCHAR(100) NOT NULL DEFAULT 'new',
    -- new | screening | under_review | shortlisted | interview | selected | rejected
    assigned_recruiter INT REFERENCES app_user(id),
    internal_notes     TEXT,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         TEXT
);
CREATE INDEX ix_job_application_job ON job_application(job_id, stage);

CREATE TABLE IF NOT EXISTS application_stage_history (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL REFERENCES job_application(id) ON DELETE CASCADE,
    from_stage     TEXT,
    to_stage       TEXT NOT NULL,
    changed_by     INT REFERENCES app_user(id),
    note           TEXT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_stage_history_app ON application_stage_history(application_id);

-- Admin-editable email bodies, keyed by a fixed set of well-known keys (see
-- _DEFAULT_EMAIL_TEMPLATES in db.py, seeded idempotently on every startup so
-- an upgrade never leaves a key missing). {{placeholder}} substitution only
-- — no code execution, no arbitrary Jinja — kept deliberately simple.
CREATE TABLE IF NOT EXISTS email_template (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    `key`        VARCHAR(100) NOT NULL UNIQUE,
    subject    TEXT NOT NULL,
    body       TEXT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by INT REFERENCES app_user(id)
);

-- ------------------------------------------------------------ 22. CLIENT PORTAL
-- Phase 3. A second, deliberately separate credential space from app_user —
-- a client contact is not a firm employee and must never end up with a
-- staff session by any code path, so this mirrors auth_session/user_invite/
-- password_reset as their own tables rather than reusing app_user's with a
-- "type" flag. One client can have more than one portal contact (director +
-- accountant, say); each row is independently invited and independently
-- revocable.
CREATE TABLE IF NOT EXISTS client_portal_user (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    client_id     INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    name          TEXT NOT NULL,
    email         VARCHAR(100) NOT NULL UNIQUE,
    pwd_hash      TEXT NOT NULL,
    pwd_salt      TEXT NOT NULL,
    is_active     INT NOT NULL DEFAULT 1,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at TEXT
);
CREATE INDEX ix_portal_user_client ON client_portal_user(client_id);

CREATE TABLE IF NOT EXISTS client_portal_session (
    token          VARCHAR(100) PRIMARY KEY,
    portal_user_id INT NOT NULL REFERENCES client_portal_user(id) ON DELETE CASCADE,
    issued_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at     TEXT NOT NULL,
    revoked_at     TEXT
);

CREATE TABLE IF NOT EXISTS client_portal_invite (
    token          VARCHAR(100) PRIMARY KEY,
    portal_user_id INT NOT NULL REFERENCES client_portal_user(id) ON DELETE CASCADE,
    expires_at     TEXT NOT NULL,
    used_at        TEXT
);

CREATE TABLE IF NOT EXISTS client_portal_password_reset (
    token          VARCHAR(100) PRIMARY KEY,
    portal_user_id INT NOT NULL REFERENCES client_portal_user(id) ON DELETE CASCADE,
    expires_at     TEXT NOT NULL,
    used_at        TEXT
);

-- ------------------------------------------------------------- 23. PAYMENTS
-- Phase 4. Gateway credentials are never typed into code — they live here,
-- blank until a super admin fills them in from Admin > Site settings >
-- Payment gateways, encrypted the same way smtp_config's password already
-- is (db.encrypt_secret — never the `setting` table, never echoed back by a
-- GET). Every field is nullable on purpose: the whole payments module is
-- inert (checkout/pay buttons say "not configured yet") until a gateway's
-- required fields are actually present — see server/payments.py.
CREATE TABLE IF NOT EXISTS payment_gateway_config (
    firm_id                  INT PRIMARY KEY REFERENCES firm(id) ON DELETE CASCADE,
    khalti_secret_key_enc    TEXT,             -- server-side key, POST Authorization: Key <...>
    khalti_public_key        TEXT,             -- not secret; used client-side by Khalti's own widget if ever added
    khalti_mode               VARCHAR(100) NOT NULL DEFAULT 'test',   -- test (dev.khalti.com) | live (khalti.com)
    connectips_merchant_id    TEXT,
    connectips_app_id         TEXT,
    connectips_app_name       TEXT,
    connectips_password_enc   TEXT,
    connectips_cert_path      TEXT,            -- uploaded .pfx, stored under CA_DATA_DIR, outside the web root
    connectips_cert_pass_enc  TEXT,
    connectips_mode           VARCHAR(100) NOT NULL DEFAULT 'test',   -- test (uat.connectips.com) | live (connectips.com)
    updated_at                DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                INT REFERENCES app_user(id)
);

-- One checkout on a buyable service, before payment succeeds or fails —
-- the "Order" the brief's Service -> Checkout -> Payment -> Order flow
-- needs, distinct from invoice (which is firm-issued billing, not a
-- website purchase). A guest can check out without a client_id; converting
-- that into a real client is a manual CRM step, same posture as `lead`.
CREATE TABLE IF NOT EXISTS service_order (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    firm_id      INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    service_id   INT NOT NULL REFERENCES service(id),
    client_id    INT REFERENCES client(id) ON DELETE SET NULL,
    name         TEXT NOT NULL,
    email        TEXT NOT NULL,
    phone        TEXT,
    amount       DOUBLE NOT NULL,
    currency     VARCHAR(100) NOT NULL DEFAULT 'NPR',
    status       VARCHAR(100) NOT NULL DEFAULT 'pending_payment',  -- pending_payment | paid | cancelled
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- One row per payment attempt, whichever gateway and whatever it's for.
-- reference_type/reference_id points at the invoice or service_order the
-- payment is settling; status is only ever changed by a server-side
-- verify call against the gateway (khalti lookup / connectips verify) —
-- never trusted from a redirect's query string alone.
CREATE TABLE IF NOT EXISTS payment_transaction (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    firm_id        INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    gateway        TEXT NOT NULL,             -- khalti | connectips
    reference_type VARCHAR(100) NOT NULL,             -- invoice | service_order
    reference_id   INT NOT NULL,
    client_id      INT REFERENCES client(id) ON DELETE SET NULL,
    amount         DOUBLE NOT NULL,
    currency       VARCHAR(100) NOT NULL DEFAULT 'NPR',
    status         VARCHAR(100) NOT NULL DEFAULT 'pending',  -- pending | success | failed | cancelled
    gateway_ref    TEXT,                      -- our own purchase_order_id / txn id, generated at initiate time
    gateway_txn_id TEXT,                      -- khalti pidx / connectips txn id, filled in once the gateway assigns one
    raw_response   TEXT,                      -- JSON — last known verify response, kept for support/audit
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TEXT
);
CREATE INDEX ix_payment_txn_ref ON payment_transaction(reference_type, reference_id);

-- ---------------------------------------------------------------- 24. ABOUT
-- The firm's story (milestones — the site's signature scroll-driven
-- timeline) and its people. Both admin-managed, both empty by default —
-- never seeded with placeholder milestones or invented staff, since this
-- content makes a real claim about the firm's actual history and who
-- actually works there.
CREATE TABLE IF NOT EXISTS about_milestone (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    firm_id      INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    year         TEXT NOT NULL,       -- free text, not strictly numeric (e.g. "2015", "2072 BS")
    title        TEXT NOT NULL,
    description  TEXT,
    image_path   TEXT,
    sort_order   INT NOT NULL DEFAULT 0,
    is_published INT NOT NULL DEFAULT 0,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_about_milestone_firm ON about_milestone(firm_id, is_published);

CREATE TABLE IF NOT EXISTS team_member (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    firm_id      INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    name         TEXT NOT NULL,
    designation  TEXT,
    bio          TEXT,
    expertise    TEXT,             -- free text, comma-separated
    photo_path   TEXT,
    linkedin_url TEXT,
    sort_order   INT NOT NULL DEFAULT 0,
    is_published INT NOT NULL DEFAULT 0,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_team_member_firm ON team_member(firm_id, is_published);

-- Per-service FAQ, shown on the dedicated /services/<slug> page — same
-- shape as blog_faq (question/answer/sort_order), kept as its own table
-- rather than reusing blog_faq's post_id column for a different entity.
CREATE TABLE IF NOT EXISTS service_faq (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL REFERENCES service(id) ON DELETE CASCADE,
    question   TEXT NOT NULL,
    answer     TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0
);
CREATE INDEX ix_service_faq_service ON service_faq(service_id);

-- The firm's YouTube channel — a title plus whatever URL an admin pastes
-- (watch/embed/shorts/youtu.be, all accepted — see app.py's
-- _extract_youtube_id). video_id is the normalized 11-character id, the
-- only thing actually needed to build an embed or a thumbnail URL, so
-- that's what's stored rather than the raw pasted link.
CREATE TABLE IF NOT EXISTS youtube_video (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    firm_id      INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    title        TEXT NOT NULL,
    video_id     TEXT NOT NULL,
    sort_order   INT NOT NULL DEFAULT 0,
    is_published INT NOT NULL DEFAULT 0,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_youtube_video_firm ON youtube_video(firm_id, is_published);

-- ------------------------------------------------------------ 25. SITE CHATBOT
-- The public, unauthenticated landing-page chatbot (server/site_chat.py) —
-- deliberately a separate module and separate tables from ca_assistant_message
-- (the internal, staff-only, client-data-grounded assistant): different trust
-- boundary (anonymous visitor vs authenticated staff), different knowledge
-- (public site content only, never rate_master or client records), different
-- purpose (guide + lead capture, not CA reasoning).
CREATE TABLE IF NOT EXISTS chatbot_conversation (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    firm_id          INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    session_token    VARCHAR(100) NOT NULL,             -- opaque, browser-generated (localStorage) — ties messages to one visitor's session, not an identity
    source           VARCHAR(100) NOT NULL DEFAULT 'landing_page',
    topic            TEXT,                      -- short auto-derived label, e.g. "Service enquiry — FDI"
    name             TEXT,
    email            TEXT,
    phone            TEXT,
    company          TEXT,
    status           VARCHAR(100) NOT NULL DEFAULT 'open',        -- open | needs_follow_up | converted | closed
    follow_up_status TEXT,
    assigned_to      INT REFERENCES app_user(id),
    converted_to     TEXT,                      -- client | appointment | enquiry | service_request
    converted_id     INT,
    internal_note    TEXT,
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_chatbot_conv_firm ON chatbot_conversation(firm_id, status);
CREATE INDEX ix_chatbot_conv_session ON chatbot_conversation(session_token);

CREATE TABLE IF NOT EXISTS chatbot_message (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT NOT NULL REFERENCES chatbot_conversation(id) ON DELETE CASCADE,
    role            TEXT NOT NULL,              -- user | assistant
    content         TEXT NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_chatbot_msg_conv ON chatbot_message(conversation_id);

-- Admin-managed AI provider keys (server/ai_provider.py) — powers both the
-- public chatbot and the internal CA Assistant. One row per provider per
-- firm; "provider" is free text on purpose (not an enum) so a new provider
-- never needs a schema migration, only one _call_<name>() function in
-- ai_provider.py — the same "generic table, code decides what's valid"
-- posture as `setting`. Exactly one row may have is_active = 1 at a time
-- (enforced in application code, same as every other "exactly one current"
-- flag in this app — e.g. fiscal_year.is_current). A DB row here wins over
-- GROQ_API_KEY/ANTHROPIC_API_KEY environment variables entirely, same
-- precedent as smtp_config vs CA_SMTP_* — never merged field-by-field.
CREATE TABLE IF NOT EXISTS ai_provider_key (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    firm_id      INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    provider     VARCHAR(100) NOT NULL,               -- 'groq' | 'anthropic' | any future _call_<name>() in ai_provider.py
    api_key_enc  TEXT NOT NULL,
    model        TEXT,                        -- optional override of that provider's default model
    is_active    INT NOT NULL DEFAULT 0,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by   INT REFERENCES app_user(id),
    UNIQUE (firm_id, provider)
);

-- ------------------------------------------------------------- 26. SITE BANNER
-- A single admin-managed popup notice (announcement, image, promo) for the
-- public landing page — one row per firm, same "one config, always upsert"
-- posture as smtp_config, since a firm only ever has one banner live at a
-- time. is_active is the on/off switch; display_seconds is the auto-dismiss
-- timer (0 = stays until the visitor closes it themselves).
CREATE TABLE IF NOT EXISTS site_banner (
    firm_id         INT PRIMARY KEY REFERENCES firm(id) ON DELETE CASCADE,
    title           TEXT,
    message         TEXT,
    image_path      TEXT,
    link_url        TEXT,
    link_label      TEXT,
    display_seconds INT NOT NULL DEFAULT 0,
    is_active       INT NOT NULL DEFAULT 0,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by      INT REFERENCES app_user(id)
);

-- ---------------------------------------------------------------- 27. DOWNLOADS
-- Public document library (brochures, forms, circulars, whatever a firm
-- wants visitors to find and download) — deliberately its own table and its
-- own storage directory, never MEDIA_DIR (which is image-only) and never
-- DOCUMENT_DIR (client documents, which must stay private — see the
-- security posture in db.py/app.py's document routes). Every row here is
-- meant to be publicly indexable, so slug/title/description exist for real
-- SEO, same discipline as blog_post.
CREATE TABLE IF NOT EXISTS download_file (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    firm_id           INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    slug              VARCHAR(100) NOT NULL,
    title             TEXT NOT NULL,
    description       TEXT,
    file_path         TEXT NOT NULL,     -- stored filename under DOWNLOADS_DIR, outside web root
    original_filename TEXT,
    mime_type         TEXT,
    file_size         INT,
    is_published      INT NOT NULL DEFAULT 0,
    download_count    INT NOT NULL DEFAULT 0,
    uploaded_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    uploaded_by       INT REFERENCES app_user(id),
    UNIQUE (firm_id, slug)
);
CREATE INDEX ix_download_file_pub ON download_file(firm_id, is_published, uploaded_at DESC);

-- ------------------------------------------------------------- 28. MEDIA GALLERY
-- Every image POST /api/admin/media has ever accepted, now tracked instead
-- of just dropped anonymously into MEDIA_DIR — this is what makes a
-- reusable gallery possible: upload an image once here, get back a stable
-- /media/<filename> URL, and paste that same URL into ANY image field
-- across the app (blog cover, team photo, milestone, banner, service
-- image, ...) instead of re-uploading the same file from scratch every
-- time a module needs it.
CREATE TABLE IF NOT EXISTS media_asset (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    firm_id           INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    filename          TEXT NOT NULL,      -- stored filename under MEDIA_DIR
    original_filename TEXT,
    mime_type         TEXT,
    file_size         INT,
    uploaded_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    uploaded_by       INT REFERENCES app_user(id)
);
CREATE INDEX ix_media_asset_firm ON media_asset(firm_id, uploaded_at DESC);

-- ------------------------------------------------------------- 29. SOCIAL LOGIN
-- Client-portal social sign-in (server/oauth_login.py) — deliberately
-- scoped to the client portal only, never staff login: a firm's own team
-- keeps signing in with a password (and, in future, real 2FA), which is
-- the right posture for a practice-management system; a client's portal
-- account is the appropriate place for "Sign in with Google" convenience.
-- Same "blank until configured" posture as SMTP/payment gateways/AI
-- providers — every field here starts empty and the buttons stay hidden
-- until a real Client ID/Secret is saved. "provider" is free text (not an
-- enum) for the same reason as ai_provider_key: a new provider is one
-- config entry + one small block of code, never a migration.
CREATE TABLE IF NOT EXISTS oauth_provider_key (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    firm_id            INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    provider           VARCHAR(100) NOT NULL,          -- 'google' today; same extensibility as ai_provider_key
    client_id          TEXT NOT NULL,
    client_secret_enc  TEXT NOT NULL,
    is_active          INT NOT NULL DEFAULT 1,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by         INT REFERENCES app_user(id),
    UNIQUE (firm_id, provider)
);

-- Short-lived CSRF state for the OAuth redirect round trip — a visitor
-- leaves this app entirely (to Google's own sign-in page) and comes back
-- seconds to minutes later; the state value proves the callback really
-- answers a request this server issued, not a forged one. Swept for
-- expiry on every use rather than needing a cron job of its own.
CREATE TABLE IF NOT EXISTS oauth_state (
    state      VARCHAR(100) PRIMARY KEY,
    provider   TEXT NOT NULL,
    firm_id    INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ------------------------------------------------------------- 30. NAV MENU
-- Admin-managed site navigation — replaces what used to be hardcoded links
-- in index.html. "section" is the field each item belongs to (primary top
-- nav vs. the footer's link column); within a section, sort_order is the
-- clean, admin-controlled order. Seeded once with the site's existing
-- default links (see db.py's _seed_default_menu) so switching to this
-- table is purely additive — nothing on the live site changes until an
-- admin actually edits something here.
CREATE TABLE IF NOT EXISTS nav_menu_item (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    firm_id    INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    section    VARCHAR(100) NOT NULL,        -- 'primary' | 'footer'
    label      TEXT NOT NULL,
    url        TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_visible INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_nav_menu_section ON nav_menu_item(firm_id, section, sort_order);

-- ============================================================================
-- ORGANIZATION STRUCTURE, EMPLOYEES, WORK ENGINE, RESOURCE MANAGEMENT
-- Phase 1 (org/employees/hierarchy) + Phase 2 (contacts/task engine/workflow/
-- calendar) + Phase 3 (skills/capacity/resource allocation) additions.
--
-- Named org_branch/org_department/org_team/org_team_member (not branch/
-- department/team) specifically to avoid colliding with the EXISTING
-- team_member table, which is the public marketing site's staff-bio roster
-- (/team/<slug> pages) — a completely different concept from an internal
-- working team. job_position (not position) avoids MySQL's POSITION()
-- reserved word, the same reason 'lead' and 'key'/'system' are already
-- backtick-quoted elsewhere in this codebase.
-- ============================================================================

-- ---------------------------------------------------- ORGANIZATION STRUCTURE

CREATE TABLE IF NOT EXISTS org_branch (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    firm_id        INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    name           TEXT NOT NULL,
    address        TEXT,
    is_head_office INT NOT NULL DEFAULT 0,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_org_branch_firm ON org_branch(firm_id);

CREATE TABLE IF NOT EXISTS org_department (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    firm_id    INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    branch_id  INT REFERENCES org_branch(id) ON DELETE SET NULL,
    name       TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_org_department_firm ON org_department(firm_id);

CREATE TABLE IF NOT EXISTS org_team (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    firm_id       INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    department_id INT REFERENCES org_department(id) ON DELETE SET NULL,
    name          TEXT NOT NULL,
    lead_user_id  INT REFERENCES app_user(id) ON DELETE SET NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_org_team_firm ON org_team(firm_id);

CREATE TABLE IF NOT EXISTS org_team_member (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    team_id INT NOT NULL REFERENCES org_team(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    UNIQUE (team_id, user_id)
);

-- Configurable positions (Managing Partner -> ... -> Intern by default, a
-- firm can rename/add/remove any of them — see seed.sql). hierarchy_level orders
-- seniority (lower = more senior); used for reports_to sanity and, later,
-- approval-authority checks. Never hard-coded into engine logic itself.
CREATE TABLE IF NOT EXISTS job_position (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    firm_id         INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    name            VARCHAR(255) NOT NULL,
    hierarchy_level INT NOT NULL DEFAULT 0,
    is_builtin      INT NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (firm_id, name)
);

-- ------------------------------------------------------------------ EMPLOYEES
-- 1:1 with app_user rather than adding these columns to app_user directly —
-- app_user is small, load-bearing (auth) and touched by a lot of existing
-- code; keeping employee-specific fields in their own table means every
-- existing app_user query/row shape is completely unaffected.
CREATE TABLE IF NOT EXISTS employee_profile (
    id                     INT AUTO_INCREMENT PRIMARY KEY,
    user_id                INT NOT NULL UNIQUE REFERENCES app_user(id) ON DELETE CASCADE,
    employee_code          TEXT,
    job_position_id       INT REFERENCES job_position(id) ON DELETE SET NULL,
    department_id         INT REFERENCES org_department(id) ON DELETE SET NULL,
    branch_id             INT REFERENCES org_branch(id) ON DELETE SET NULL,
    reports_to_user_id    INT REFERENCES app_user(id) ON DELETE SET NULL,
    date_of_joining       TEXT,
    employment_type       VARCHAR(100) NOT NULL DEFAULT 'full_time',
    weekly_capacity_hours DOUBLE NOT NULL DEFAULT 40,
    cost_rate             DOUBLE,
    billing_rate          DOUBLE,
    phone                 TEXT,
    notes                 TEXT,
    created_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------------- CONTACTS
CREATE TABLE IF NOT EXISTS client_contact (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    designation TEXT,
    email       TEXT,
    phone       TEXT,
    is_primary  INT NOT NULL DEFAULT 0,
    notes       TEXT,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_client_contact_client ON client_contact(client_id);

-- ------------------------------------------------------------- TASK ENGINE
CREATE TABLE IF NOT EXISTS task_dependency (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    task_id            INT NOT NULL REFERENCES task(id) ON DELETE CASCADE,
    depends_on_task_id INT NOT NULL REFERENCES task(id) ON DELETE CASCADE,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (task_id, depends_on_task_id)
);

CREATE TABLE IF NOT EXISTS task_checklist_item (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    task_id    INT NOT NULL REFERENCES task(id) ON DELETE CASCADE,
    label      TEXT NOT NULL,
    is_done    INT NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_task_checklist_task ON task_checklist_item(task_id);

-- ----------------------------------------------------- ENGAGEMENT TEAM ROSTER
-- Additive alongside engagement.partner_id/manager_id (unchanged) — this is
-- the FULL team (seniors, associates, reviewers...), not a replacement for
-- those two single-person fields.
CREATE TABLE IF NOT EXISTS engagement_member (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    engagement_id      INT NOT NULL REFERENCES engagement(id) ON DELETE CASCADE,
    user_id            INT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    role_on_engagement VARCHAR(100) NOT NULL DEFAULT 'associate',
    allocated_hours    DOUBLE,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (engagement_id, user_id)
);
CREATE INDEX ix_engagement_member_eng ON engagement_member(engagement_id);
CREATE INDEX ix_engagement_member_user ON engagement_member(user_id);

-- -------------------------------------------------------------- CALENDAR
-- Internal team calendar — distinct from appointment_slot/appointment_booking
-- (public consultation booking for leads/prospects, unrelated concept).
CREATE TABLE IF NOT EXISTS calendar_event (
    id                   INT AUTO_INCREMENT PRIMARY KEY,
    firm_id              INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    user_id              INT REFERENCES app_user(id) ON DELETE CASCADE,
    title                TEXT NOT NULL,
    description          TEXT,
    start_at             VARCHAR(100) NOT NULL,
    end_at               TEXT,
    related_entity_type  TEXT,
    related_entity_id    INT,
    created_by           INT REFERENCES app_user(id) ON DELETE SET NULL,
    created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_calendar_event_firm ON calendar_event(firm_id);
CREATE INDEX ix_calendar_event_user ON calendar_event(user_id);
CREATE INDEX ix_calendar_event_start ON calendar_event(start_at);

-- -------------------------------------------------------------- WORKFLOW ENGINE
-- EVENT -> CONDITION -> ACTION, firm-configurable. Kept deliberately small:
-- a fixed set of trigger_event names the app itself fires (see
-- server/workflow_engine.py), simple equality conditions, and two action
-- types (create a task, notify someone) — enough to be genuinely useful and
-- genuinely wired to real triggers, rather than a speculative generic engine
-- with nothing calling it.
CREATE TABLE IF NOT EXISTS workflow_rule (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    firm_id        INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    name           TEXT NOT NULL,
    trigger_event  VARCHAR(100) NOT NULL,
    condition_json TEXT,
    action_type    TEXT NOT NULL,
    action_json    TEXT,  -- app.py defaults this to '{}' on insert -- MySQL can't DEFAULT a TEXT column
    is_active      INT NOT NULL DEFAULT 1,
    created_by     INT REFERENCES app_user(id) ON DELETE SET NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_workflow_rule_firm_event ON workflow_rule(firm_id, trigger_event);

-- ------------------------------------------------------------------- SKILLS
CREATE TABLE IF NOT EXISTS skill (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    firm_id INT NOT NULL REFERENCES firm(id) ON DELETE CASCADE,
    name    VARCHAR(255) NOT NULL,
    UNIQUE (firm_id, name)
);

CREATE TABLE IF NOT EXISTS employee_skill (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    skill_id    INT NOT NULL REFERENCES skill(id) ON DELETE CASCADE,
    proficiency INT NOT NULL DEFAULT 3,
    UNIQUE (user_id, skill_id)
);

-- ============================================================================
-- POSTGRESQL NOTES
--   * INT PRIMARY KEY            -> GENERATED ALWAYS AS IDENTITY
--   * datetime('now')                -> now() AT TIME ZONE 'utc'
--   * TEXT holding JSON              -> JSONB (payload, spec, params, applies_when)
--   * INT used as boolean        -> BOOLEAN
--   * drop the PRAGMA line
-- The application layer (db.py) speaks plain SQL only, so the port is mechanical.
-- ============================================================================

SET FOREIGN_KEY_CHECKS=1;
