-- ============================================================================
-- NBO tenant schema — Migration 002: catch-up on v2.9 through v2.21 changes
-- ============================================================================
-- The initial schema (001) was captured from an earlier v2 snapshot. After
-- that snapshot, v2 shipped migrations v2.9 through v2.21 that added several
-- columns and tables now referenced by api/index.php. This migration brings
-- every tenant DB up to parity with the current v2 shape so those handlers
-- stop erroring with "Unknown column ...".
--
-- Idempotent for fresh tenants (001 has NONE of the columns/tables added
-- here). For safety on any DB that's been partially hand-patched, each ALTER
-- is wrapped in an INFORMATION_SCHEMA guard so re-running is a no-op.
--
-- Run: php bin/migrate.php tenant --all   (applies to gc, kulaya, kilimo, and
-- any tenant provisioned later).
-- ============================================================================

-- ===========================================================================
-- USERS: phone, date_of_birth, two_factor_enabled, last_activity
-- ===========================================================================

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='phone');
SET @s := IF(@x=0,
    'ALTER TABLE users ADD COLUMN phone VARCHAR(30) DEFAULT NULL AFTER email',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='date_of_birth');
SET @s := IF(@x=0,
    'ALTER TABLE users ADD COLUMN date_of_birth DATE NULL DEFAULT NULL AFTER phone',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='two_factor_enabled');
SET @s := IF(@x=0,
    'ALTER TABLE users ADD COLUMN two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER is_active',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='last_activity');
