-- CostusWorx © 2026 — Feature 6B: Tax Brackets
-- SARS personal income tax tables, with per-tenant override support.
--
-- Column-name authority: tax_brackets_2026.sql + SarsTaxCalculator.php
--   threshold_start / threshold_end / tenant_id
--
-- tenant_id = 0  →  SARS global defaults (managed via super-admin).
-- tenant_id > 0  →  Per-tenant override rows (rare; for niche configurations).
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS.
-- If you previously ran an older version of this file that used bracket_from /
-- bracket_to columns, run database/fix_tax_brackets_rename.sql first.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS tax_brackets (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT NOT NULL               DEFAULT 0
                        COMMENT '0 = SARS global defaults; tenant.id for per-tenant override',
    tax_year        INT NOT NULL               COMMENT 'e.g. 2026 = 2025/26 tax year',
    threshold_start DECIMAL(14,2) NOT NULL     COMMENT 'Lower bound of this bracket (ZAR)',
    threshold_end   DECIMAL(14,2) DEFAULT NULL COMMENT 'Upper bound; NULL = open-ended top bracket',
    base_tax        DECIMAL(14,2) NOT NULL     COMMENT 'Fixed tax at bottom of bracket',
    marginal_rate   DECIMAL(6,4)  NOT NULL     COMMENT 'e.g. 0.4100 = 41%',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_bracket (tenant_id, tax_year, threshold_start),
    INDEX tax_year_idx (tax_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tax rebates
CREATE TABLE IF NOT EXISTS tax_rebates (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tax_year        YEAR          NOT NULL,
    rebate_type     ENUM('primary','secondary','tertiary') NOT NULL,
    amount          DECIMAL(10,2) NOT NULL,
    age_threshold   TINYINT UNSIGNED DEFAULT NULL COMMENT 'secondary: 65+, tertiary: 75+',
    INDEX idx_year (tax_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Medical Tax Credits
CREATE TABLE IF NOT EXISTS medical_tax_credits (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tax_year        YEAR          NOT NULL,
    credit_per_main DECIMAL(8,2)  NOT NULL COMMENT 'Per main member per month',
    credit_per_dep  DECIMAL(8,2)  NOT NULL COMMENT 'Per dependant per month',
    INDEX idx_year (tax_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;