

-- 2) Create the WhatsApp category if it still does not exist
INSERT INTO setting_categories (name, code)
SELECT 'WhatsApp', 'whatsapp'
FROM (SELECT 1 AS n) AS dummy
WHERE NOT EXISTS (
    SELECT 1 FROM setting_categories WHERE code = 'whatsapp' LIMIT 1
);

-- 3) Create "Send sales invoice via WhatsApp" under that category (boolean, default ON)
INSERT INTO settings (store_id, category_id, name, code, type, value, default_value, description, is_active)
SELECT
    NULL,
    c.id,
    'Send sales invoice via WhatsApp',
    'pos_whatsapp_invoice_enabled',
    'boolean',
    '0',
    '0',
    'When enabled, the WhatsApp invoice button appears on the POS after payment so staff can send the invoice text to the customer.',
    1
FROM setting_categories AS c
WHERE c.code = 'whatsapp'
  AND NOT EXISTS (
      SELECT 1 FROM settings WHERE code = 'pos_whatsapp_invoice_enabled' LIMIT 1
  )
LIMIT 1;







-- 4) Customer ledger on WhatsApp (boolean, default OFF)
INSERT INTO settings (store_id, category_id, name, code, type, value, default_value, description, is_active)
SELECT
    NULL,
    c.id,
    'Customer ledger on WhatsApp',
    'whatsapp_customer_ledger_enabled',
    'boolean',
    '0',
    '0',
    'When enabled, staff can send the customer account ledger as a WhatsApp message from the Customer Ledger screen.',
    1
FROM setting_categories AS c
WHERE c.code = 'whatsapp'
  AND NOT EXISTS (
      SELECT 1 FROM settings WHERE code = 'whatsapp_customer_ledger_enabled' LIMIT 1
  )
LIMIT 1;


UPDATE `settings` SET `value` = '1' WHERE `settings`.`id` = 53;




-- Add branch_id only if it doesn't exist
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'journal_entries'
    AND COLUMN_NAME = 'branch_id'
);

SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `journal_entries` ADD COLUMN `branch_id` INT NOT NULL DEFAULT 1 AFTER `store_id`',
  'SELECT "branch_id already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add branch index only if it doesn't exist
SET @idx_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'journal_entries'
    AND INDEX_NAME = 'idx_branch_id'
);