SET @s := IF(@x=0,
    'ALTER TABLE users ADD COLUMN last_activity DATETIME NULL DEFAULT NULL AFTER last_login, ADD INDEX idx_last_activity (last_activity)',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- Seed last_activity from last_login so the "who's online" view shows sensible data immediately
UPDATE users SET last_activity = last_login WHERE last_activity IS NULL AND last_login IS NOT NULL;

-- ===========================================================================
-- EMPLOYEES: employee_number, date_of_birth, termination_date
-- ===========================================================================

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employees' AND COLUMN_NAME='employee_number');
SET @s := IF(@x=0,
    'ALTER TABLE employees ADD COLUMN employee_number VARCHAR(20) DEFAULT NULL AFTER id, ADD UNIQUE KEY uk_employee_number (employee_number)',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employees' AND COLUMN_NAME='date_of_birth');
SET @s := IF(@x=0,
    'ALTER TABLE employees ADD COLUMN date_of_birth DATE NULL DEFAULT NULL AFTER start_date, ADD INDEX idx_emp_dob (date_of_birth)',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employees' AND COLUMN_NAME='termination_date');
SET @s := IF(@x=0,
    'ALTER TABLE employees ADD COLUMN termination_date DATE NULL DEFAULT NULL AFTER status, ADD INDEX idx_termination_date (termination_date)',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ===========================================================================
-- ATTENDANCE: self-service checkin (v2.14) — GPS / IP / source / work_summary / selfie
-- ===========================================================================

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='check_in_at');
SET @s := IF(@x=0,
    'ALTER TABLE attendance ADD COLUMN check_in_at DATETIME DEFAULT NULL AFTER check_in',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='check_out_at');
SET @s := IF(@x=0,
    'ALTER TABLE attendance ADD COLUMN check_out_at DATETIME DEFAULT NULL AFTER check_out',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='check_in_lat');
SET @s := IF(@x=0,
    'ALTER TABLE attendance ADD COLUMN check_in_lat DECIMAL(10,7) DEFAULT NULL, ADD COLUMN check_in_lng DECIMAL(10,7) DEFAULT NULL',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='check_out_lat');
SET @s := IF(@x=0,
    'ALTER TABLE attendance ADD COLUMN check_out_lat DECIMAL(10,7) DEFAULT NULL, ADD COLUMN check_out_lng DECIMAL(10,7) DEFAULT NULL',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='check_in_ip');
SET @s := IF(@x=0,
    'ALTER TABLE attendance ADD COLUMN check_in_ip VARCHAR(45) DEFAULT NULL, ADD COLUMN check_out_ip VARCHAR(45) DEFAULT NULL',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='user_agent');
SET @s := IF(@x=0,
    'ALTER TABLE attendance ADD COLUMN user_agent VARCHAR(255) DEFAULT NULL',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='location_type');
SET @s := IF(@x=0,
    "ALTER TABLE attendance ADD COLUMN location_type ENUM('On-site','Remote','Unknown') DEFAULT 'Unknown'",
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='work_summary');
SET @s := IF(@x=0,
    'ALTER TABLE attendance ADD COLUMN work_summary TEXT DEFAULT NULL',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='source');
SET @s := IF(@x=0,
    "ALTER TABLE attendance ADD COLUMN source ENUM('Portal','Manual','SMS','Cron') DEFAULT 'Manual'",
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance' AND COLUMN_NAME='selfie_path');
SET @s := IF(@x=0,
    'ALTER TABLE attendance ADD COLUMN selfie_path VARCHAR(255) DEFAULT NULL',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ===========================================================================
-- PUBLIC HOLIDAYS (v2.14) — so the cron auto-absent job skips them
-- ===========================================================================

CREATE TABLE IF NOT EXISTS public_holidays (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    holiday_date DATE NOT NULL UNIQUE,
    name         VARCHAR(120) NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_holiday_date (holiday_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO public_holidays (holiday_date, name) VALUES
('2026-01-01', "New Year's Day"),
('2026-01-12', 'Zanzibar Revolution Day'),
('2026-04-03', 'Good Friday'),
('2026-04-05', 'Easter Sunday'),
('2026-04-06', 'Easter Monday'),
('2026-04-07', 'Sheikh Abeid Karume Day'),
('2026-04-26', 'Union Day'),
('2026-05-01', "Workers' Day"),
('2026-07-07', 'Saba Saba Day'),
('2026-08-08', 'Nane Nane Day'),
('2026-10-14', 'Nyerere Day'),
('2026-12-09', 'Independence Day'),
('2026-12-25', 'Christmas Day'),
('2026-12-26', 'Boxing Day');

-- ===========================================================================
-- DIRECTOR'S LOAN ACCOUNT (v2.13)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS director_transactions (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    tx_date       DATE NOT NULL,
    tx_type       ENUM('Withdrawal','Repayment') NOT NULL,
    director_name VARCHAR(150) NOT NULL,
    amount        DECIMAL(15,2) NOT NULL,
    method        VARCHAR(50) DEFAULT 'Cash',
    reference     VARCHAR(100) DEFAULT NULL,
    description   TEXT,
    created_by    INT DEFAULT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tx_date (tx_date),
    INDEX idx_director_name (director_name),
    INDEX idx_tx_type (tx_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===========================================================================
-- SMS: logs + templates + settings (v2.15, v2.17, v2.18, v2.19)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS sms_log (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    phone           VARCHAR(20) NOT NULL,
    recipient_type  ENUM('Employee','Client','Manual','Other') DEFAULT 'Manual',
    recipient_id    INT DEFAULT NULL,
    recipient_name  VARCHAR(150) DEFAULT NULL,
    sender_id       VARCHAR(20) DEFAULT NULL,
    message         TEXT NOT NULL,
    segments        INT DEFAULT 1,
    status          ENUM('Sent','Failed','Pending') DEFAULT 'Pending',
    beem_request_id VARCHAR(64) DEFAULT NULL,
    error_message   VARCHAR(500) DEFAULT NULL,
    trigger_event   VARCHAR(64) DEFAULT 'manual',
    sent_by         INT DEFAULT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_status (status),
    INDEX idx_sms_created_at (created_at),
    INDEX idx_trigger_event (trigger_event)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sms_templates (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    body       TEXT NOT NULL,
    category   VARCHAR(50) DEFAULT 'General',
    is_active  TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO sms_templates (name, body, category) VALUES
('Payroll paid',
 'Hi {{name}}, your salary for {{month}} of TZS {{amount}} has been paid. Payslip sent to your email. — {{company}}',
 'Payroll'),
('Cash request approved',
 'Hi {{name}}, your cash request of TZS {{amount}} for "{{description}}" has been APPROVED. You will be notified when paid. — {{company}}',
 'Cash Request'),
('Cash request rejected',
 'Hi {{name}}, your cash request of TZS {{amount}} for "{{description}}" was NOT approved. Please see your manager for details. — {{company}}',
 'Cash Request'),
('Cash request paid',
 'Hi {{name}}, TZS {{amount}} has been disbursed for your request "{{description}}". Please collect from accounts. — {{company}}',
 'Cash Request'),
('Invoice reminder',
 'Dear {{name}}, this is a reminder that invoice {{invoice_no}} of TZS {{amount}} is due on {{due_date}}. Kindly settle at your earliest convenience. — {{company}}',
 'Invoice'),
('Attendance reminder',
 'Hi {{name}}, we noticed you have not checked in today. Please open the Employee Portal to check in. — {{company}}',
 'Attendance'),
('Generic broadcast',
 '{{message}} — {{company}}',
 'General'),
('Birthday wish',
 'Happy Birthday, {{name}}! Wishing you a fantastic year ahead full of success, good health and happiness. With love from the entire {{company}} team.',
 'Greetings'),
('Public holiday greeting',
 'Happy {{holiday}}, {{name}}! The {{company}} family wishes you and your loved ones a restful and blessed holiday.',
 'Greetings'),
('Work anniversary',
 'Happy work anniversary, {{name}}! Today marks {{years}} year(s) with {{company}}. Thank you for your dedication and all you contribute. Here is to many more!',
 'Greetings'),
('Monthly attendance summary',
 'Hi {{name}}, your {{month}} attendance: Present {{present}}, Late {{late}}, Absent {{absent}}, Leave {{leave}}, Half-day {{halfday}}. Worked {{worked}}/{{workdays}} days. - {{company}}',
 'Attendance'),
('Petty cash request submitted',
 'New cash request: {{employee}} asks TZS {{amount}} for "{{description}}". Please review in Netpoa. - {{company}}',
 'Cash Request'),
('Login code',
 'Your {{company}} login code is {{code}}. It expires in 5 minutes. Do not share this code.',
 'Security');

-- 2FA one-time passwords (v2.19)
CREATE TABLE IF NOT EXISTS user_otps (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    code_hash  VARCHAR(100) NOT NULL,
    purpose    VARCHAR(30)  NOT NULL DEFAULT 'login_2fa',
    attempts   TINYINT      NOT NULL DEFAULT 0,
    used_at    DATETIME     NULL,
    expires_at DATETIME     NOT NULL,
    created_at DATETIME     DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45)  NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_expires (expires_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===========================================================================
-- COMPLIANCE (v2.20) — obligation catalog + per-period filings
-- ===========================================================================

CREATE TABLE IF NOT EXISTS compliance_obligations (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    code               VARCHAR(40)  NOT NULL UNIQUE,
    name               VARCHAR(120) NOT NULL,
    authority          VARCHAR(60)  NOT NULL,
    frequency          VARCHAR(20)  NOT NULL,
    due_day            TINYINT      NULL,
    due_month_offset   TINYINT      NOT NULL DEFAULT 1,
    annual_due_month   TINYINT      NULL,
    annual_due_day     TINYINT      NULL,
    reminder_lead_days TINYINT      NOT NULL DEFAULT 7,
    notes              TEXT         NULL,
    is_enabled         TINYINT(1)   NOT NULL DEFAULT 1,
    is_system          TINYINT(1)   NOT NULL DEFAULT 1,
    created_at         DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at         DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS compliance_filings (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    obligation_id    INT          NOT NULL,
    period_label     VARCHAR(20)  NOT NULL,
    period_start     DATE         NOT NULL,
    period_end       DATE         NOT NULL,
    due_date         DATE         NOT NULL,
    status           VARCHAR(20)  NOT NULL DEFAULT 'upcoming',
    amount_due       DECIMAL(14,2) NULL,
    amount_paid      DECIMAL(14,2) NULL,
    filed_date       DATE         NULL,
    paid_date        DATE         NULL,
    reference_number VARCHAR(100) NULL,
    receipt_path     VARCHAR(255) NULL,
    notes            TEXT         NULL,
    last_reminded_at DATETIME     NULL,
    created_at       DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_obligation_period (obligation_id, period_label),
    INDEX idx_cf_due_date (due_date),
    INDEX idx_cf_status (status),
    FOREIGN KEY (obligation_id) REFERENCES compliance_obligations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO compliance_obligations
  (code, name, authority, frequency, due_day, due_month_offset, annual_due_month, annual_due_day, reminder_lead_days, notes)
VALUES
 ('vat',              'VAT Return',                    'TRA',        'monthly',  20,   1, NULL, NULL, 7,  '18% VAT. File by 20th of the month following the taxable period.'),
 ('paye',             'PAYE',                          'TRA',        'monthly',   7,   1, NULL, NULL, 5,  'Pay As You Earn. Due 7th of month following payroll.'),
 ('sdl',              'Skills Development Levy (SDL)', 'TRA',        'monthly',   7,   1, NULL, NULL, 5,  '4% of gross payroll. Due with PAYE.'),
 ('withholding_tax',  'Withholding Tax',               'TRA',        'monthly',   7,   1, NULL, NULL, 5,  'Due 7th of month following deduction.'),
 ('nssf',             'NSSF Contribution',             'NSSF',       'monthly',  NULL, 1, NULL, NULL, 7,  '20% of gross (10% employee + 10% employer). Due end of month following payroll.'),
 ('wcf',              'Workers Compensation Fund',     'WCF',        'quarterly',NULL, 1, NULL, NULL, 10, '0.5% of gross payroll, quarterly.'),
 ('corp_tax_q1',      'Corporate Tax — Quarter 1',     'TRA',        'one-off',  NULL, 0, 3,    31,   14, 'First provisional installment for the year.'),
 ('corp_tax_q2',      'Corporate Tax — Quarter 2',     'TRA',        'one-off',  NULL, 0, 6,    30,   14, 'Second provisional installment.'),
 ('corp_tax_q3',      'Corporate Tax — Quarter 3',     'TRA',        'one-off',  NULL, 0, 9,    30,   14, 'Third provisional installment.'),
 ('corp_tax_q4',      'Corporate Tax — Quarter 4',     'TRA',        'one-off',  NULL, 0, 12,   31,   14, 'Fourth provisional installment.'),
 ('corp_tax_annual',  'Corporate Tax — Annual Return', 'TRA',        'annual',   NULL, 0, 6,    30,   30, 'Annual income tax return — 6 months after year-end.'),
 ('brela_annual',     'BRELA Annual Return',           'BRELA',      'annual',   NULL, 0, 1,    31,   30, 'Annual return of company — filed with BRELA.'),
 ('business_license', 'Business License Renewal',      'Local Govt', 'annual',   NULL, 0, 1,    31,   30, 'Renew municipal/city council business license.'),
 ('osha',             'OSHA Registration Renewal',     'OSHA',       'annual',   NULL, 0, 1,    31,   30, 'Occupational Safety & Health compliance.'),
 ('fire',             'Fire & Rescue Inspection',      'Fire',       'annual',   NULL, 0, 1,    31,   30, 'Annual fire safety inspection certificate.'),
 ('tcra',             'TCRA License',                  'TCRA',       'annual',   NULL, 0, 1,    31,   30, 'If the business is in telecom/ICT. Disable if not applicable.');

-- TCRA off by default (most SMEs don't need it — tenant admin can enable)
UPDATE compliance_obligations SET is_enabled = 0 WHERE code = 'tcra';

INSERT IGNORE INTO sms_templates (name, body, category) VALUES
('Compliance reminder',
 '{{obligation}} for {{period}} is due on {{due_date}} ({{days_left}} days). Please file/pay before the deadline. - {{company}}',
 'Compliance'),
('Compliance overdue',
 'OVERDUE: {{obligation}} for {{period}} was due on {{due_date}} ({{days_over}} days overdue). Please file/pay immediately. - {{company}}',
 'Compliance');

-- ===========================================================================
-- SETTINGS: everything added v2.14 through v2.20
-- ===========================================================================

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
-- v2.14 — attendance defaults
('work_start_time',                '08:00'),
('late_threshold_min',             '15'),
('halfday_threshold_min',          '90'),
('office_lat',                     ''),
('office_lng',                     ''),
('office_radius_m',                '200'),
('require_remote_summary',         '1'),
('attendance_enabled',             '1'),
-- v2.15 — Beem SMS credentials + master toggles
('beem_api_key',                   ''),
('beem_secret_key',                ''),
('beem_sender_id',                 'INFO'),
('sms_enabled',                    '0'),
('sms_on_payroll_paid',            '0'),
('sms_on_request_approved',        '0'),
('sms_on_request_rejected',        '0'),
('sms_on_request_paid',            '0'),
('sms_on_invoice_reminder',        '0'),
-- v2.17 — birthday / holiday
('sms_on_birthday',                '0'),
('sms_on_holiday',                 '0'),
('sms_greetings_last_run',         ''),
-- v2.18 — anniversary / monthly summary / invoice schedule
('sms_on_anniversary',             '0'),
('sms_on_monthly_attendance',      '0'),
('sms_invoice_reminder_days',      '3'),
('sms_last_attendance_run',        ''),
('sms_last_invoice_run',           ''),
-- v2.19 — request-submitted + 2FA
('sms_on_request_submitted',       '0'),
('twofa_required_for_admin',       '0'),
-- v2.20 — compliance
('compliance_reminders_enabled',   '1'),
('compliance_reminder_lead_days',  '7'),
('compliance_overdue_reminder_days','3'),
('compliance_last_run',            ''),
-- Employee number prefix (used by save_employee auto-gen)
('employee_prefix',                'EMP');

-- payslip_token_secret: generate one per tenant if absent (keeps each tenant's
-- payslip HMAC distinct). UUID() is fine for secret entropy here.
INSERT IGNORE INTO settings (setting_key, setting_value)
VALUES ('payslip_token_secret', REPLACE(UUID(),'-',''));

-- ===========================================================================
-- AUDIT LOG composite indexes (v2.21) — record-history + activity log pages
-- ===========================================================================
-- Skipped the procedure-based guards since these are idempotent via CREATE
-- INDEX IF NOT EXISTS (MySQL 8.0.29+ / MariaDB 10.5.4+). Falls back to guards
-- if that syntax isn't supported on the target — wrap each in INFORMATION_SCHEMA
-- check.

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='audit_log' AND INDEX_NAME='idx_audit_table_record');
SET @s := IF(@x=0,
    'CREATE INDEX idx_audit_table_record ON audit_log (table_name, record_id, created_at)',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='audit_log' AND INDEX_NAME='idx_audit_user_time');
SET @s := IF(@x=0,
    'CREATE INDEX idx_audit_user_time ON audit_log (user_id, created_at)',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
           WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='audit_log' AND INDEX_NAME='idx_audit_created_at');
SET @s := IF(@x=0,
    'CREATE INDEX idx_audit_created_at ON audit_log (created_at)',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
