-- ============================================================================ -- 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;