-- ============================================================
-- Retirement Decision Engine — CostusWorx © 2026
-- retireuk.costusworx.co.za  (UK edition)
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';  -- UTC (UK standard; BST handled at application layer)

-- ── Plans (prices in GBP) ────────────────────────────────────
CREATE TABLE plans (
    id               TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(50)  NOT NULL,          -- Solo, Practice, Enterprise
    max_advisers     SMALLINT     NOT NULL DEFAULT 1,
    max_clients      SMALLINT     NOT NULL DEFAULT 25,
    max_runs_month   SMALLINT     NOT NULL DEFAULT 100,
    has_white_label  TINYINT(1)   NOT NULL DEFAULT 0,
    has_api          TINYINT(1)   NOT NULL DEFAULT 0,
    has_pdf          TINYINT(1)   NOT NULL DEFAULT 1,
    has_csv          TINYINT(1)   NOT NULL DEFAULT 0,
    price_gbp        DECIMAL(9,2) NOT NULL DEFAULT 0.00,   -- GBP (was price_zar)
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO plans (name, max_advisers, max_clients, max_runs_month, has_white_label, has_api, has_pdf, has_csv, price_gbp) VALUES
('Free',       1,   5,   10,  0, 0, 0, 0,    0.00),
('Solo',       1,   25,  150, 0, 0, 1, 0,   29.00),
('Practice',   5,   200, 750, 0, 0, 1, 1,   99.00),
('Enterprise', 999, 999, 999, 1, 1, 1, 1,  499.00);

-- ── Tenants ──────────────────────────────────────────────────
CREATE TABLE tenants (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(120) NOT NULL,
    subdomain    VARCHAR(60)  NOT NULL UNIQUE,       -- mycompany.retireuk.costusworx.co.za
    plan_id      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    status       ENUM('active','suspended','cancelled') NOT NULL DEFAULT 'active',
    logo_path    VARCHAR(255)  DEFAULT NULL,
    primary_color VARCHAR(7)   DEFAULT '#1B4F72',    -- hex for white-label theming
    created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Subscriptions ─────────────────────────────────────────────
CREATE TABLE subscriptions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT UNSIGNED NOT NULL,
    plan_id         TINYINT UNSIGNED NOT NULL,
    status          ENUM('active','past_due','cancelled','trialling') NOT NULL DEFAULT 'trialling',
    started_at      DATETIME     NOT NULL,
    expires_at      DATETIME     NOT NULL,
    stripe_sub_id   VARCHAR(100) DEFAULT NULL,       -- Stripe (replaces PayFast for UK)
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    FOREIGN KEY (plan_id)   REFERENCES plans(id),
    INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Credits (pay-per-report) ──────────────────────────────────
CREATE TABLE credits (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT UNSIGNED NOT NULL UNIQUE,
    balance     SMALLINT     NOT NULL DEFAULT 0,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Users ─────────────────────────────────────────────────────
CREATE TABLE users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT UNSIGNED NULL,   -- NULL for superadmin (no tenant context)
    email         VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name          VARCHAR(120) NOT NULL,
    role          ENUM('admin','adviser','viewer','superadmin') NOT NULL DEFAULT 'adviser',
    status        ENUM('active','suspended') NOT NULL DEFAULT 'active',
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_email (tenant_id, email),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Clients (PII encrypted at application layer — UK GDPR Art.32) ────────────
CREATE TABLE clients (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           INT UNSIGNED NOT NULL,
    adviser_id          INT UNSIGNED NOT NULL,
    name_enc            TEXT         NOT NULL,   -- AES-256-GCM encrypted
    ni_number_enc       TEXT         DEFAULT NULL, -- National Insurance number (replaces SA ID)
    age                 TINYINT UNSIGNED NOT NULL,
    life_expectancy     TINYINT UNSIGNED NOT NULL DEFAULT 87, -- UK ONS 2024 life expectancy
    ukgdpr_consent      TINYINT(1)   NOT NULL DEFAULT 0,      -- UK GDPR (replaces POPI)
    consent_date        DATETIME     DEFAULT NULL,
    status              ENUM('active','archived') NOT NULL DEFAULT 'active',
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id),
    FOREIGN KEY (adviser_id) REFERENCES users(id),
    INDEX idx_tenant    (tenant_id),
    INDEX idx_adviser   (adviser_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── UK GDPR Consent log (replaces popi_consents) ─────────────
CREATE TABLE gdpr_consents (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT UNSIGNED NOT NULL,
    client_id     INT UNSIGNED NOT NULL,
    consent_type  VARCHAR(60)  NOT NULL,   -- 'simulation_data','pdf_report','marketing'
    lawful_basis  VARCHAR(40)  NOT NULL DEFAULT 'consent',  -- GDPR lawful basis
    given_at      DATETIME     DEFAULT NULL,
    withdrawn_at  DATETIME     DEFAULT NULL,
    created_by    INT UNSIGNED NOT NULL,
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id),
    FOREIGN KEY (client_id)  REFERENCES clients(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Scenarios ─────────────────────────────────────────────────
CREATE TABLE scenarios (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id          INT UNSIGNED NOT NULL,
    client_id          INT UNSIGNED NOT NULL,
    adviser_id         INT UNSIGNED NOT NULL,
    name               VARCHAR(120) NOT NULL,
    risk_profile       ENUM('conservative','moderate','aggressive') NOT NULL DEFAULT 'moderate',
    inflation_rate     DECIMAL(5,4) NOT NULL DEFAULT 0.0250,  -- 2.5% UK long-run CPI target
    withdrawal_amount  DECIMAL(12,2) NOT NULL DEFAULT 0.00,   -- annual, GBP
    simulation_years   TINYINT UNSIGNED NOT NULL DEFAULT 25,
    retirement_date         DATE          NULL,                  -- NULL/today/past = drawdown starts now
    monthly_contribution    DECIMAL(12,2) NOT NULL DEFAULT 0.00, -- GBP/month during accumulation
    contribution_escalation DECIMAL(5,4)  NOT NULL DEFAULT 0.0000, -- annual, e.g. 0.0250 = 2.5%
    created_at         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id),
    FOREIGN KEY (client_id)  REFERENCES clients(id),
    FOREIGN KEY (adviser_id) REFERENCES users(id),
    INDEX idx_tenant   (tenant_id),
    INDEX idx_client   (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Assets ────────────────────────────────────────────────────
-- UK asset types added; SA legacy types retained for backward-compat.
CREATE TABLE assets (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id          INT UNSIGNED NOT NULL,
    scenario_id        INT UNSIGNED NOT NULL,
    asset_type         ENUM(
                           -- UK types
                           'sipp',
                           'isa',
                           'workplace_pension',
                           'state_pension',
                           -- generic (both regions)
                           'cash',
                           'unit_trust',
                           'fixed_deposit',
                           'offshore',
                           -- SA legacy (backward-compat only)
                           'living_annuity'
                       ) NOT NULL,
    name               VARCHAR(120) NOT NULL,
    value              DECIMAL(14,2) NOT NULL DEFAULT 0.00,   -- GBP
    equity_weight      DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
    bond_weight        DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
    cash_weight        DECIMAL(5,4) NOT NULL DEFAULT 1.0000,
    drawdown_rate      DECIMAL(5,4) NOT NULL DEFAULT 0.0000,  -- annual proportion
    is_drawdown_source TINYINT(1)   NOT NULL DEFAULT 0,
    is_locked          TINYINT(1)   NOT NULL DEFAULT 0,
    priority_order     TINYINT UNSIGNED NOT NULL DEFAULT 1,
    -- UK fund identifiers (replaces jse_code with SEDOL)
    sedol              VARCHAR(7)   DEFAULT NULL COMMENT 'UK Stock Exchange Daily Official List code',
    created_at         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id)   REFERENCES tenants(id),
    FOREIGN KEY (scenario_id) REFERENCES scenarios(id) ON DELETE CASCADE,
    INDEX idx_scenario (scenario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── UK Market Index Registry ───────────────────────────────────
-- FTSE Russell / BoE / ONS indices (replaces JSE/SARB/StatsSA).
-- See uk_market_data.sql for the actual historical return rows.
CREATE TABLE market_index_registry (
    id            TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_class   ENUM('equity','bonds','cash','inflation') NOT NULL UNIQUE,
    index_code    VARCHAR(20)  NOT NULL,
    index_name    VARCHAR(120) NOT NULL,
    provider      VARCHAR(80)  NOT NULL,
    provider_url  VARCHAR(255) DEFAULT NULL,
    notes         TEXT         DEFAULT NULL,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- UK default benchmarks (see uk_market_data.sql for full data population)
INSERT INTO market_index_registry (asset_class, index_code, index_name, provider, provider_url, notes) VALUES
('equity',    'FTAS',       'FTSE All-Share Total Return Index',       'FTSE Russell', 'https://www.ftserussell.com', 'Broad UK market ~600 stocks. Use FTSE100/UKX for large-cap.'),
('bonds',     'FTSE-GILTS', 'FTSE UK Gilts All Stocks Total Return',   'FTSE Russell', 'https://www.ftserussell.com', 'UK government gilts. Standard fixed-income benchmark.'),
('cash',      'SONIA',      'Sterling Overnight Index Average',        'Bank of England', 'https://www.bankofengland.co.uk', 'UK overnight rate proxy. Replaced LIBOR 2021.'),
('inflation', 'CPI-ONS',    'Consumer Price Index (All Items)',        'ONS', 'https://www.ons.gov.uk', 'UK CPI published monthly by ONS. Triple-lock proxy for State Pension modelling.');

-- ── Market Data ───────────────────────────────────────────────
-- Populated by uk_market_data.sql.
CREATE TABLE market_data (
    id              SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    year            YEAR         NOT NULL UNIQUE,
    equity_return   DECIMAL(7,4) NOT NULL COMMENT 'FTSE All-Share TR',
    bond_return     DECIMAL(7,4) NOT NULL COMMENT 'FTSE Gilts All Stocks TR',
    cash_return     DECIMAL(7,4) NOT NULL COMMENT 'BoE Base Rate / SONIA',
    inflation       DECIMAL(7,4) NOT NULL COMMENT 'ONS CPI annual',
    equity_index    VARCHAR(20)  NOT NULL DEFAULT 'FTAS',
    bond_index      VARCHAR(20)  NOT NULL DEFAULT 'FTSE-GILTS',
    cash_index      VARCHAR(20)  NOT NULL DEFAULT 'SONIA',
    data_source     VARCHAR(120) NOT NULL DEFAULT 'FTSE Russell / BoE / ONS',
    verified        TINYINT(1)   NOT NULL DEFAULT 0,
    INDEX idx_year (year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Simulation Runs ───────────────────────────────────────────
CREATE TABLE simulation_runs (
    id                         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id                  INT UNSIGNED NOT NULL,
    scenario_id                INT UNSIGNED NOT NULL,
    adviser_id                 INT UNSIGNED NOT NULL,
    run_count                  SMALLINT     NOT NULL DEFAULT 500,
    success_rate               DECIMAL(5,2) NOT NULL,
    median_years_remaining     TINYINT      NOT NULL,
    worst_depletion_year       SMALLINT     DEFAULT NULL,
    median_path_json           MEDIUMTEXT   NOT NULL,
    worst_path_json            MEDIUMTEXT   NOT NULL,
    best_path_json             MEDIUMTEXT   NOT NULL,
    confidence_bands_json      MEDIUMTEXT   NOT NULL,
    credits_charged            TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at                 DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id)   REFERENCES tenants(id),
    FOREIGN KEY (scenario_id) REFERENCES scenarios(id),
    FOREIGN KEY (adviser_id)  REFERENCES users(id),
    INDEX idx_tenant    (tenant_id),
    INDEX idx_scenario  (scenario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Audit Log (UK GDPR / FCA requirement) ────────────────────
CREATE TABLE audit_log (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT UNSIGNED NOT NULL,
    user_id      INT UNSIGNED DEFAULT NULL,
    action       VARCHAR(80)  NOT NULL,
    entity_type  VARCHAR(50)  DEFAULT NULL,
    entity_id    INT UNSIGNED DEFAULT NULL,
    ip_address   VARCHAR(45)  DEFAULT NULL,
    user_agent   VARCHAR(255) DEFAULT NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant  (tenant_id),
    INDEX idx_user    (user_id),
    INDEX idx_action  (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Usage tracking (quota enforcement) ───────────────────────
CREATE TABLE usage_monthly (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT UNSIGNED NOT NULL,
    month       CHAR(7)      NOT NULL,
    run_count   SMALLINT     NOT NULL DEFAULT 0,
    UNIQUE KEY uq_tenant_month (tenant_id, month),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Run uk_tax_2025.sql next to seed HMRC brackets ───────────
-- ── Run uk_state_pension_schema.sql for State Pension tables ──
-- ── Run uk_market_data.sql for FTSE/BoE/ONS historical data ──