SET @sql := IF(
  @idx_exists = 0,
  'ALTER TABLE `journal_entries` ADD INDEX `idx_branch_id` (`branch_id`)',
  'SELECT "idx_branch_id already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Backfill branch_id from stores mapping
UPDATE `journal_entries` je
LEFT JOIN `stores` s ON s.`id` = je.`store_id`
SET je.`branch_id` = COALESCE(s.`branch_id`, je.`branch_id`, 1)
WHERE je.`branch_id` IS NULL OR je.`branch_id` = 0;

-- Expand source_type enum to values used by code
ALTER TABLE `journal_entries`
MODIFY COLUMN `source_type` ENUM(
  'sale',
  'purchase',
  'purchase_return',
  'sale_return',
  'expense',
  'payment',
  'receipt',
  'adjustment',
  'manual',
  'opening_balance',
  'quick_transaction',
  'expense_reversal',
  'bank_transfer',
  'cash_adjustment',
  'bank_adjustment'
) NOT NULL;



INSERT INTO `video_help`
(`page_name`, `page_title`, `video_url`, `video_title`, `description`, `is_active`)
VALUES
(
  'suggestions',
  'Suggestions',
  'https://youtu.be/lZvlnZ6Ekhg',
  'Suggestions Feature Training',
  'Learn how to submit, track, and vote on suggestions in the Suggestions feature.',
  1
)
ON DUPLICATE KEY UPDATE
  `page_title` = VALUES(`page_title`),
  `video_url` = VALUES(`video_url`),
  `video_title` = VALUES(`video_title`),
  `description` = VALUES(`description`),
  `is_active` = VALUES(`is_active`),
  `updated_at` = CURRENT_TIMESTAMP;







CREATE TABLE IF NOT EXISTS `purchase_orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `po_number` varchar(50) NOT NULL,
  `vendor_id` int NOT NULL,
  `store_id` int DEFAULT NULL,
  `branch_id` int NOT NULL DEFAULT '1',
  `po_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `total_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `discount_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `net_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `notes` text,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `converted_purchase_id` int DEFAULT NULL,
  `converted_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_po_number` (`po_number`),
  KEY `idx_po_status` (`status`),
  KEY `idx_po_vendor` (`vendor_id`),
  KEY `idx_po_branch_store` (`branch_id`,`store_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `purchase_order_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `purchase_order_id` int NOT NULL,
  `item_id` int NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` decimal(12,3) NOT NULL,
  `quantity_loose` decimal(12,3) NOT NULL DEFAULT '0.000',
  `unit_cost` decimal(10,2) NOT NULL,
  `retail_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `total_cost` decimal(10,2) NOT NULL,
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `purchase_mode` varchar(10) NOT NULL DEFAULT 'loose',
  `bulk_base_qty` decimal(12,3) NOT NULL DEFAULT '0.000',
  PRIMARY KEY (`id`),
  KEY `idx_poi_po` (`purchase_order_id`),
  KEY `idx_poi_item` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Simple phpMyAdmin-safe ALTERs (run once)
ALTER TABLE `purchases`
  ADD COLUMN `source_po_id` int DEFAULT NULL AFTER `status`;

ALTER TABLE `purchases`
  ADD KEY `idx_purchases_source_po_id` (`source_po_id`);



ALTER TABLE `sup_payments`
  ADD COLUMN `withholding_rate` decimal(5,2) DEFAULT '0.00' AFTER `discount_amount`;

ALTER TABLE `sup_payments`
  ADD COLUMN `withholding_amount` decimal(10,2) DEFAULT '0.00' AFTER `withholding_rate`;

ALTER TABLE `sup_payments`
  ADD COLUMN `gross_settlement_amount` decimal(10,2) DEFAULT '0.00' AFTER `withholding_amount`;

ALTER TABLE `sup_payments`
  ADD COLUMN `net_cash_paid` decimal(10,2) DEFAULT '0.00' AFTER `gross_settlement_amount`;





-- 2) Make sure parent table exists and is InnoDB
ALTER TABLE `items` ENGINE=InnoDB;

-- 3) Create child table first (without FK)
CREATE TABLE IF NOT EXISTS `item_barcodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `barcode` varchar(100) NOT NULL,
  `barcode_label` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_item_barcodes_barcode` (`barcode`),
  KEY `idx_item_barcodes_item_id` (`item_id`),
  KEY `idx_item_barcodes_item_active` (`item_id`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 4) Add FK after table creation
ALTER TABLE `item_barcodes`
  ADD CONSTRAINT `fk_item_barcodes_item`
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE;




ALTER TABLE `inventory_movements`
MODIFY COLUMN `reference_type` ENUM(
  'purchase','sale','manual','other','sale_return','purchase_return',
  'stock_transfer_out','stock_transfer_in','production','opening_balance','wastage'
) DEFAULT 'manual';

ALTER TABLE `journal_entries`
MODIFY COLUMN `source_type` ENUM(
  'sale','purchase','purchase_return','sale_return','expense','payment','receipt',
  'adjustment','manual','opening_balance','quick_transaction','expense_reversal',
  'bank_transfer','cash_adjustment','bank_adjustment','wastage'
) NOT NULL;

CREATE TABLE IF NOT EXISTS `wastage` (
  `id` int NOT NULL AUTO_INCREMENT,
  `wastage_number` varchar(50) NOT NULL,
  `store_id` int NOT NULL,
  `branch_id` int NOT NULL DEFAULT 1,
  `wastage_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `total_items` int DEFAULT 0,
  `total_wastage_value` decimal(12,2) DEFAULT 0.00,
  `remarks` text,
  `created_by` int NOT NULL,
  `journal_entry_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `wastage_number` (`wastage_number`),
  KEY `store_id` (`store_id`),
  KEY `branch_id` (`branch_id`),
  KEY `wastage_date` (`wastage_date`),
  KEY `created_by` (`created_by`),
  KEY `journal_entry_id` (`journal_entry_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wastage_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `wastage_id` int NOT NULL,
  `item_id` int NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `quantity` decimal(12,6) NOT NULL,
  `cost_price` decimal(12,4) NOT NULL DEFAULT 0.0000,
  `line_value` decimal(12,2) NOT NULL,
  `loss_reason` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `wastage_id` (`wastage_id`),
  KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `rights` (`name`, `description`, `category`)
SELECT 'Wastage', 'Record stock wastage and damage', 'Inventory'
FROM (SELECT 1 AS _x) AS _
WHERE NOT EXISTS (SELECT 1 FROM `rights` WHERE LOWER(`name`) = 'wastage');

INSERT INTO `role_rights` (`role_id`, `right_id`)
SELECT 1, r.`id` FROM `rights` r WHERE LOWER(r.`name`) = 'wastage'
AND NOT EXISTS (SELECT 1 FROM `role_rights` rr WHERE rr.`role_id` = 1 AND rr.`right_id` = r.`id`);




INSERT IGNORE INTO chart_of_accounts (
    account_code, account_name, account_type, account_category, account_level, description, is_active
) VALUES (
    '4005',
    'Stock Gain',
    'revenue',
    'revenue',
    1,
    'Income from physical inventory increases (stock adjustments)',
    1
);

INSERT IGNORE INTO account_mappings (mapping_code, mapping_name, account_id, description)
SELECT
    'rev_StockGain',
    'Stock Gain',
    id,
    'Revenue from inventory count increases / stock adjustments'
FROM chart_of_accounts
WHERE account_code = '4005'
LIMIT 1;







INSERT INTO `payment_methods` (`method_name`, `display_name`, `icon_class`, `color_class`, `is_active`, `requires_bank_account`, `sort_order`)
VALUES ('cheque', 'Cheque', 'fas fa-money-check-alt', 'btn-outline-secondary', 1, 1, 6)
ON DUPLICATE KEY UPDATE
  `display_name` = VALUES(`display_name`),
  `icon_class` = VALUES(`icon_class`),
  `color_class` = VALUES(`color_class`),
  `is_active` = VALUES(`is_active`),
  `requires_bank_account` = VALUES(`requires_bank_account`),
  `sort_order` = VALUES(`sort_order`);

INSERT INTO `payment_method_tax_rates` (`payment_method`, `tax_rate`, `is_active`)
VALUES ('cheque', 6.00, 1)
ON DUPLICATE KEY UPDATE `tax_rate` = VALUES(`tax_rate`), `is_active` = VALUES(`is_active`);

-- ---------------------------------------------------------------------------
-- 2) Optional GL link: tie account_transactions to a cheque row (sale step later)
-- If you see "Duplicate column name 'cheque_id'", skip this block (already applied).
-- ---------------------------------------------------------------------------
ALTER TABLE `account_transactions`
  ADD COLUMN `cheque_id` int DEFAULT NULL AFTER `account_id`,
  ADD KEY `idx_account_transactions_cheque_id` (`cheque_id`);

