-- Create the hmrc_bsas table
CREATE TABLE `hmrc_bsas` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `nino` VARCHAR(255) NOT NULL,
  `tax_year` VARCHAR(255) NOT NULL,
  `calculation_id` VARCHAR(255) NULL COMMENT 'HMRC\'s calculation ID',
  `business_id` VARCHAR(255) NULL COMMENT 'Business ID (self-employment or property)',
  `type_of_business` VARCHAR(255) NULL COMMENT 'self-employment, uk-property-fhl, uk-property-non-fhl, foreign-property-fhl-eea, foreign-property',
  `request_source` VARCHAR(255) NULL COMMENT 'MTD-SA, HMRC-HELD, etc.',
  
  -- Summary totals
  `total_income` DECIMAL(13, 2) NULL,
  `total_expenses` DECIMAL(13, 2) NULL,
  `total_additions` DECIMAL(13, 2) NULL,
  `total_deductions` DECIMAL(13, 2) NULL,
  `net_profit` DECIMAL(13, 2) NULL,
  `net_loss` DECIMAL(13, 2) NULL,
  `total_bsas_adjustments` DECIMAL(13, 2) NULL,
  `adjusted_summary_profit` DECIMAL(13, 2) NULL,
  `adjusted_summary_loss` DECIMAL(13, 2) NULL,
  
  -- Metadata
  `summary_timestamp` DATETIME NULL,
  `summary_data` TEXT NULL COMMENT 'JSON store for full summary details',
  `adjustments_data` TEXT NULL COMMENT 'JSON store for adjustments',
  `metadata` TEXT NULL COMMENT 'JSON store for additional metadata',
  `adjusted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Has adjustments been submitted',
  
  -- Standard Laravel columns
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  
  -- Foreign key constraint
  CONSTRAINT `hmrc_bsas_user_id_foreign` 
    FOREIGN KEY (`user_id`) 
    REFERENCES `user` (`id`)
    ON DELETE RESTRICT 
    ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add indexes for better query performance (optional but recommended)
CREATE INDEX `hmrc_bsas_user_id_index` ON `hmrc_bsas` (`user_id`);
CREATE INDEX `hmrc_bsas_calculation_id_index` ON `hmrc_bsas` (`calculation_id`);
CREATE INDEX `hmrc_bsas_tax_year_index` ON `hmrc_bsas` (`tax_year`);
CREATE INDEX `hmrc_bsas_business_id_index` ON `hmrc_bsas` (`business_id`);
CREATE INDEX `hmrc_bsas_type_of_business_index` ON `hmrc_bsas` (`type_of_business`);
CREATE INDEX `hmrc_bsas_deleted_at_index` ON `hmrc_bsas` (`deleted_at`);