-- =====================================================
-- POS System - Branch M001 Setup Script
-- For use in phpMyAdmin or MySQL command line
-- =====================================================

USE pos;

-- Create credit_transactions table
CREATE TABLE IF NOT EXISTS `m001_credit_transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `type` enum('sale','payment') NOT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `payment_method` enum('cash','gcash','credit') DEFAULT NULL COMMENT 'Payment method for payment transactions',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes` text,
  PRIMARY KEY (`transaction_id`),
  KEY `customer_id` (`customer_id`),
  KEY `sale_id` (`sale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create customers table
CREATE TABLE IF NOT EXISTS `m001_customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `credit_balance` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create items_cat table first (parent table)
CREATE TABLE IF NOT EXISTS `m001_items_cat` (
  `items_cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `items_cat_name` varchar(100) NOT NULL,
  PRIMARY KEY (`items_cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create items table (child table)
CREATE TABLE IF NOT EXISTS `m001_items` (
  `items_id` int(11) NOT NULL AUTO_INCREMENT,
  `items_name` varchar(255) NOT NULL,
  `items_cat_id` int(11) NOT NULL,
  `sell_type` enum('unit','fraction') NOT NULL COMMENT 'unit: sell as whole, fraction: sell by weight/portion',
  `selling_price` decimal(10,2) NOT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `is_favorite` tinyint(1) DEFAULT '0',
  `current_stock` decimal(10,3) DEFAULT 0.00 COMMENT 'Current available stock quantity',
  `reorder_level` decimal(10,3) DEFAULT 0.00 COMMENT 'Minimum stock level for reorder alerts',
  `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`items_id`),
  KEY `items_cat_id` (`items_cat_id`),
  CONSTRAINT `m001_items_cat_fk` FOREIGN KEY (`items_cat_id`) REFERENCES `m001_items_cat` (`items_cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create sales table
CREATE TABLE IF NOT EXISTS `m001_sales` (
  `sale_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `tax` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `payment_method` enum('cash','gcash','credit') NOT NULL DEFAULT 'cash',
  `amount_received` decimal(10,2) NOT NULL,
  `amount_change` decimal(10,2) NOT NULL,
  `status` enum('active','voided') DEFAULT 'active',
  `voided_at` timestamp NULL DEFAULT NULL,
  `voided_by` int(11) DEFAULT NULL,
  `void_reason` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`sale_id`),
  KEY `user_id` (`user_id`),
  KEY `customer_id` (`customer_id`),
  KEY `status` (`status`),
  KEY `voided_by` (`voided_by`),
  CONSTRAINT `m001_sales_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `m001_sales_customer_fk` FOREIGN KEY (`customer_id`) REFERENCES `m001_customers` (`customer_id`) ON DELETE SET NULL,
  CONSTRAINT `m001_sales_voided_by_fk` FOREIGN KEY (`voided_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create sale_items table
CREATE TABLE IF NOT EXISTS `m001_sale_items` (
  `sale_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `original_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `discount_type` enum('percent','amount') DEFAULT NULL,
  `discount_value` decimal(10,2) DEFAULT 0.00,
  `is_free` tinyint(1) DEFAULT 0,
  `total` decimal(10,2) NOT NULL,
  PRIMARY KEY (`sale_item_id`),
  KEY `sale_id` (`sale_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `m001_sale_items_sale_fk` FOREIGN KEY (`sale_id`) REFERENCES `m001_sales` (`sale_id`) ON DELETE CASCADE,
  CONSTRAINT `m001_sale_items_item_fk` FOREIGN KEY (`item_id`) REFERENCES `m001_items` (`items_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create payment methods table first (parent table)
CREATE TABLE IF NOT EXISTS `m001_payment_methods` (
    `method_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `method_name` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`method_id`),
    UNIQUE KEY `method_name` (`method_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default payment methods (ignore duplicates)
INSERT IGNORE INTO `m001_payment_methods` (`method_name`) VALUES
    ('Cash'),
    ('Bank Transfer'),
    ('Check'),
    ('Credit Card');

-- Create expense categories table
CREATE TABLE IF NOT EXISTS `m001_expense_categories` (
    `category_id` INT PRIMARY KEY AUTO_INCREMENT,
    `category_name` VARCHAR(100) NOT NULL,
    UNIQUE KEY `unique_category` (`category_name`),
    INDEX `idx_category` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create expenses table
CREATE TABLE IF NOT EXISTS `m001_expenses` (
    `expense_id` INT PRIMARY KEY AUTO_INCREMENT,
    `category_id` int(11) NOT NULL,
    `expense_description` TEXT NULL,
    `expense_amount` DECIMAL(10,2) NOT NULL,
    `expense_date` timestamp NOT NULL DEFAULT current_timestamp(),
    `payment_method_id` INT UNSIGNED NOT NULL,
    `status` ENUM('active', 'voided') NOT NULL DEFAULT 'active',
    `created_by` INT NOT NULL,
    `voided_at` TIMESTAMP NULL DEFAULT NULL,
    `voided_by` INT NULL DEFAULT NULL,
    `void_reason` VARCHAR(255) NULL DEFAULT NULL,
    INDEX `idx_date` (`expense_date`),
    INDEX `idx_status` (`status`),
    INDEX `idx_voided_at` (`voided_at`),
    CONSTRAINT `m001_expenses_payment_method_fk` FOREIGN KEY (`payment_method_id`) REFERENCES `m001_payment_methods` (`method_id`),
    CONSTRAINT `m001_expenses_category_fk` FOREIGN KEY (`category_id`) REFERENCES `m001_expense_categories` (`category_id`),
    CONSTRAINT `m001_expenses_created_by_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
    CONSTRAINT `m001_expenses_voided_by_fk` FOREIGN KEY (`voided_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create POS settings table
CREATE TABLE IF NOT EXISTS `m001_pos_settings` (
    `setting_id` INT PRIMARY KEY AUTO_INCREMENT,
    `setting_key` VARCHAR(50) NOT NULL,
    `setting_value` TEXT NOT NULL,
    `setting_description` VARCHAR(255) NULL COMMENT 'Description of what the setting does',
    `setting_group` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Groups settings by functionality',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_setting` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default POS settings (ignore duplicates)
INSERT IGNORE INTO `m001_pos_settings` (`setting_key`, `setting_value`, `setting_description`, `setting_group`) VALUES
('show_counter', '1', 'Show item counter in POS interface', 'display'),
('fraction_item_input', '1', 'Allow fractional quantities for items', 'input'),
('customer_cart_modal', '1', 'Show customer selection modal in cart', 'display'),
('timezone', 'Asia/Manila', 'Application timezone for dates and times', 'general'),
('closed_shift_behavior', 'block', 'How to handle operations when shift is closed (block|warn|allow)', 'shift_control'),
('allow_sales_closed_shift', 'false', 'Allow sales when shift is closed', 'shift_control'),
('allow_expenses_closed_shift', 'false', 'Allow expenses when shift is closed', 'shift_control'),
('allow_credit_closed_shift', 'false', 'Allow credit operations when shift is closed', 'shift_control'),
('allow_void_closed_shift', 'false', 'Allow void operations when shift is closed', 'shift_control'),
('require_shift_note', 'false', 'Require note when closing shift', 'shift_control'),
('min_shift_duration', '0', 'Minimum shift duration in hours (0 for no limit)', 'shift_control'),
('max_shift_duration', '24', 'Maximum shift duration in hours (0 for no limit)', 'shift_control'),
('max_shifts_per_day', '1', 'Maximum number of shifts allowed per cashier per day', 'shift_control');

-- Create stock transactions table - tracks all stock movements
CREATE TABLE IF NOT EXISTS `m001_stock_transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `transaction_type` enum('receipt','delivery','sale','return','adjustment') NOT NULL,
  `quantity_change` decimal(10,3) NOT NULL COMMENT 'Positive for receipts/returns, negative for sales/deliveries',
  `new_balance` decimal(10,3) NOT NULL COMMENT 'Stock balance after this transaction',
  `reference_type` enum('sale','purchase','manual') DEFAULT NULL,
  `reference_id` int(11) DEFAULT NULL COMMENT 'References sale_id, purchase_id, etc.',
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  KEY `item_id` (`item_id`),
  KEY `transaction_type` (`transaction_type`),
  KEY `reference_type_id` (`reference_type`, `reference_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `m001_stock_transactions_item_fk` FOREIGN KEY (`item_id`) REFERENCES `m001_items` (`items_id`),
  CONSTRAINT `m001_stock_transactions_user_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create stock receipts table - main table for stock receipt/delivery documents
CREATE TABLE IF NOT EXISTS `m001_stock_receipts` (
  `receipt_id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_number` varchar(50) NOT NULL,
  `receipt_type` enum('receipt','delivery') NOT NULL COMMENT 'receipt=stock in, delivery=stock out',
  `supplier_name` varchar(255) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL COMMENT 'PO number, invoice number, etc.',
  `total_items` int(11) DEFAULT 0,
  `status` enum('draft','completed','cancelled') DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`receipt_id`),
  UNIQUE KEY `receipt_number` (`receipt_number`),
  KEY `receipt_type` (`receipt_type`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `m001_stock_receipts_user_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create stock receipt items table - detail items for each receipt/delivery
CREATE TABLE IF NOT EXISTS `m001_stock_receipt_items` (
  `receipt_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` decimal(10,3) NOT NULL,
  `unit_cost` decimal(10,2) DEFAULT 0.00,
  `total_cost` decimal(10,2) DEFAULT 0.00,
  `expiry_date` date DEFAULT NULL,
  `batch_number` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`receipt_item_id`),
  KEY `receipt_id` (`receipt_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `m001_stock_receipt_items_receipt_fk` FOREIGN KEY (`receipt_id`) REFERENCES `m001_stock_receipts` (`receipt_id`) ON DELETE CASCADE,
  CONSTRAINT `m001_stock_receipt_items_item_fk` FOREIGN KEY (`item_id`) REFERENCES `m001_items` (`items_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create cash shifts table - tracks daily cash shift sessions
CREATE TABLE IF NOT EXISTS `m001_cash_shifts` (
  `shift_id` int(11) NOT NULL AUTO_INCREMENT,
  `cashier_id` int(11) NOT NULL,
  `shift_date` date NOT NULL,
  `opening_fund` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expected_balance` decimal(10,2) NOT NULL DEFAULT 0.00,
  `closing_count` decimal(10,2) DEFAULT NULL,
  `variance` decimal(10,2) DEFAULT NULL,
  `shift_status` enum('open','closed') NOT NULL DEFAULT 'open',
  `opened_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `closed_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`shift_id`),
  UNIQUE KEY `unique_cashier_date` (`cashier_id`, `shift_date`),
  KEY `shift_date` (`shift_date`),
  KEY `shift_status` (`shift_status`),
  CONSTRAINT `m001_cash_shifts_user_fk` FOREIGN KEY (`cashier_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create cash movements table - tracks all cash transactions
CREATE TABLE IF NOT EXISTS `m001_cash_movements` (
  `movement_id` int(11) NOT NULL AUTO_INCREMENT,
  `movement_type` enum('opening_fund','sale','expense','credit_settlement','cash_count','adjustment','withdrawal','deposit','void_sale','void_expense') NOT NULL,
  `reference_type` enum('opening','sale','expense','credit_payment','closing','manual') NOT NULL,
  `reference_id` int(11) DEFAULT NULL COMMENT 'References sale_id, expense_id, etc.',
  `amount` decimal(10,2) NOT NULL COMMENT 'Positive for inflow, negative for outflow',
  `running_balance` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `cashier_id` int(11) NOT NULL,
  `shift_date` date NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`movement_id`),
  KEY `movement_type` (`movement_type`),
  KEY `reference_type_id` (`reference_type`, `reference_id`),
  KEY `cashier_date` (`cashier_id`, `shift_date`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `m001_cash_movements_user_fk` FOREIGN KEY (`cashier_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create sales audit table - tracks all sales operations for audit trail
CREATE TABLE IF NOT EXISTS `m001_sales_audit` (
  `audit_id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `action_type` enum('create','edit','void','delete','return') NOT NULL,
  `original_total` decimal(10,2) NOT NULL,
  `return_amount` decimal(10,2) DEFAULT 0.00,
  `reason` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `performed_by` int(11) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `sale_data` longtext DEFAULT NULL COMMENT 'JSON snapshot of sale data',
  PRIMARY KEY (`audit_id`),
  KEY `sale_id` (`sale_id`),
  KEY `action_type` (`action_type`),
  KEY `performed_by` (`performed_by`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `m001_sales_audit_user_fk` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create sales returns table - tracks returned sales
CREATE TABLE IF NOT EXISTS `m001_sales_returns` (
  `return_id` int(11) NOT NULL AUTO_INCREMENT,
  `original_sale_id` int(11) NOT NULL,
  `return_amount` decimal(10,2) NOT NULL,
  `return_reason` varchar(255) DEFAULT NULL,
  `return_notes` text DEFAULT NULL,
  `processed_by` int(11) NOT NULL,
  `processed_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`return_id`),
  KEY `original_sale_id` (`original_sale_id`),
  KEY `processed_by` (`processed_by`),
  KEY `processed_at` (`processed_at`),
  CONSTRAINT `m001_sales_returns_user_fk` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create return items table - tracks individual returned items
CREATE TABLE IF NOT EXISTS `m001_return_items` (
  `return_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) NOT NULL,
  `original_sale_item_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `return_quantity` decimal(10,2) NOT NULL,
  `return_price` decimal(10,2) NOT NULL,
  `return_total` decimal(10,2) NOT NULL,
  PRIMARY KEY (`return_item_id`),
  KEY `return_id` (`return_id`),
  KEY `original_sale_item_id` (`original_sale_item_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `m001_return_items_return_fk` FOREIGN KEY (`return_id`) REFERENCES `m001_sales_returns` (`return_id`) ON DELETE CASCADE,
  CONSTRAINT `m001_return_items_sale_item_fk` FOREIGN KEY (`original_sale_item_id`) REFERENCES `m001_sale_items` (`sale_item_id`),
  CONSTRAINT `m001_return_items_item_fk` FOREIGN KEY (`item_id`) REFERENCES `m001_items` (`items_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create cash counts table - tracks cash counting sessions
CREATE TABLE IF NOT EXISTS `m001_cash_counts` (
    `count_id` INT PRIMARY KEY AUTO_INCREMENT,
    `shift_id` INT DEFAULT NULL,
    `cashier_id` INT NOT NULL,
    `count_type` ENUM('opening', 'partial', 'closing') NOT NULL,
    `expected_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `actual_count` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `variance` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_by` INT NOT NULL,
    INDEX `idx_shift` (`shift_id`),
    INDEX `idx_cashier` (`cashier_id`),
    INDEX `idx_count_type` (`count_type`),
    INDEX `idx_created_at` (`created_at`),
    CONSTRAINT `m001_cash_counts_shift_fk` FOREIGN KEY (`shift_id`) REFERENCES `m001_cash_shifts` (`shift_id`) ON DELETE CASCADE,
    CONSTRAINT `m001_cash_counts_cashier_fk` FOREIGN KEY (`cashier_id`) REFERENCES `users` (`id`),
    CONSTRAINT `m001_cash_counts_created_by_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create cash count details table - tracks individual denomination counts
CREATE TABLE IF NOT EXISTS `m001_cash_count_details` (
    `detail_id` INT PRIMARY KEY AUTO_INCREMENT,
    `count_id` INT NOT NULL,
    `denomination_type` ENUM('bill', 'coin') NOT NULL,
    `denomination_value` DECIMAL(10,2) NOT NULL,
    `quantity` INT NOT NULL DEFAULT 0,
    `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    INDEX `idx_count` (`count_id`),
    INDEX `idx_denomination` (`denomination_type`, `denomination_value`),
    CONSTRAINT `m001_cash_count_details_count_fk` FOREIGN KEY (`count_id`) REFERENCES `m001_cash_counts` (`count_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create branch-specific cash denominations table
CREATE TABLE IF NOT EXISTS `m001_cash_denominations` (
    `denomination_id` INT PRIMARY KEY AUTO_INCREMENT,
    `denomination_type` ENUM('bill', 'coin') NOT NULL,
    `denomination_value` DECIMAL(10,2) NOT NULL,
    `denomination_name` VARCHAR(50) NOT NULL,
    `sort_order` INT NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `country_code` VARCHAR(3) NOT NULL DEFAULT 'PHP',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_denomination` (`country_code`, `denomination_value`, `denomination_type`),
    INDEX `idx_active_sort` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default Philippine peso denominations (ignore duplicates)
INSERT IGNORE INTO `m001_cash_denominations` (`denomination_type`, `denomination_value`, `denomination_name`, `sort_order`, `country_code`) VALUES
-- Bills (largest to smallest)
('bill', 1000.00, '₱1000 Bill', 1, 'PHP'),
('bill', 500.00, '₱500 Bill', 2, 'PHP'),
('bill', 200.00, '₱200 Bill', 3, 'PHP'),
('bill', 100.00, '₱100 Bill', 4, 'PHP'),
('bill', 50.00, '₱50 Bill', 5, 'PHP'),
('bill', 20.00, '₱20 Bill', 6, 'PHP'),
-- Coins (largest to smallest)
('coin', 20.00, '₱20 Coin', 7, 'PHP'),
('coin', 10.00, '₱10 Coin', 8, 'PHP'),
('coin', 5.00, '₱5 Coin', 9, 'PHP'),
('coin', 1.00, '₱1 Coin', 10, 'PHP'),
('coin', 0.25, '25¢ Coin', 11, 'PHP'),
('coin', 0.10, '10¢ Coin', 12, 'PHP'),
('coin', 0.05, '5¢ Coin', 13, 'PHP'),
('coin', 0.01, '1¢ Coin', 14, 'PHP');

-- Insert default expense categories (ignore duplicates)
INSERT IGNORE INTO `m001_expense_categories` (`category_name`) VALUES
('Office Supplies'),
('Utilities'),
('Transportation'),
('Meals & Entertainment'),
('Maintenance & Repairs'),
('Marketing & Advertising'),
('Professional Services'),
('Insurance'),
('Rent'),
('Miscellaneous');

-- Insert sample item categories (ignore duplicates)
INSERT IGNORE INTO `m001_items_cat` (`items_cat_name`) VALUES
('Beverages'),
('Snacks'),
('Groceries'),
('Personal Care'),
('Household Items'),
('Electronics'),
('Clothing'),
('Books & Stationery'),
('Health & Medicine'),
('Miscellaneous');

-- =====================================================
-- SETUP COMPLETE FOR BRANCH M001
-- =====================================================

-- Verify table creation
SELECT 
    TABLE_NAME as 'Created Tables',
    TABLE_ROWS as 'Rows',
    CREATE_TIME as 'Created'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'pos' 
    AND TABLE_NAME LIKE 'm001_%'
ORDER BY TABLE_NAME;

-- Show settings
SELECT 
    setting_key,
    setting_value,
    setting_description,
    setting_group
FROM m001_pos_settings
ORDER BY setting_group, setting_key;

-- Show denominations
SELECT 
    denomination_type,
    denomination_name,
    denomination_value,
    sort_order
FROM m001_cash_denominations
WHERE is_active = 1
ORDER BY sort_order;

-- Show expense categories
SELECT category_id, category_name FROM m001_expense_categories ORDER BY category_name;

-- Show item categories
SELECT items_cat_id, items_cat_name FROM m001_items_cat ORDER BY items_cat_name; 
