-- ============================================================================
-- NBO tenant schema — Migration 001: initial (consolidated from v2 database.sql)
-- ============================================================================
-- Applied against a newly-provisioned nbo_t_<slug> database (e.g. nbo_t_acme).
-- Run via:  php bin/migrate.php tenant --slug=<slug>
--
-- This is v2's full schema (all v2.1–v2.8 ALTERs rolled into the CREATE TABLE
-- statements so every tenant starts at the latest shape). Two deltas vs v2:
--
--   1. `users` gets a `global_user_id` column. In NBO, a tenant user row is a
--      pointer to a `nbo_master.global_users` row; the password lives there,
--      not here. `password` stays nullable for backward compatibility with
--      imported v2 data (Netpoa tenant's mysqldump will populate it), but new
--      v3 code never reads it.
--
--   2. Netpoa-specific seed data (Jacob's employees, Netpoa service providers,
--      Netpoa's company settings) is NOT here. A fresh tenant starts empty;
--      the tenant admin fills company settings via the first-run wizard.
--      Generic defaults only (rates, role catalogue, category dropdowns).
--
-- The WHMCS tables stay in every tenant's schema so the shared v2 API code
-- continues to work against all tenants uniformly — only Netpoa will ever have
-- `tenants.whmcs_enabled=1` and actually populate them.
-- ============================================================================

-- -- USERS (local to this tenant; authoritative password lives in nbo_master.global_users)
CREATE TABLE IF NOT EXISTS users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    global_user_id  INT UNSIGNED DEFAULT NULL,                       -- link to nbo_master.global_users.id
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(150) NOT NULL,
    password        VARCHAR(255) DEFAULT NULL,                       -- legacy; NULL for v3-native users
    role            VARCHAR(50)  NOT NULL DEFAULT 'accountant',      -- matches custom_roles.role_key
    employee_id     INT          DEFAULT NULL,
    is_active       TINYINT(1)   DEFAULT 1,
    last_login      DATETIME     NULL,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_users_email (email),
    UNIQUE KEY uniq_users_global (global_user_id),
    INDEX idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- CUSTOM ROLES (dynamic RBAC editable from the admin UI)
CREATE TABLE IF NOT EXISTS custom_roles (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    role_key    VARCHAR(50) UNIQUE NOT NULL,
    role_label  VARCHAR(100) NOT NULL,
    permissions TEXT DEFAULT NULL,                                   -- JSON array of permission keys
    is_system   TINYINT(1) DEFAULT 0,                                -- 1 = cannot be deleted
    created_by  INT DEFAULT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- USER ROLES (many-to-many; one user may hold multiple roles)
CREATE TABLE IF NOT EXISTS user_roles (
    user_id     INT NOT NULL,
    role_key    VARCHAR(50) NOT NULL,
    is_primary  TINYINT(1) DEFAULT 0,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_key),
    INDEX idx_user_roles_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- CLIENTS (used by invoicing autosave + browse)
CREATE TABLE IF NOT EXISTS clients (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    email       VARCHAR(150) DEFAULT NULL,
    phone       VARCHAR(60)  DEFAULT NULL,
    address     TEXT         DEFAULT NULL,
    tin_number  VARCHAR(50)  DEFAULT NULL,
    vrn_number  VARCHAR(50)  DEFAULT NULL,
    notes       TEXT         DEFAULT NULL,
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    created_by  INT          DEFAULT NULL,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_client_name (name),
    INDEX idx_client_name  (name),
    INDEX idx_client_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- INVOICES (table historically named whmcs_invoices; used for ALL invoices, WHMCS or not)
CREATE TABLE IF NOT EXISTS whmcs_invoices (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number  VARCHAR(50)  NOT NULL,
    client_name     VARCHAR(150) NOT NULL,
    client_email    VARCHAR(150) DEFAULT NULL,
    client_id       INT          DEFAULT NULL,
    service_name    VARCHAR(200) DEFAULT NULL,
    product_id      INT          DEFAULT NULL,
    quantity        INT          NOT NULL DEFAULT 1,
    subtotal        DECIMAL(14,2) NOT NULL DEFAULT 0,
    vat_rate        DECIMAL(5,2)  NOT NULL DEFAULT 0,
    vat_amount      DECIMAL(14,2) NOT NULL DEFAULT 0,
    vat_mode        VARCHAR(10)   NOT NULL DEFAULT 'exclusive',
    category        VARCHAR(200) DEFAULT 'Other',
    amount          DECIMAL(14,2) NOT NULL DEFAULT 0.00,              -- grand total (subtotal + vat)
    currency        VARCHAR(10)  DEFAULT 'TZS',
    payment_method  VARCHAR(80)  DEFAULT NULL,
    status          ENUM('Paid','Pending','Overdue','Cancelled') DEFAULT 'Pending',
    invoice_date    DATE         NOT NULL,
    due_date        DATE         DEFAULT NULL,
    paid_date       DATE         DEFAULT NULL,
    whmcs_id        VARCHAR(50)  DEFAULT NULL,
    notes           TEXT         DEFAULT NULL,
    created_by      INT          DEFAULT NULL,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_inv_status    (status),
    INDEX idx_inv_date      (invoice_date),
    INDEX idx_inv_product   (product_id),
    INDEX idx_inv_client_id (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- INVOICE LINE ITEMS
CREATE TABLE IF NOT EXISTS invoice_items (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id  INT NOT NULL,
    item_type   ENUM('service','product') NOT NULL DEFAULT 'service',
    product_id  INT          NULL,
    description VARCHAR(255) NOT NULL,
    quantity    DECIMAL(12,2) NOT NULL DEFAULT 1,
    unit_price  DECIMAL(14,2) NOT NULL DEFAULT 0,
    line_total  DECIMAL(14,2) NOT NULL DEFAULT 0,
    position    INT NOT NULL DEFAULT 0,
    INDEX idx_inv_items_invoice (invoice_id),
    INDEX idx_inv_items_product (product_id),
    FOREIGN KEY (invoice_id) REFERENCES whmcs_invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- EXPENSES (COGS + Operating + Payroll)
CREATE TABLE IF NOT EXISTS expenses (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    vendor          VARCHAR(150) NOT NULL,
    category        VARCHAR(200) DEFAULT 'Other',
    expense_type    ENUM('COGS','Operating','Payroll') DEFAULT 'Operating',
    amount          DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    currency        VARCHAR(10)   DEFAULT 'TZS',
    exchange_rate   DECIMAL(10,4) DEFAULT 1.0000,
    amount_tzs      DECIMAL(14,2) DEFAULT 0.00,
    payment_method  ENUM('Bank Transfer','Credit Card','PayPal','Mobile Money - MPESA','Mobile Money - Tigopesa','Cash','Other') DEFAULT 'Bank Transfer',
    expense_date    DATE NOT NULL,
    description     TEXT DEFAULT NULL,
    receipt_number  VARCHAR(100) DEFAULT NULL,
    transaction_ref VARCHAR(200) DEFAULT NULL,
    created_by      INT DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_exp_date (expense_date),
    INDEX idx_exp_cat  (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- OTHER INCOME
CREATE TABLE IF NOT EXISTS other_income (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    source      VARCHAR(150) NOT NULL,
    category    ENUM('Consulting','Affiliate','Refund','Grant','Investment','Other') DEFAULT 'Other',
    amount      DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    currency    VARCHAR(10) DEFAULT 'TZS',
    income_date DATE NOT NULL,
    notes       TEXT DEFAULT NULL,
    created_by  INT  DEFAULT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- EMPLOYEES
CREATE TABLE IF NOT EXISTS employees (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150) NOT NULL,
    email           VARCHAR(150) DEFAULT NULL,
    phone           VARCHAR(30)  DEFAULT NULL,
    department      VARCHAR(100) DEFAULT NULL,
    position        VARCHAR(100) DEFAULT NULL,
    employment_type VARCHAR(50)  DEFAULT 'Full-time',
    basic_salary    DECIMAL(14,2) DEFAULT 0.00,
    currency        VARCHAR(10)  DEFAULT 'TZS',
    bank_name       VARCHAR(100) DEFAULT NULL,
    bank_account    VARCHAR(50)  DEFAULT NULL,
    mobile_money    VARCHAR(50)  DEFAULT NULL,
    tin_number      VARCHAR(50)  DEFAULT NULL,
    nssf_number     VARCHAR(50)  DEFAULT NULL,
    wcf_number      VARCHAR(50)  DEFAULT NULL,
    national_id     VARCHAR(50)  DEFAULT NULL,
    physical_address VARCHAR(255) DEFAULT NULL,
    profile_photo   VARCHAR(255) DEFAULT NULL,
    kin_name        VARCHAR(150) DEFAULT NULL,
    kin_phone       VARCHAR(40)  DEFAULT NULL,
    kin_relationship VARCHAR(60) DEFAULT NULL,
    contract_file   VARCHAR(255) DEFAULT NULL,
    job_description TEXT DEFAULT NULL,
    contract_start  DATE DEFAULT NULL,
    contract_end    DATE DEFAULT NULL,
    contract_type   VARCHAR(50) DEFAULT NULL,
    contract_notes  TEXT DEFAULT NULL,
    exempt_paye     TINYINT(1) DEFAULT 0,
    exempt_nssf     TINYINT(1) DEFAULT 0,
    exempt_wcf      TINYINT(1) DEFAULT 0,
    start_date      DATE DEFAULT NULL,
    status          ENUM('Active','On Leave','Terminated') DEFAULT 'Active',
    notes           TEXT DEFAULT NULL,
    created_by      INT  DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_emp_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- PAYROLL
CREATE TABLE IF NOT EXISTS payroll (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    employee_id     INT NOT NULL,
    payroll_month   VARCHAR(7) NOT NULL,
    basic_salary    DECIMAL(14,2) DEFAULT 0.00,
    allowances      DECIMAL(14,2) DEFAULT 0.00,
    overtime        DECIMAL(14,2) DEFAULT 0.00,
    deductions      DECIMAL(14,2) DEFAULT 0.00,
    loan_deduction  DECIMAL(14,2) DEFAULT 0.00,
    nssf_employee   DECIMAL(14,2) DEFAULT 0.00,
    nssf_employer   DECIMAL(14,2) DEFAULT 0.00,
    wcf             DECIMAL(14,2) DEFAULT 0.00,
    paye            DECIMAL(14,2) DEFAULT 0.00,
    gross_salary    DECIMAL(14,2) DEFAULT 0.00,
    net_salary      DECIMAL(14,2) DEFAULT 0.00,
    status          ENUM('Pending','Paid','Cancelled') DEFAULT 'Pending',
    payment_date    DATE DEFAULT NULL,
    payment_method  VARCHAR(50) DEFAULT 'Bank Transfer',
    notes           TEXT DEFAULT NULL,
    created_by      INT DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_payroll (employee_id, payroll_month),
    INDEX idx_payroll_month (payroll_month),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- PETTY CASH (actual disbursements)
CREATE TABLE IF NOT EXISTS petty_cash (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    category    VARCHAR(100) DEFAULT 'Other',
    description VARCHAR(200) DEFAULT NULL,
    amount      DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    cash_date   DATE NOT NULL,
    approved_by VARCHAR(100) DEFAULT NULL,
    received_by VARCHAR(100) DEFAULT NULL,
    created_by  INT DEFAULT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- PETTY CASH REQUESTS (approval workflow)
CREATE TABLE IF NOT EXISTS petty_cash_requests (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    employee_id      INT NOT NULL,
    category         VARCHAR(100) DEFAULT 'Other',
    description      VARCHAR(200) NOT NULL,
    amount           DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    reason           TEXT DEFAULT NULL,
    status           ENUM('Pending','Approved','Rejected','Paid') DEFAULT 'Pending',
    requested_by     INT DEFAULT NULL,
    approved_by      INT DEFAULT NULL,
    approved_at      DATETIME DEFAULT NULL,
    rejection_reason VARCHAR(200) DEFAULT NULL,
    paid_by          INT DEFAULT NULL,
    paid_at          DATETIME DEFAULT NULL,
    petty_cash_id    INT DEFAULT NULL,
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- SERVICE PROVIDERS (recurring vendor bills)
CREATE TABLE IF NOT EXISTS service_providers (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(150) NOT NULL,
    service_description TEXT DEFAULT NULL,
    website             VARCHAR(255) DEFAULT NULL,
    payment_day         VARCHAR(50) DEFAULT NULL,
    monthly_cost        DECIMAL(14,2) DEFAULT 0.00,
    annual_cost         DECIMAL(14,2) DEFAULT 0.00,
    currency            VARCHAR(10) DEFAULT 'TZS',
    category            VARCHAR(100) DEFAULT NULL,
    is_active           TINYINT(1) DEFAULT 1,
    notes               TEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- AUDIT LOG (tenant-scoped; master-level actions go in nbo_master.master_audit_log)
CREATE TABLE IF NOT EXISTS audit_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT DEFAULT NULL,
    action      VARCHAR(100) NOT NULL,
    table_name  VARCHAR(50)  DEFAULT NULL,
    record_id   INT          DEFAULT NULL,
    ip_address  VARCHAR(45)  DEFAULT NULL,
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_user   (user_id),
    INDEX idx_audit_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- SETTINGS (key/value; per-tenant)
CREATE TABLE IF NOT EXISTS settings (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    setting_key   VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT DEFAULT NULL,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- WHMCS SYNC LOG (only Netpoa tenant uses this; harmless empty table elsewhere)
CREATE TABLE IF NOT EXISTS whmcs_sync_log (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    sync_type      VARCHAR(50) DEFAULT 'direct_db',
    records_synced INT DEFAULT 0,
    status         ENUM('success','error') DEFAULT 'success',
    message        TEXT DEFAULT NULL,
    synced_by      INT DEFAULT NULL,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- EMPLOYEE LEAVES
CREATE TABLE IF NOT EXISTS employee_leaves (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    employee_id     INT NOT NULL,
    leave_type      ENUM('Annual','Sick','Maternity','Paternity','Emergency','Unpaid','Other') DEFAULT 'Annual',
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL,
    days_requested  INT DEFAULT 1,
    reason          TEXT DEFAULT NULL,
    status          ENUM('Pending','Approved','Rejected') DEFAULT 'Pending',
    reviewed_by     INT DEFAULT NULL,
    reviewed_at     DATETIME DEFAULT NULL,
    manager_notes   TEXT DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- ATTENDANCE
CREATE TABLE IF NOT EXISTS attendance (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    employee_id     INT NOT NULL,
    attendance_date DATE NOT NULL,
    check_in        TIME DEFAULT NULL,
    check_out       TIME DEFAULT NULL,
    status          ENUM('Present','Absent','Late','Half-day','Leave','Holiday') DEFAULT 'Present',
    notes           VARCHAR(200) DEFAULT NULL,
    recorded_by     INT DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_att (employee_id, attendance_date),
    INDEX idx_att_date (attendance_date),
    INDEX idx_att_emp  (employee_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- DAILY REPORTS
CREATE TABLE IF NOT EXISTS daily_reports (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    employee_id       INT NOT NULL,
    report_date       DATE NOT NULL,
    department        VARCHAR(100) DEFAULT NULL,
    tasks_completed   TEXT DEFAULT NULL,
    tasks_in_progress TEXT DEFAULT NULL,
    challenges        TEXT DEFAULT NULL,
    plan_tomorrow     TEXT DEFAULT NULL,
    time_spent        VARCHAR(50) DEFAULT NULL,
    status            ENUM('Draft','Submitted','Approved','Rejected') DEFAULT 'Draft',
    manager_comment   TEXT DEFAULT NULL,
    reviewed_by       INT DEFAULT NULL,
    reviewed_at       DATETIME DEFAULT NULL,
    created_by        INT DEFAULT NULL,
    created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_daily_report (employee_id, report_date),
    INDEX idx_report_date   (report_date),
    INDEX idx_report_emp    (employee_id),
    INDEX idx_report_status (status),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- EMPLOYEE LOANS & SALARY ADVANCES
CREATE TABLE IF NOT EXISTS employee_loans (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    employee_id       INT NOT NULL,
    loan_type         ENUM('Loan','Salary Advance') DEFAULT 'Loan',
    amount_requested  DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    amount_approved   DECIMAL(14,2) DEFAULT 0.00,
    repayment_months  INT DEFAULT 1,
    monthly_deduction DECIMAL(14,2) DEFAULT 0.00,
    amount_paid       DECIMAL(14,2) DEFAULT 0.00,
    balance           DECIMAL(14,2) DEFAULT 0.00,
    purpose           VARCHAR(255) DEFAULT NULL,
    notes             TEXT DEFAULT NULL,
    hr_notes          TEXT DEFAULT NULL,
    rejection_reason  VARCHAR(255) DEFAULT NULL,
    status            ENUM('Pending','Active','Completed','Rejected','Cancelled') DEFAULT 'Pending',
    approved_by       INT DEFAULT NULL,
    approved_at       DATETIME DEFAULT NULL,
    created_by        INT DEFAULT NULL,
    created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_loan_emp    (employee_id),
    INDEX idx_loan_status (status),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- LOAN DEDUCTION LEDGER (one row per payroll deduction against a loan)
CREATE TABLE IF NOT EXISTS loan_deductions (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    loan_id          INT NOT NULL,
    payroll_id       INT DEFAULT NULL,
    employee_id      INT NOT NULL,
    amount           DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    deduction_month  VARCHAR(7) DEFAULT NULL,
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (loan_id) REFERENCES employee_loans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- PRODUCTS (inventory catalogue — tenants without inventory simply leave empty)
CREATE TABLE IF NOT EXISTS products (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    sku            VARCHAR(64) UNIQUE,
    name           VARCHAR(200) NOT NULL,
    category       VARCHAR(100) DEFAULT NULL,
    description    TEXT DEFAULT NULL,
    unit           VARCHAR(30) DEFAULT 'pcs',
    cost_price     DECIMAL(14,2) DEFAULT 0.00,
    sell_price     DECIMAL(14,2) DEFAULT 0.00,
    currency       VARCHAR(10) DEFAULT 'TZS',
    reorder_level  INT DEFAULT 0,
    stock_qty      INT DEFAULT 0,
    is_active      TINYINT(1) DEFAULT 1,
    created_by     INT DEFAULT NULL,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- INVENTORY MOVEMENTS (in/out/adjust)
CREATE TABLE IF NOT EXISTS inventory_movements (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    product_id    INT NOT NULL,
    movement_type ENUM('IN','OUT','ADJUST') DEFAULT 'IN',
    quantity      INT NOT NULL DEFAULT 0,
    unit_cost     DECIMAL(14,2) DEFAULT 0.00,
    reference     VARCHAR(100) DEFAULT NULL,
    notes         VARCHAR(255) DEFAULT NULL,
    movement_date DATE NOT NULL,
    created_by    INT DEFAULT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_inv_prod (product_id),
    INDEX idx_inv_move_date (movement_date),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- EMPLOYEE TASKS (goals/KPIs)
CREATE TABLE IF NOT EXISTS employee_tasks (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    description  TEXT DEFAULT NULL,
    task_type    ENUM('checkbox','numeric') NOT NULL DEFAULT 'checkbox',
    target_value DECIMAL(14,2) DEFAULT NULL,
    unit         VARCHAR(30) DEFAULT NULL,
    frequency    ENUM('Daily','Weekly','Monthly','One-off') NOT NULL DEFAULT 'Daily',
    department   VARCHAR(100) DEFAULT NULL,
    assigned_to  INT DEFAULT NULL,
    priority     ENUM('Low','Medium','High') NOT NULL DEFAULT 'Medium',
    weight       TINYINT UNSIGNED NOT NULL DEFAULT 1,
    start_date   DATE NOT NULL,
    due_date     DATE DEFAULT NULL,
    status       ENUM('Active','Paused','Archived') NOT NULL DEFAULT 'Active',
    created_by   INT NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_task_dept     (department),
    INDEX idx_task_assignee (assigned_to),
    FOREIGN KEY (assigned_to) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by)  REFERENCES users(id)     ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- EMPLOYEE TASK LOG (per-period completion)
CREATE TABLE IF NOT EXISTS employee_task_log (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    task_id      INT NOT NULL,
    employee_id  INT NOT NULL,
    period_date  DATE NOT NULL,
    status       ENUM('Done','Missed','In Progress') NOT NULL DEFAULT 'Done',
    actual_value DECIMAL(14,2) DEFAULT NULL,
    score        DECIMAL(5,2)  DEFAULT NULL,
    note         TEXT DEFAULT NULL,
    logged_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_task_period (task_id, employee_id, period_date),
    INDEX idx_tasklog_emp         (employee_id),
    INDEX idx_tasklog_period_emp  (period_date, employee_id),
    INDEX idx_tasklog_score       (score),
    FOREIGN KEY (task_id)     REFERENCES employee_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id)      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- PASSWORD RESET TOKENS (tenant-local; used ONLY for legacy v2 password flow on the Netpoa tenant's imported data — NBO-native reset flow lives in nbo_master.password_resets)
CREATE TABLE IF NOT EXISTS password_resets (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    token_hash  VARCHAR(80) NOT NULL,
    expires_at  TIMESTAMP NOT NULL,
    used_at     TIMESTAMP NULL DEFAULT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pwreset_user  (user_id),
    INDEX idx_pwreset_token (token_hash),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- Migration-runner bookkeeping for the TENANT stream.
CREATE TABLE IF NOT EXISTS schema_migrations (
    version    VARCHAR(20) PRIMARY KEY,
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    checksum   CHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- GENERIC DEFAULT DATA
-- (No Netpoa-specific data here — fresh tenants set their own company settings
--  via the first-run wizard. Only defaults that make sense for ANY company.)
-- ============================================================================

-- Built-in role catalogue (mirrors v2's custom_roles; tenant admin can edit via UI)
INSERT INTO custom_roles (role_key, role_label, permissions, is_system) VALUES
('admin',      'Administrator',        '["view","create","edit","delete","settings","users","hr","finance","operations","inventory","sales","reports"]', 1),
('director',   'Director',             '["view","create","edit","finance","hr","operations","inventory","sales","reports"]',                             1),
('accountant', 'Accountant',           '["view","create","edit","finance","inventory","reports"]',                                                        1),
('hr',         'HR',                   '["view","create","edit","hr"]',                                                                                   1),
('om',         'Operations Manager',   '["view","create","edit","operations","inventory","reports"]',                                                    1),
('sales',      'Sales',                '["view","create","edit","sales","inventory"]',                                                                    0),
('employee',   'Staff',                '["view"]',                                                                                                        1)
ON DUPLICATE KEY UPDATE role_key = role_key;

-- Tanzania-standard statutory defaults + dropdown category lists
INSERT INTO settings (setting_key, setting_value) VALUES
-- Company identity — intentionally blank, tenant fills via first-run wizard
('company_name',             ''),
('company_address',          ''),
('company_phone',            ''),
('company_website',          ''),
('company_tin',              ''),
('company_vrn',              ''),
-- Tax & payroll defaults (Tanzania)
('currency',                 'TZS'),
('tax_rate',                 '30'),
('nssf_rate_employee',       '10'),
('nssf_rate_employer',       '10'),
('wcf_rate',                 '0.5'),
('financial_year_start',     '01-01'),
-- Invoice VAT
('vat_rate_default',         '0'),
('vat_mode',                 'exclusive'),
-- USD display (off by default)
('usd_display_enabled',      '0'),
('usd_rate',                 '2500'),
-- Loan + salary advance windows
('salary_advance_enabled',   '1'),
('salary_advance_day_from',  '10'),
('salary_advance_day_to',    '13'),
('loan_enabled',             '1'),
('loan_day_from',            '10'),
('loan_day_to',              '13'),
('loan_advance_closed_msg',  'You cannot apply to Loan or Salary advance this time'),
-- WHMCS (disabled by default — only the Netpoa tenant will fill these)
('whmcs_url',                ''),
('whmcs_api_identifier',     ''),
('whmcs_api_secret',         ''),
-- Invoice payment details (tenant admin fills via Settings page)
('pay_bank_name',            ''),
('pay_bank_account',         ''),
('pay_bank_branch',          ''),
('pay_swift_code',           ''),
('pay_lipa_number',          ''),
('pay_mobile_money',         ''),
('pay_instructions',         ''),
-- Dropdown category lists (tenant admin can edit from Settings)
('expense_categories',       '["Server - Hosting","Domain Registration","Licenses","Salaries","Director Remuneration","Office Rent","Internet & Utilities","Transport & Fuel","Marketing","Petty Cash","Tax","Banking Fees","Other"]'),
('invoice_categories',       '["Consulting","Services","Training","Sales","Other"]'),
('petty_categories',         '["Parking","Breakfast/Lunch","Water","Electricity","Phone Bill","Internet","Stationery","Transport","Office Supplies","Other"]'),
('departments',              '["General","Finance","Technical","Operations","HR","Management","Sales","Other"]')
ON DUPLICATE KEY UPDATE setting_key = setting_key;
