-- Sales Transaction System Database Schema
-- All tables are prefixed with {business_code}_sales_ for consistency

-- 1. SALES TRANSACTIONS - Main transaction records
CREATE TABLE IF NOT EXISTS `{business_code}_sales_transactions` (
    `transaction_id` INT AUTO_INCREMENT PRIMARY KEY,
    `transaction_number` VARCHAR(50) UNIQUE NOT NULL,
    `customer_id` INT NULL,
    `cashier_id` VARCHAR(50) NOT NULL,
    `cashier_name` VARCHAR(100) NOT NULL,
    `shift_id` BIGINT NULL, -- Added for cashier shift tracking
    
    -- Financial totals
    `subtotal` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `tax_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `discount_type` ENUM('none', 'percentage', 'fixed') DEFAULT 'none',
    `discount_value` DECIMAL(15,2) DEFAULT 0.00,
    `discount_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `total_amount` DECIMAL(15,2) NOT NULL,
    `amount_paid` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `change_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    
    -- Status and metadata
    `payment_status` ENUM('pending', 'paid', 'partially_paid', 'voided', 'returned', 'credit') DEFAULT 'pending',
    `transaction_type` ENUM('sale', 'return', 'void') DEFAULT 'sale',
    `transaction_date` DATETIME NOT NULL,
    `notes` TEXT NULL,
    `receipt_printed` BOOLEAN DEFAULT FALSE,
    
    -- Timestamps
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_transaction_number` (`transaction_number`),
    INDEX `idx_transaction_date` (`transaction_date`),
    INDEX `idx_cashier` (`cashier_id`),
    INDEX `idx_payment_status` (`payment_status`),
    INDEX `idx_shift` (`shift_id`),
    FOREIGN KEY (`shift_id`) REFERENCES `{business_code}_cashier_shifts`(`shift_id`)
);

-- 2. SALES ITEMS - Individual items in each transaction
CREATE TABLE IF NOT EXISTS `{business_code}_sales_items` (
    `item_id` INT AUTO_INCREMENT PRIMARY KEY,
    `transaction_id` INT NOT NULL,
    `product_id` INT NOT NULL,
    
    -- Product snapshot (for historical accuracy)
    `product_name` VARCHAR(255) NOT NULL,
    `product_code` VARCHAR(100) NULL,
    `category_name` VARCHAR(100) NULL,
    `sell_type` ENUM('unit', 'weight', 'combo', 'service') NOT NULL,
    `unit_symbol` VARCHAR(10) NULL,
    
    -- Quantity and pricing
    `quantity` DECIMAL(10,3) NOT NULL,
    `weight` DECIMAL(10,3) NULL, -- For weight-based items
    `unit_price` DECIMAL(15,2) NOT NULL,
    `line_subtotal` DECIMAL(15,2) NOT NULL,
    `line_discount_amount` DECIMAL(15,2) DEFAULT 0.00,
    `line_total` DECIMAL(15,2) NOT NULL,
    
    -- Metadata
    `is_returned` BOOLEAN DEFAULT FALSE,
    `returned_quantity` DECIMAL(10,3) DEFAULT 0,
    `returned_weight` DECIMAL(10,3) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`transaction_id`) REFERENCES `{business_code}_sales_transactions`(`transaction_id`) ON DELETE CASCADE,
    INDEX `idx_product_id` (`product_id`),
    INDEX `idx_transaction_id` (`transaction_id`)
);

-- 3. SALES PAYMENTS - Payment methods used for each transaction
CREATE TABLE IF NOT EXISTS `{business_code}_sales_payments` (
    `payment_id` INT AUTO_INCREMENT PRIMARY KEY,
    `transaction_id` INT NOT NULL,
    `payment_method_id` INT NOT NULL,
    `amount` DECIMAL(15,2) NOT NULL,
    `payment_date` DATETIME NOT NULL,
    `reference_number` VARCHAR(100) NULL, -- For card/digital payments
    `card_last_four` VARCHAR(4) NULL, -- Last 4 digits of card
    `approval_code` VARCHAR(50) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`transaction_id`) REFERENCES `{business_code}_sales_transactions`(`transaction_id`) ON DELETE CASCADE,
    FOREIGN KEY (`payment_method_id`) REFERENCES `{business_code}_payment_method`(`id`) ON DELETE RESTRICT,
    INDEX `idx_transaction_id` (`transaction_id`),
    INDEX `idx_payment_method_id` (`payment_method_id`),
    INDEX `idx_payment_date` (`payment_date`)
);

-- 4. SALES RETURNS - Return/void transaction records
CREATE TABLE IF NOT EXISTS `{business_code}_sales_returns` (
    `return_id` INT AUTO_INCREMENT PRIMARY KEY,
    `return_number` VARCHAR(50) UNIQUE NOT NULL,
    `original_transaction_id` INT NOT NULL,
    `return_type` ENUM('full_void', 'partial_return', 'exchange', 'refund') NOT NULL,
    `total_return_amount` DECIMAL(15,2) NOT NULL,
    `refund_method` ENUM('cash', 'original_payment', 'store_credit') DEFAULT 'cash',
    `reason` VARCHAR(255) NULL,
    `processed_by` VARCHAR(50) NOT NULL,
    `processor_name` VARCHAR(100) NOT NULL,
    `return_date` DATETIME NOT NULL,
    `notes` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`original_transaction_id`) REFERENCES `{business_code}_sales_transactions`(`transaction_id`),
    INDEX `idx_return_number` (`return_number`),
    INDEX `idx_original_transaction` (`original_transaction_id`),
    INDEX `idx_return_date` (`return_date`)
);

