-- Tenant schema sync: apply all HRapp tables missing from tenant DBs
-- Run against each tenant: starz_us, starz_tn, starz_ma
-- Usage: docker exec -i crm-v2-mysql mysql -uroot DB_NAME < sync_tenant_schema.sql

SET FOREIGN_KEY_CHECKS=0;

-- 1. customer_credit_profile
CREATE TABLE IF NOT EXISTS `customer_credit_profile` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `hold_placed_by_id` int DEFAULT NULL,
  `credit_limit` decimal(15,2) NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_on_credit_hold` tinyint(1) NOT NULL,
  `hold_reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `hold_placed_at` datetime DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
  `payment_terms_days` int NOT NULL,
  `current_balance` decimal(15,2) NOT NULL,
  `available_credit` decimal(15,2) NOT NULL,
  `days_overdue` int NOT NULL,
  `created_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_22BEF9EA9395C3F3` (`customer_id`),
  KEY `IDX_22BEF9EA451F1D7` (`hold_placed_by_id`),
  CONSTRAINT `FK_22BEF9EA451F1D7` FOREIGN KEY (`hold_placed_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_22BEF9EA9395C3F3` FOREIGN KEY (`customer_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. payment_receipt
CREATE TABLE IF NOT EXISTS `payment_receipt` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `fiscal_profile_id` int NOT NULL,
  `received_by_id` int NOT NULL,
  `deposited_to_id` int DEFAULT NULL,
  `received_at` datetime NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `check_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  `allocated_amount` decimal(15,2) NOT NULL,
  `unallocated_amount` decimal(15,2) NOT NULL,
  `created_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_4AD0B8AF2492F9BB` (`fiscal_profile_id`),
  KEY `IDX_4AD0B8AF6F8DDD17` (`received_by_id`),
  KEY `IDX_4AD0B8AFFC9D053C` (`deposited_to_id`),
  KEY `IDX_payment_receipt_customer` (`customer_id`),
  KEY `IDX_payment_receipt_date` (`received_at`),
  KEY `IDX_payment_receipt_reference` (`reference`),
  CONSTRAINT `FK_4AD0B8AF2492F9BB` FOREIGN KEY (`fiscal_profile_id`) REFERENCES `fiscal_profile` (`id`),
  CONSTRAINT `FK_4AD0B8AF6F8DDD17` FOREIGN KEY (`received_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_4AD0B8AF9395C3F3` FOREIGN KEY (`customer_id`) REFERENCES `company` (`id`),
  CONSTRAINT `FK_4AD0B8AFFC9D053C` FOREIGN KEY (`deposited_to_id`) REFERENCES `bank_account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. sales_order
CREATE TABLE IF NOT EXISTS `sales_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `fiscal_profile_id` int NOT NULL,
  `approved_by_id` int DEFAULT NULL,
  `released_by_id` int DEFAULT NULL,
  `source_quotation_id` int DEFAULT NULL,
  `payment_terms_id` int DEFAULT NULL,
  `created_by_id` int NOT NULL,
  `so_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `approved_at` datetime DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
  `released_at` datetime DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
  `payment_terms_days` int DEFAULT NULL,
  `requested_delivery_date` date DEFAULT NULL,
  `customer_ponumber` longtext COLLATE utf8mb4_unicode_ci,
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  `metadata_json` json DEFAULT NULL,
  `subtotal` decimal(15,4) NOT NULL,
  `tax_total` decimal(15,4) NOT NULL,
  `grand_total` decimal(15,4) NOT NULL,
  `created_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_36D222ED4237E00` (`so_number`),
  KEY `IDX_36D222E2D234F6A` (`approved_by_id`),
  KEY `IDX_36D222E445947DD` (`released_by_id`),
  KEY `IDX_36D222E620092D8` (`source_quotation_id`),
  KEY `IDX_36D222E13B26D4F` (`payment_terms_id`),
  KEY `IDX_36D222EB03A8386` (`created_by_id`),
  KEY `IDX_sales_order_number` (`so_number`),
  KEY `IDX_sales_order_status` (`status`),
  KEY `IDX_sales_order_customer` (`customer_id`),
  KEY `IDX_sales_order_fiscal` (`fiscal_profile_id`),
  CONSTRAINT `FK_36D222E13B26D4F` FOREIGN KEY (`payment_terms_id`) REFERENCES `pay_term` (`id`),
  CONSTRAINT `FK_36D222E2492F9BB` FOREIGN KEY (`fiscal_profile_id`) REFERENCES `fiscal_profile` (`id`),
  CONSTRAINT `FK_36D222E2D234F6A` FOREIGN KEY (`approved_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_36D222E445947DD` FOREIGN KEY (`released_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_36D222E620092D8` FOREIGN KEY (`source_quotation_id`) REFERENCES `quotation` (`id`),
  CONSTRAINT `FK_36D222E9395C3F3` FOREIGN KEY (`customer_id`) REFERENCES `company` (`id`),
  CONSTRAINT `FK_36D222EB03A8386` FOREIGN KEY (`created_by_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. sales_order_line
CREATE TABLE IF NOT EXISTS `sales_order_line` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sales_order_id` int NOT NULL,
  `article_id` int DEFAULT NULL,
  `sku` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(15,4) NOT NULL,
  `unit_price` decimal(15,4) NOT NULL,
  `discount_percent` decimal(15,4) DEFAULT NULL,
  `discount_amount` decimal(15,4) DEFAULT NULL,
  `tax_rate` decimal(15,4) DEFAULT NULL,
  `tax_amount` decimal(15,4) DEFAULT NULL,
  `line_total` decimal(15,4) DEFAULT NULL,
  `line_number` int DEFAULT NULL,
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `IDX_93D9398DC023F51C` (`sales_order_id`),
  KEY `IDX_93D9398D7294869C` (`article_id`),
  CONSTRAINT `FK_93D9398D7294869C` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`),
  CONSTRAINT `FK_93D9398DC023F51C` FOREIGN KEY (`sales_order_id`) REFERENCES `sales_order` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. supplier_invoice
CREATE TABLE IF NOT EXISTS `supplier_invoice` (
  `id` int NOT NULL AUTO_INCREMENT,
  `supplier_id` int NOT NULL,
  `fiscal_profile_id` int NOT NULL,
  `purchase_order_id` int DEFAULT NULL,
  `approved_by_id` int DEFAULT NULL,
  `posted_by_id` int DEFAULT NULL,
  `paid_by_id` int DEFAULT NULL,
  `created_by_id` int NOT NULL,
  `supplier_invoice_number` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `memo` longtext COLLATE utf8mb4_unicode_ci,
  `subtotal` decimal(15,4) NOT NULL,
  `tax_total` decimal(15,4) NOT NULL,
  `grand_total` decimal(15,4) NOT NULL,
  `amount_paid` decimal(15,4) NOT NULL,
  `amount_due` decimal(15,4) NOT NULL,
  `approved_at` datetime DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
  `posted_at` datetime DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
  `paid_at` datetime DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
  `metadata_json` json DEFAULT NULL,
  `created_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_1100635B2492F9BB` (`fiscal_profile_id`),
  KEY `IDX_1100635BA45D7E6A` (`purchase_order_id`),
  KEY `IDX_1100635B2D234F6A` (`approved_by_id`),
  KEY `IDX_1100635B5A6D2235` (`posted_by_id`),
  KEY `IDX_1100635B7F9BC654` (`paid_by_id`),
  KEY `IDX_1100635BB03A8386` (`created_by_id`),
  KEY `IDX_supplier_invoice_number` (`supplier_invoice_number`),
  KEY `IDX_supplier_invoice_supplier` (`supplier_id`),
  KEY `IDX_supplier_invoice_status` (`status`),
  KEY `IDX_supplier_invoice_due_date` (`due_date`),
  CONSTRAINT `FK_1100635B2492F9BB` FOREIGN KEY (`fiscal_profile_id`) REFERENCES `fiscal_profile` (`id`),
  CONSTRAINT `FK_1100635B2ADD6D8C` FOREIGN KEY (`supplier_id`) REFERENCES `supplier_info` (`id`),
  CONSTRAINT `FK_1100635B2D234F6A` FOREIGN KEY (`approved_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_1100635B5A6D2235` FOREIGN KEY (`posted_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_1100635B7F9BC654` FOREIGN KEY (`paid_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_1100635BA45D7E6A` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_order` (`id`),
  CONSTRAINT `FK_1100635BB03A8386` FOREIGN KEY (`created_by_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. supplier_invoice_line
CREATE TABLE IF NOT EXISTS `supplier_invoice_line` (
  `id` int NOT NULL AUTO_INCREMENT,
  `supplier_invoice_id` int NOT NULL,
  `article_id` int DEFAULT NULL,
  `sku` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(15,4) NOT NULL,
  `unit_price` decimal(15,4) NOT NULL,
  `tax_amount` decimal(15,4) DEFAULT NULL,
  `line_number` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_B9B841CA48783C14` (`supplier_invoice_id`),
  KEY `IDX_B9B841CA7294869C` (`article_id`),
  CONSTRAINT `FK_B9B841CA48783C14` FOREIGN KEY (`supplier_invoice_id`) REFERENCES `supplier_invoice` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_B9B841CA7294869C` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. invoice_dispute
CREATE TABLE IF NOT EXISTS `invoice_dispute` (
  `id` int NOT NULL AUTO_INCREMENT,
  `invoice_id` int NOT NULL,
  `opened_by_id` int NOT NULL,
  `resolved_by_id` int DEFAULT NULL,
  `opened_at` datetime NOT NULL,
  `reason` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `resolution` longtext COLLATE utf8mb4_unicode_ci,
  `disputed_amount` decimal(15,2) DEFAULT NULL,
  `adjustment_amount` decimal(15,2) DEFAULT NULL,
  `internal_notes` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `IDX_CE4B66D4AB159F5` (`opened_by_id`),
  KEY `IDX_CE4B66D46713A32B` (`resolved_by_id`),
  KEY `IDX_dispute_invoice` (`invoice_id`),
  KEY `IDX_dispute_status` (`status`),
  CONSTRAINT `FK_CE4B66D42989F1FD` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`),
  CONSTRAINT `FK_CE4B66D46713A32B` FOREIGN KEY (`resolved_by_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_CE4B66D4AB159F5` FOREIGN KEY (`opened_by_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. payment_allocation
CREATE TABLE IF NOT EXISTS `payment_allocation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `payment_receipt_id` int NOT NULL,
  `invoice_id` int NOT NULL,
  `allocated_by_id` int NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `allocated_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `IDX_69B2A2FF6802B588` (`allocated_by_id`),
  KEY `IDX_payment_alloc_receipt` (`payment_receipt_id`),
  KEY `IDX_payment_alloc_invoice` (`invoice_id`),
  CONSTRAINT `FK_69B2A2FF1EAA2943` FOREIGN KEY (`payment_receipt_id`) REFERENCES `payment_receipt` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_69B2A2FF2989F1FD` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`),
  CONSTRAINT `FK_69B2A2FF6802B588` FOREIGN KEY (`allocated_by_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Add sales_order_id to invoice (if missing)
ALTER TABLE `invoice` ADD COLUMN IF NOT EXISTS `sales_order_id` int DEFAULT NULL;

-- 10. Add sales_order_id to shipment (if missing)
ALTER TABLE `shipment` ADD COLUMN IF NOT EXISTS `sales_order_id` int DEFAULT NULL;

-- NOTE: For missing employee_salary columns and payroll tables (pay_period,
-- payroll_run, payroll_payment), use bin/fix_all_databases.php which uses
-- PHP PDO with proper existence checks (MySQL does not support ADD COLUMN IF NOT EXISTS).

SET FOREIGN_KEY_CHECKS=1;
