-- ═══════════════════════════════════════════════════════════════════════════
-- V7 — contracts (NDA, consulting, MSA, SOW, service agreements)
--
-- New sibling crate `contract-cli` writes here. Shares issuers + clients
-- with invoice-cli. Adds legal-name / company-no / legal-jurisdiction to
-- clients so we have enough information to draft a contract party block.
-- ═══════════════════════════════════════════════════════════════════════════

-- Legal-party fields on clients. Optional — invoice-cli ignores them.
ALTER TABLE clients ADD COLUMN legal_name         TEXT;
ALTER TABLE clients ADD COLUMN company_no         TEXT;
ALTER TABLE clients ADD COLUMN legal_jurisdiction TEXT;

CREATE TABLE contracts (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    number              TEXT NOT NULL UNIQUE,
    kind                TEXT NOT NULL CHECK (kind IN ('consulting','nda','msa','sow','service')),
    issuer_id           INTEGER NOT NULL,
    client_id           INTEGER NOT NULL,
    title               TEXT NOT NULL,
    effective_date      TEXT NOT NULL,
    end_date            TEXT,
    term_months         INTEGER,
    governing_law       TEXT NOT NULL,
    venue               TEXT,
    status              TEXT NOT NULL DEFAULT 'draft'
        CHECK (status IN ('draft','sent','signed','active','expired','terminated')),
    sent_at             TEXT,
    signed_at           TEXT,
    terminated_at       TEXT,
    notes               TEXT,
    -- Money columns (optional, populated for fee-bearing contracts).
    fee_type            TEXT,    -- 'fixed' | 'hourly' | 'daily' | 'retainer'
    fee_amount_minor    INTEGER,
    fee_currency        TEXT,
    fee_schedule        TEXT,    -- 'on-completion' | 'monthly' | 'on-milestone' | 'upon-invoice'
    -- Kind-specific structured data, validated as JSON.
    terms_json          TEXT NOT NULL DEFAULT '{}' CHECK (json_valid(terms_json)),
    -- Selected clause pack + version, so renders are reproducible.
    clause_pack         TEXT NOT NULL,
    clause_pack_version TEXT NOT NULL,
    -- Optional template override (else config.default_contract_template, else built-in default).
    default_template    TEXT,
    -- Signature info — kept on the row for simplicity (one signer per side).
    signed_by_us_name   TEXT,
    signed_by_us_title  TEXT,
    signed_by_us_at     TEXT,
    signed_by_them_name TEXT,
    signed_by_them_title TEXT,
    signed_by_them_at   TEXT,
    created_at          TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (NOT (end_date IS NOT NULL AND term_months IS NOT NULL)),
    FOREIGN KEY (issuer_id) REFERENCES issuers(id) ON DELETE RESTRICT,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
);

-- Selected clause set for a contract. Each row is one clause slug from the
-- pack (e.g. 'confidentiality', 'fees', 'termination'), with optional body
-- override (if the user wrote a custom version). Ordered by `position`.
CREATE TABLE contract_clauses (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    contract_id  INTEGER NOT NULL,
    position     INTEGER NOT NULL,
    slug         TEXT NOT NULL,
    heading      TEXT,            -- overrides the pack's default heading
    body         TEXT,            -- overrides the pack's default body (markdown)
    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE,
    UNIQUE (contract_id, slug)
);

CREATE INDEX idx_contracts_issuer ON contracts(issuer_id);
CREATE INDEX idx_contracts_client ON contracts(client_id);
CREATE INDEX idx_contracts_kind   ON contracts(kind);
CREATE INDEX idx_contracts_status ON contracts(status);
CREATE INDEX idx_contract_clauses ON contract_clauses(contract_id, position);
