-- ============================================================================
-- HMRC Individual Calculations - Complete Database Schema
-- Created: 2025-12-04
-- Description: Consolidated SQL file for all HMRC calculation tables
-- This file contains all migrations for the normalized calculation schema
-- ============================================================================

-- Table 1: Income Tax Details
CREATE TABLE IF NOT EXISTS `hmrc_calculation_income_tax` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    -- Income totals
    `total_income_received` DECIMAL(13, 2) NULL,
    `total_allowances_and_deductions` DECIMAL(13, 2) NULL,
    `total_taxable_income` DECIMAL(13, 2) NULL,

    -- Income tax amounts
    `total_income_tax_due` DECIMAL(13, 2) NULL,
    `income_tax_due_after_reliefs` DECIMAL(13, 2) NULL,
    `total_reliefs` DECIMAL(13, 2) NULL,
    `total_notional_tax` DECIMAL(13, 2) NULL,
    `income_tax_due_after_gift_aid` DECIMAL(13, 2) NULL,
    `total_pension_savings_tax_charges` DECIMAL(13, 2) NULL,
    `state_pension_lump_sum_charges` DECIMAL(13, 2) NULL,
    `total_income_tax_and_nics_due` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_income_tax_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 2: Pay, Pensions, and Profit
CREATE TABLE IF NOT EXISTS `hmrc_calculation_pay_pensions_profit` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `total_self_employment_profit` DECIMAL(13, 2) NULL,
    `total_property_profit` DECIMAL(13, 2) NULL,
    `income_tax_amount` DECIMAL(13, 2) NULL,
    `taxable_income` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_pay_pensions_profit_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 3: Tax Bands