-- ---------------------------------------------------------------------------
-- 3) Cheque books + individual cheques
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cheque_books` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bank_account_id` int NOT NULL,
  `branch_id` int NOT NULL DEFAULT '1',
  `first_cheque_number` char(6) NOT NULL COMMENT 'First cheque serial (6 digits)',
  `last_cheque_number` char(6) NOT NULL COMMENT 'Last cheque serial (6 digits)',
  `cheque_count` int NOT NULL DEFAULT '0',
  `notes` varchar(255) DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cheque_books_bank` (`bank_account_id`),
  KEY `idx_cheque_books_branch` (`branch_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `cheques` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cheque_book_id` int NOT NULL,
  `bank_account_id` int NOT NULL,
  `branch_id` int NOT NULL DEFAULT '1',
  `cheque_number` char(6) NOT NULL,
  `status` enum('unused','issued','pending_clearance','cleared','bounced','cancelled','stopped') NOT NULL DEFAULT 'unused',
  `amount` decimal(12,2) DEFAULT NULL COMMENT 'Filled when issued against a payment',
  `sale_id` int DEFAULT NULL,
  `payee_name` varchar(255) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `cleared_date` date DEFAULT NULL,
  `bounced_date` date DEFAULT NULL,
  `memo` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bank_cheque_number` (`bank_account_id`,`cheque_number`),
  KEY `idx_cheques_book` (`cheque_book_id`),
  KEY `idx_cheques_status` (`status`),
  KEY `idx_cheques_sale` (`sale_id`),
  KEY `idx_cheques_branch` (`branch_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------------------------------------------------------------------------
-- 4) Permission: Cheques (Accounts)
-- ---------------------------------------------------------------------------
INSERT INTO `rights` (`id`, `name`, `description`, `category`)
VALUES (114, 'Cheques', 'View and manage cheque books and cheque status', 'Accounts')
ON DUPLICATE KEY UPDATE
  `description` = VALUES(`description`),
  `category` = VALUES(`category`);

INSERT IGNORE INTO `role_rights` (`role_id`, `right_id`) VALUES (1, 114);



ALTER TABLE `hold_items`
ADD COLUMN `serial_ids` TEXT NULL COMMENT 'JSON array of item_serials.id' AFTER `deal_selections`;
