-- Step 42: Generic tax configuration and immutable accounting foundation
-- Additive only: this migration does not activate a tax profile or alter current totals.

CREATE TABLE IF NOT EXISTS `businessEntities` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `displayName` varchar(100) NOT NULL,
  `legalName` varchar(150) NOT NULL,
  `companyRegistrationNo` varchar(50) DEFAULT NULL,
  `addressLine1` varchar(150) DEFAULT NULL,
  `addressLine2` varchar(150) DEFAULT NULL,
  `city` varchar(80) DEFAULT NULL,
  `postalCode` varchar(20) DEFAULT NULL,
  `countryCode` char(2) NOT NULL,
  `currencyCode` char(3) NOT NULL,
  `timezone` varchar(50) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_businessEntities_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vendorBusinessEntityAssignments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `businessEntityId` bigint unsigned NOT NULL,
  `effectiveFrom` datetime NOT NULL,
  `effectiveUntil` datetime DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vbea_vendor_effective` (`vendorId`,`effectiveFrom`,`effectiveUntil`),
  KEY `idx_vbea_entity` (`businessEntityId`),
  CONSTRAINT `fk_vbea_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vbea_entity` FOREIGN KEY (`businessEntityId`) REFERENCES `businessEntities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `taxRegistrations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `businessEntityId` bigint unsigned NOT NULL,
  `jurisdictionCode` varchar(20) NOT NULL,
  `taxSchemeCode` varchar(40) NOT NULL,
  `registrationNo` varchar(60) DEFAULT NULL,
  `serviceCategoryCode` varchar(50) DEFAULT NULL,
  `registrationScope` enum('entity','branch','vendor') NOT NULL DEFAULT 'entity',
  `accountingBasis` enum('payment','invoice') NOT NULL DEFAULT 'payment',
  `approvalReference` varchar(100) DEFAULT NULL,
  `effectiveFrom` datetime NOT NULL,
  `effectiveUntil` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 0,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_taxRegistrations_entity_effective` (`businessEntityId`,`effectiveFrom`,`effectiveUntil`),
  KEY `idx_taxRegistrations_scheme_active` (`jurisdictionCode`,`taxSchemeCode`,`active`),
  CONSTRAINT `fk_taxRegistrations_entity` FOREIGN KEY (`businessEntityId`) REFERENCES `businessEntities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `taxCodes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `businessEntityId` bigint unsigned DEFAULT NULL,
  `code` varchar(40) NOT NULL,
  `name` varchar(100) NOT NULL,
  `reportingCategory` varchar(60) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_taxCodes_entity_code` (`businessEntityId`,`code`),
  KEY `idx_taxCodes_active` (`active`),
  CONSTRAINT `fk_taxCodes_entity` FOREIGN KEY (`businessEntityId`) REFERENCES `businessEntities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `taxCodeVersions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `taxCodeId` bigint unsigned NOT NULL,
  `taxRegistrationId` bigint unsigned NOT NULL,
  `rateBasisPoints` int unsigned NOT NULL,
  `calculationMode` enum('inclusive','exclusive','zero','exempt','out_of_scope') NOT NULL,
  `roundingMode` enum('half_up') NOT NULL DEFAULT 'half_up',
  `roundingLevel` enum('line') NOT NULL DEFAULT 'line',
  `effectiveFrom` datetime NOT NULL,
  `effectiveUntil` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_taxCodeVersions_code_effective` (`taxCodeId`,`effectiveFrom`,`effectiveUntil`),
  KEY `idx_taxCodeVersions_registration` (`taxRegistrationId`),
  CONSTRAINT `fk_taxCodeVersions_code` FOREIGN KEY (`taxCodeId`) REFERENCES `taxCodes` (`id`),
  CONSTRAINT `fk_taxCodeVersions_registration` FOREIGN KEY (`taxRegistrationId`) REFERENCES `taxRegistrations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menuItemTaxAssignments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `menuItemId` int unsigned NOT NULL,
  `taxCodeId` bigint unsigned NOT NULL,
  `effectiveFrom` datetime NOT NULL,
  `effectiveUntil` datetime DEFAULT NULL,
  `changedBy` varchar(100) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mita_item_effective` (`vendorId`,`menuItemId`,`effectiveFrom`,`effectiveUntil`),
  KEY `idx_mita_taxCode` (`taxCodeId`),
  CONSTRAINT `fk_mita_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mita_taxCode` FOREIGN KEY (`taxCodeId`) REFERENCES `taxCodes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `chargeRules` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `businessEntityId` bigint unsigned NOT NULL,
  `code` varchar(40) NOT NULL,
  `name` varchar(100) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_chargeRules_entity_code` (`businessEntityId`,`code`),
  CONSTRAINT `fk_chargeRules_entity` FOREIGN KEY (`businessEntityId`) REFERENCES `businessEntities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `chargeRuleVersions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `chargeRuleId` bigint unsigned NOT NULL,
  `method` enum('percentage','fixed') NOT NULL,
  `rateBasisPoints` int unsigned DEFAULT NULL,
  `fixedAmount` decimal(10,2) DEFAULT NULL,
  `baseSelector` enum('discounted_items','selected_items','document') NOT NULL DEFAULT 'discounted_items',
  `applyToTable` tinyint(1) NOT NULL DEFAULT 1,
  `applyToTakeout` tinyint(1) NOT NULL DEFAULT 0,
  `applyToDelivery` tinyint(1) NOT NULL DEFAULT 0,
  `applyToVirtual` tinyint(1) NOT NULL DEFAULT 1,
  `taxCodeId` bigint unsigned DEFAULT NULL,
  `effectiveFrom` datetime NOT NULL,
  `effectiveUntil` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_chargeRuleVersions_effective` (`chargeRuleId`,`effectiveFrom`,`effectiveUntil`),
  KEY `idx_chargeRuleVersions_taxCode` (`taxCodeId`),
  CONSTRAINT `fk_chargeRuleVersions_rule` FOREIGN KEY (`chargeRuleId`) REFERENCES `chargeRules` (`id`),
  CONSTRAINT `fk_chargeRuleVersions_taxCode` FOREIGN KEY (`taxCodeId`) REFERENCES `taxCodes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `receiptItemFinancials` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `receiptCode` varchar(50) NOT NULL,
  `receiptItemId` int NOT NULL,
  `originalGross` decimal(10,2) NOT NULL,
  `componentGross` decimal(10,2) NOT NULL DEFAULT 0.00,
  `allocatedDiscount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `finalSellingAmount` decimal(10,2) NOT NULL,
  `serviceChargeAllocation` decimal(10,2) NOT NULL DEFAULT 0.00,
  `netTaxableAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `includedTaxAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `exclusiveTaxAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `calculationVersion` varchar(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rif_receipt_item` (`vendorId`,`receiptCode`,`receiptItemId`),
  KEY `idx_rif_receipt` (`vendorId`,`receiptCode`),
  CONSTRAINT `fk_rif_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `receiptItemTaxes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `receiptCode` varchar(50) NOT NULL,
  `receiptItemId` int NOT NULL,
  `taxCodeId` bigint unsigned DEFAULT NULL,
  `taxCodeVersionId` bigint unsigned DEFAULT NULL,
  `taxCodeSnapshot` varchar(40) NOT NULL,
  `taxNameSnapshot` varchar(100) NOT NULL,
  `reportingCategorySnapshot` varchar(60) NOT NULL,
  `rateBasisPoints` int unsigned NOT NULL,
  `calculationMode` enum('inclusive','exclusive','zero','exempt','out_of_scope') NOT NULL,
  `grossAmount` decimal(10,2) NOT NULL,
  `netTaxableAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `taxAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_rit_receipt` (`vendorId`,`receiptCode`),
  KEY `idx_rit_item` (`receiptItemId`),
  KEY `idx_rit_reporting` (`reportingCategorySnapshot`,`rateBasisPoints`),
  CONSTRAINT `fk_rit_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rit_taxCode` FOREIGN KEY (`taxCodeId`) REFERENCES `taxCodes` (`id`),
  CONSTRAINT `fk_rit_taxCodeVersion` FOREIGN KEY (`taxCodeVersionId`) REFERENCES `taxCodeVersions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `receiptTaxSummaries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `receiptCode` varchar(50) NOT NULL,
  `taxCodeVersionId` bigint unsigned DEFAULT NULL,
  `taxCodeSnapshot` varchar(40) NOT NULL,
  `taxNameSnapshot` varchar(100) NOT NULL,
  `reportingCategorySnapshot` varchar(60) NOT NULL,
  `rateBasisPoints` int unsigned NOT NULL,
  `calculationMode` enum('inclusive','exclusive','zero','exempt','out_of_scope') NOT NULL,
  `grossAmount` decimal(10,2) NOT NULL,
  `netTaxableAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `taxAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rts_receipt_tax` (`vendorId`,`receiptCode`,`taxCodeSnapshot`,`rateBasisPoints`,`calculationMode`),
  KEY `idx_rts_reporting` (`vendorId`,`reportingCategorySnapshot`,`rateBasisPoints`),
  CONSTRAINT `fk_rts_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rts_taxCodeVersion` FOREIGN KEY (`taxCodeVersionId`) REFERENCES `taxCodeVersions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `receiptPaymentFinancialAllocations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `receiptCode` varchar(50) NOT NULL,
  `receiptPaymentId` int unsigned NOT NULL,
  `receiptItemId` int DEFAULT NULL,
  `itemSellingAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `netTaxableAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `includedTaxAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `exclusiveTaxAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `serviceChargeAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `roundingAmount` decimal(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_rpfa_receipt_payment` (`vendorId`,`receiptCode`,`receiptPaymentId`),
  KEY `idx_rpfa_item` (`receiptItemId`),
  CONSTRAINT `fk_rpfa_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `accountingAuditEvents` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `businessEntityId` bigint unsigned DEFAULT NULL,
  `vendorId` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `actorType` varchar(30) NOT NULL,
  `actorId` varchar(100) DEFAULT NULL,
  `eventType` varchar(60) NOT NULL,
  `entityType` varchar(60) NOT NULL,
  `entityKey` varchar(150) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `beforeSnapshot` json DEFAULT NULL,
  `afterSnapshot` json DEFAULT NULL,
  `correlationId` varchar(100) DEFAULT NULL,
  `occurredAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_aae_vendor_time` (`vendorId`,`occurredAt`),
  KEY `idx_aae_entity_time` (`businessEntityId`,`occurredAt`),
  KEY `idx_aae_target` (`entityType`,`entityKey`),
  CONSTRAINT `fk_aae_entity` FOREIGN KEY (`businessEntityId`) REFERENCES `businessEntities` (`id`),
  CONSTRAINT `fk_aae_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Idempotently add nullable compatibility/snapshot columns to existing receipts.
DROP PROCEDURE IF EXISTS `_step42_add_receipt_column`;
DELIMITER //
CREATE PROCEDURE `_step42_add_receipt_column`(IN columnName varchar(64), IN columnDefinition text)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'receipts' AND column_name = columnName
  ) THEN
    SET @statement = CONCAT('ALTER TABLE `receipts` ADD COLUMN `', columnName, '` ', columnDefinition);
    PREPARE dynamicStatement FROM @statement;
    EXECUTE dynamicStatement;
    DEALLOCATE PREPARE dynamicStatement;
  END IF;
END//
DELIMITER ;

CALL `_step42_add_receipt_column`('businessEntityId', 'bigint unsigned DEFAULT NULL');
CALL `_step42_add_receipt_column`('taxRegistrationId', 'bigint unsigned DEFAULT NULL');
CALL `_step42_add_receipt_column`('documentType', 'enum(''sale'',''credit_note'',''debit_note'',''cancelled'') NOT NULL DEFAULT ''sale''');
CALL `_step42_add_receipt_column`('originalReceiptCode', 'varchar(50) DEFAULT NULL');
CALL `_step42_add_receipt_column`('documentReason', 'varchar(255) DEFAULT NULL');
CALL `_step42_add_receipt_column`('calculationVersion', 'varchar(40) DEFAULT NULL');
CALL `_step42_add_receipt_column`('itemSellingTotal', 'decimal(10,2) DEFAULT NULL');
CALL `_step42_add_receipt_column`('netTaxableTotal', 'decimal(10,2) DEFAULT NULL');
CALL `_step42_add_receipt_column`('includedTaxTotal', 'decimal(10,2) DEFAULT NULL');
CALL `_step42_add_receipt_column`('exclusiveTaxTotal', 'decimal(10,2) DEFAULT NULL');
CALL `_step42_add_receipt_column`('sellerIdentitySnapshot', 'json DEFAULT NULL');

DROP PROCEDURE IF EXISTS `_step42_add_receipt_column`;