-- v1.40 Sales Invoices (Draft/Posted) + Customers + VAT/Discount + Customer Ledger
-- Idempotent migration (safe to run multiple times)

SET @db := DATABASE();

SET @has_sales_invoices := (
  SELECT COUNT(*) FROM information_schema.TABLES
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices'
);

SET @has_sales_items := (
  SELECT COUNT(*) FROM information_schema.TABLES
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_items'
);

-- Extend sales_invoices columns (only if missing)

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='customer_id'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `customer_id` bigint(20) UNSIGNED NULL AFTER `warehouse_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='status'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `status` varchar(12) NOT NULL DEFAULT \'POSTED\' AFTER `customer_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='subtotal'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00 AFTER `total`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='discount_type'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `discount_type` varchar(12) NOT NULL DEFAULT \'PERCENT\' AFTER `subtotal`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='discount_value'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `discount_value` decimal(14,2) NOT NULL DEFAULT 0.00 AFTER `discount_type`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='discount_note'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `discount_note` varchar(255) NULL AFTER `discount_value`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='discount_amount'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `discount_amount` decimal(14,2) NOT NULL DEFAULT 0.00 AFTER `discount_note`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='net_after_discount'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `net_after_discount` decimal(14,2) NOT NULL DEFAULT 0.00 AFTER `discount_amount`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='vat_enabled'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `vat_enabled` tinyint(1) NOT NULL DEFAULT 0 AFTER `net_after_discount`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='vat_rate'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `vat_rate` decimal(8,2) NOT NULL DEFAULT 14.00 AFTER `vat_enabled`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='vat_amount'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `vat_amount` decimal(14,2) NOT NULL DEFAULT 0.00 AFTER `vat_rate`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='grand_total'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `grand_total` decimal(14,2) NOT NULL DEFAULT 0.00 AFTER `vat_amount`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='notes'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `notes` varchar(255) NULL AFTER `grand_total`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='posted_by'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `posted_by` bigint(20) UNSIGNED NULL AFTER `user_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := IF(@has_sales_invoices>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='posted_at'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_invoices` ADD COLUMN `posted_at` datetime NULL AFTER `posted_by`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Extend sales_items (snapshot item name)

SET @col := IF(@has_sales_items>0, (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_items' AND COLUMN_NAME='item_name'
), 1);
SET @sql := IF(@col=0,
  'ALTER TABLE `sales_items` ADD COLUMN `item_name` varchar(200) NULL AFTER `item_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Customer ledger (A/R)
CREATE TABLE IF NOT EXISTS `customer_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `doc_type` varchar(30) NOT NULL,
  `doc_id` bigint(20) UNSIGNED NOT NULL,
  `doc_no` varchar(60) NOT NULL,
  `doc_date` date NOT NULL,
  `debit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `credit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `notes` varchar(255) NULL,
  `created_by` bigint(20) UNSIGNED NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_customer_date` (`customer_id`,`doc_date`),
  KEY `idx_doc` (`doc_type`,`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Permissions (code, name, module)
INSERT IGNORE INTO `permissions` (`code`,`name`,`module`) VALUES
 ('sales.invoices.view','عرض فواتير المبيعات','sales'),
 ('sales.invoices.create','إنشاء فاتورة مبيعات','sales'),
 ('sales.invoices.edit','تعديل فاتورة مبيعات','sales'),
 ('sales.invoices.post','ترحيل فاتورة مبيعات','sales'),
 ('sales.invoices.print','طباعة فاتورة مبيعات','sales');

-- Grant sales permissions to Administrator (role_id=1) and Supervisor/Manager (role_id=2) if roles exist
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, p.id FROM permissions p
WHERE p.code IN ('sales.invoices.view','sales.invoices.create','sales.invoices.edit','sales.invoices.post','sales.invoices.print');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, p.id FROM permissions p
WHERE p.code IN ('sales.invoices.view','sales.invoices.create','sales.invoices.edit','sales.invoices.post','sales.invoices.print')
  AND EXISTS (SELECT 1 FROM roles r WHERE r.id=2);