CREATE TABLE IF NOT EXISTS `hmrc_calculation_tax_bands` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL,
    `income_type` ENUM('pay_pensions_profit', 'savings_and_gains', 'dividends', 'lump_sums') NOT NULL,

    `band_name` VARCHAR(100) NULL,
    `rate` DECIMAL(5, 2) NULL,
    `band_limit` INT NULL,
    `apportioned_band_limit` INT NULL,
    `income` DECIMAL(13, 2) NULL,
    `taxable_income` DECIMAL(13, 2) NULL,
    `tax_amount` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_tax_bands_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE,

    INDEX `idx_calculation_income_type` (`calculation_id`, `income_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 4: Savings and Gains
CREATE TABLE IF NOT EXISTS `hmrc_calculation_savings_and_gains` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `income_tax_amount` DECIMAL(13, 2) NULL,
    `taxable_income` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_savings_gains_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 5: Dividends
CREATE TABLE IF NOT EXISTS `hmrc_calculation_dividends` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `income_tax_amount` DECIMAL(13, 2) NULL,
    `taxable_income` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_dividends_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 6: National Insurance Contributions
CREATE TABLE IF NOT EXISTS `hmrc_calculation_nics` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    -- Class 2 NICs
    `class2_amount` DECIMAL(13, 2) NULL,
    `class2_week_rate` DECIMAL(13, 2) NULL,
    `class2_weeks` INT NULL,
    `class2_limit` DECIMAL(13, 2) NULL,
    `class2_apportioned_limit` DECIMAL(13, 2) NULL,

    -- Class 4 NICs
    `class4_total_amount` DECIMAL(13, 2) NULL,

    -- Total NICs
    `total_nic` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_nics_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 7: Class 4 NIC Bands
CREATE TABLE IF NOT EXISTS `hmrc_calculation_class4_bands` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL,

    `band_name` VARCHAR(100) NULL,
    `rate` DECIMAL(5, 2) NULL,
    `band_limit` INT NULL,
    `apportioned_band_limit` INT NULL,
    `income` DECIMAL(13, 2) NULL,
    `amount` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_class4_bands_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE,

    INDEX `idx_class4_calculation` (`calculation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 8: Capital Gains Tax
CREATE TABLE IF NOT EXISTS `hmrc_calculation_capital_gains_tax` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `total_taxable_gains` DECIMAL(13, 2) NULL,
    `adjustments` DECIMAL(13, 2) NULL,
    `foreign_tax_credit_relief` DECIMAL(13, 2) NULL,
    `tax_on_gains_already_paid` DECIMAL(13, 2) NULL,
    `capital_gains_tax_due` DECIMAL(13, 2) NULL,
    `capital_gains_overpaid` DECIMAL(13, 2) NULL,

    -- Residential property and carried interest
    `residential_property_gains` DECIMAL(13, 2) NULL,
    `residential_property_and_carried_interest_gains` DECIMAL(13, 2) NULL,
    `residential_property_and_carried_interest_tax` DECIMAL(13, 2) NULL,

    -- Other gains
    `other_gains_amount` DECIMAL(13, 2) NULL,
    `other_gains_tax` DECIMAL(13, 2) NULL,

    -- Business assets disposal relief
    `badr_gains` DECIMAL(13, 2) NULL,
    `badr_rate` DECIMAL(5, 2) NULL,
    `badr_tax` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_cgt_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 9: Allowances and Deductions
CREATE TABLE IF NOT EXISTS `hmrc_calculation_allowances_and_deductions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `personal_allowance` DECIMAL(13, 2) NULL,
    `reduced_personal_allowance` DECIMAL(13, 2) NULL,
    `marriage_allowance_transfer_out_amount` DECIMAL(13, 2) NULL,
    `pension_contributions` DECIMAL(13, 2) NULL,
    `losses_applied_to_general_income` DECIMAL(13, 2) NULL,
    `gift_of_investments_and_property_to_charity` DECIMAL(13, 2) NULL,
    `gross_annuity_payments` DECIMAL(13, 2) NULL,
    `qualifying_loan_interest_from_investments` DECIMAL(13, 2) NULL,
    `post_cessation_trade_receipts` DECIMAL(13, 2) NULL,
    `payments_to_trade_unions_for_death_benefits` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_allowances_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 10: Reliefs
CREATE TABLE IF NOT EXISTS `hmrc_calculation_reliefs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL,

    `relief_type` VARCHAR(100) NULL,
    `amount` DECIMAL(13, 2) NULL,

    -- Residential finance costs (specific fields)
    `total_residential_finance_costs_relief` DECIMAL(13, 2) NULL,
    `relievable_residential_finance_costs` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_reliefs_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE,

    INDEX `idx_reliefs_calculation` (`calculation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 11: Tax Deducted at Source
CREATE TABLE IF NOT EXISTS `hmrc_calculation_tax_deducted_at_source` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `paye_employments` DECIMAL(13, 2) NULL,
    `occupational_pensions` DECIMAL(13, 2) NULL,
    `state_benefits` DECIMAL(13, 2) NULL,
    `cis` DECIMAL(13, 2) NULL,
    `uk_land_and_property` DECIMAL(13, 2) NULL,
    `special_withholding_tax_or_uk_tax_paid` DECIMAL(13, 2) NULL,
    `voided_isa` DECIMAL(13, 2) NULL,
    `savings_and_gains_income` DECIMAL(13, 2) NULL,
    `total_tax_deducted_at_source` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_tax_deducted_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 12: Pension Savings Tax Charges
CREATE TABLE IF NOT EXISTS `hmrc_calculation_pension_savings_tax_charges` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `total_pension_charges` DECIMAL(13, 2) NULL,
    `total_tax_paid` DECIMAL(13, 2) NULL,
    `total_pension_charges_due` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_pension_charges_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 13: Student Loans
CREATE TABLE IF NOT EXISTS `hmrc_calculation_student_loans` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL,

    `plan_type` VARCHAR(50) NULL,
    `amount` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_student_loans_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE,

    INDEX `idx_student_loans_calculation` (`calculation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 14: Calculation Totals
CREATE TABLE IF NOT EXISTS `hmrc_calculation_totals` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `total_income_tax_and_nics_due` DECIMAL(13, 2) NULL,
    `total_income_tax_and_nics_and_cgt_due` DECIMAL(13, 2) NULL,
    `total_student_loans_repayment_amount` DECIMAL(13, 2) NULL,
    `total_annuity_payments_tax_charged` DECIMAL(13, 2) NULL,
    `total_royalty_payments_tax_charged` DECIMAL(13, 2) NULL,
    `total_tax_deducted` DECIMAL(13, 2) NULL,
    `total_income_tax_nics_and_cgt_due` DECIMAL(13, 2) NULL,

    -- Calculated fields
    `balance_due` DECIMAL(13, 2) NULL,
    `payments_made` DECIMAL(13, 2) NULL,
    `amount_owed` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_totals_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 15: End of Year Estimate
CREATE TABLE IF NOT EXISTS `hmrc_calculation_end_of_year_estimate` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL UNIQUE,

    `total_estimated_income` DECIMAL(13, 2) NULL,
    `total_taxable_income` DECIMAL(13, 2) NULL,
    `income_tax_amount` DECIMAL(13, 2) NULL,
    `nic2` DECIMAL(13, 2) NULL,
    `nic4` DECIMAL(13, 2) NULL,
    `total_nic_amount` DECIMAL(13, 2) NULL,
    `total_tax_deducted_before_coding_out` DECIMAL(13, 2) NULL,
    `sa_underpayments_coded_out` DECIMAL(13, 2) NULL,
    `total_student_loans_repayment_amount` DECIMAL(13, 2) NULL,
    `total_annuity_payments_tax_charged` DECIMAL(13, 2) NULL,
    `total_royalty_payments_tax_charged` DECIMAL(13, 2) NULL,
    `total_income_tax_and_nics_due` DECIMAL(13, 2) NULL,
    `total_income_tax_nics_and_cgt_due` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_eoy_estimate_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 16: End of Year Estimate Income Sources
CREATE TABLE IF NOT EXISTS `hmrc_calculation_end_of_year_estimate_income_sources` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `calculation_id` INT UNSIGNED NOT NULL,

    `income_source_id` VARCHAR(255) NULL,
    `income_source_name` VARCHAR(255) NULL,
    `taxable_income` DECIMAL(13, 2) NULL,

    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    CONSTRAINT `fk_eoy_income_sources_calculation`
        FOREIGN KEY (`calculation_id`)
        REFERENCES `hmrc_individual_calculations` (`id`)
        ON DELETE CASCADE,

    INDEX `idx_eoy_income_sources_calculation` (`calculation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update main calculations table with calculation_reason field
ALTER TABLE `hmrc_individual_calculations`
ADD COLUMN `calculation_reason` VARCHAR(255) NULL AFTER `calculation_timestamp`;

-- ============================================================================
-- End of HMRC Individual Calculations Schema
-- ============================================================================