-- 5. SALES RETURN ITEMS - Specific items being returned
CREATE TABLE IF NOT EXISTS `{business_code}_sales_return_items` (
    `return_item_id` INT AUTO_INCREMENT PRIMARY KEY,
    `return_id` INT NOT NULL,
    `original_item_id` INT NOT NULL,
    `product_id` INT NOT NULL,
    `product_name` VARCHAR(255) NOT NULL,
    `quantity_returned` DECIMAL(10,3) NOT NULL,
    `weight_returned` DECIMAL(10,3) NULL,
    `unit_price` DECIMAL(15,2) NOT NULL,
    `return_amount` DECIMAL(15,2) NOT NULL,
    `restocked` BOOLEAN DEFAULT TRUE, -- Whether item was returned to inventory
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`return_id`) REFERENCES `{business_code}_sales_returns`(`return_id`) ON DELETE CASCADE,
    FOREIGN KEY (`original_item_id`) REFERENCES `{business_code}_sales_items`(`item_id`),
    INDEX `idx_return_id` (`return_id`),
    INDEX `idx_product_id` (`product_id`)
);

-- 6. SALES DAILY SUMMARY - Pre-aggregated daily sales data for fast reporting
CREATE TABLE IF NOT EXISTS `{business_code}_sales_daily_summary` (
    `summary_id` INT AUTO_INCREMENT PRIMARY KEY,
    `summary_date` DATE NOT NULL UNIQUE,
    
    -- Transaction counts
    `total_transactions` INT DEFAULT 0,
    `voided_transactions` INT DEFAULT 0,
    `returned_transactions` INT DEFAULT 0,
    
    -- Financial totals
    `gross_sales` DECIMAL(15,2) DEFAULT 0.00,
    `total_discounts` DECIMAL(15,2) DEFAULT 0.00,
    `total_tax` DECIMAL(15,2) DEFAULT 0.00,
    `net_sales` DECIMAL(15,2) DEFAULT 0.00,
    `total_returns` DECIMAL(15,2) DEFAULT 0.00,
    `total_voids` DECIMAL(15,2) DEFAULT 0.00,
    
    -- Payment method breakdown
    `cash_sales` DECIMAL(15,2) DEFAULT 0.00,
    `card_sales` DECIMAL(15,2) DEFAULT 0.00,
    `digital_wallet_sales` DECIMAL(15,2) DEFAULT 0.00,
    `credit_sales` DECIMAL(15,2) DEFAULT 0.00,
    `other_payments` DECIMAL(15,2) DEFAULT 0.00,
    
    -- Item statistics
    `total_items_sold` INT DEFAULT 0,
    `total_weight_sold` DECIMAL(10,3) DEFAULT 0.00,
    
    -- Hourly breakdown (JSON for flexibility)
    `hourly_sales` JSON NULL,
    
    -- Timestamps
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_summary_date` (`summary_date`)
);

-- 7. CASH DRAWER SESSIONS - Track cash drawer openings and closings
CREATE TABLE IF NOT EXISTS `{business_code}_sales_cash_sessions` (
    `session_id` INT AUTO_INCREMENT PRIMARY KEY,
    `cashier_id` VARCHAR(50) NOT NULL,
    `cashier_name` VARCHAR(100) NOT NULL,
    `opening_cash` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `closing_cash` DECIMAL(15,2) NULL,
    `expected_cash` DECIMAL(15,2) NULL,
    `cash_difference` DECIMAL(15,2) NULL,
    `session_start` DATETIME NOT NULL,
    `session_end` DATETIME NULL,
    `session_status` ENUM('open', 'closed') DEFAULT 'open',
    `notes` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_cashier_id` (`cashier_id`),
    INDEX `idx_session_start` (`session_start`),
    INDEX `idx_session_status` (`session_status`)
);

-- 8. SALES SEQUENCE NUMBERS - Track transaction numbering
CREATE TABLE IF NOT EXISTS `{business_code}_sales_sequences` (
    `sequence_type` VARCHAR(50) PRIMARY KEY,
    `current_number` INT NOT NULL DEFAULT 0,
    `prefix` VARCHAR(10) DEFAULT '',
    `suffix` VARCHAR(10) DEFAULT '',
    `date_format` VARCHAR(20) DEFAULT '',
    `reset_daily` BOOLEAN DEFAULT FALSE,
    `last_reset_date` DATE NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Initialize sequence numbers
INSERT IGNORE INTO `{business_code}_sales_sequences` 
(`sequence_type`, `current_number`, `prefix`, `date_format`, `reset_daily`) 
VALUES 
('transaction', 0, 'TXN', 'Ymd', TRUE),
('return', 0, 'RTN', 'Ymd', TRUE); 