-- Cashier Shift Management Tables

-- Table for tracking cashier shifts
CREATE TABLE IF NOT EXISTS `{business_code}_cashier_shifts` (
    `shift_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `cashier_id` VARCHAR(50) NOT NULL,
    `shift_start` DATETIME NOT NULL,
    `shift_end` DATETIME NULL,
    `opening_amount` DECIMAL(10,2) NOT NULL,
    `closing_amount` DECIMAL(10,2) NULL,
    `expected_amount` DECIMAL(10,2) NULL,
    `difference_amount` DECIMAL(10,2) NULL,
    `status` ENUM('open', 'closed') DEFAULT 'open',
    `notes` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_cashier_status` (`cashier_id`, `status`),
    INDEX `idx_shift_dates` (`shift_start`, `shift_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for tracking cash counts during shifts
CREATE TABLE IF NOT EXISTS `{business_code}_cashier_counts` (
    `count_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `shift_id` BIGINT NOT NULL,
    `count_type` ENUM('opening', 'closing', 'mid_shift') NOT NULL,
    `total_amount` DECIMAL(10,2) NOT NULL,
    `counted_at` DATETIME NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`shift_id`) REFERENCES `{business_code}_cashier_shifts`(`shift_id`),
    INDEX `idx_shift_counts` (`shift_id`, `count_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for tracking denominations in cash counts
CREATE TABLE IF NOT EXISTS `{business_code}_cashier_denominations` (
    `denomination_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `count_id` BIGINT NOT NULL,
    `denomination_value` DECIMAL(10,2) NOT NULL,
    `quantity` INT NOT NULL,
    `subtotal` DECIMAL(10,2) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`count_id`) REFERENCES `{business_code}_cashier_counts`(`count_id`),
    INDEX `idx_count_denoms` (`count_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for tracking all cash transactions during shifts
CREATE TABLE IF NOT EXISTS `{business_code}_cashier_transactions` (
    `transaction_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `shift_id` BIGINT NOT NULL,
    `transaction_type` ENUM('sale', 'refund', 'void', 'payout', 'cash_in', 'cash_out', 'expense') NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `reference_id` VARCHAR(100) NULL,
    `notes` TEXT NULL,
    `payment_method` VARCHAR(50) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`shift_id`) REFERENCES `{business_code}_cashier_shifts`(`shift_id`),
    INDEX `idx_shift_transactions` (`shift_id`, `transaction_type`),
    INDEX `idx_reference` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 