USE BeezifiIOM;

CREATE TABLE IF NOT EXISTS pos_sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    warehouse_id INT UNSIGNED NOT NULL,
    register_name VARCHAR(100) NOT NULL DEFAULT 'Register 1',
    opened_by INT UNSIGNED NOT NULL,
    opened_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_by INT UNSIGNED NULL,
    closed_at TIMESTAMP NULL,
    opening_float DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    closing_float DECIMAL(10,2) NULL,
    cash_sales DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    card_sales DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    other_sales DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    refunds_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    expected_cash DECIMAL(10,2) NULL,
    status ENUM('open', 'closed') NOT NULL DEFAULT 'open',
    notes TEXT NULL,
    INDEX idx_tenant_status (tenant_id, status),
    INDEX idx_tenant_opened (tenant_id, opened_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (opened_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pos_cash_movements (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    session_id INT UNSIGNED NOT NULL,
    type ENUM('open', 'in', 'out', 'close') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    note VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session (session_id),
    FOREIGN KEY (session_id) REFERENCES pos_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pos_transactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    session_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED NULL,
    transaction_number VARCHAR(50) NOT NULL,
    customer_id INT UNSIGNED NULL,
    subtotal 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,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tendered_amount DECIMAL(10,2) NULL,
    change_amount DECIMAL(10,2) NULL,
    payment_method ENUM('cash', 'card', 'split', 'other') NOT NULL DEFAULT 'cash',
    payment_reference VARCHAR(255) NULL,
    status ENUM('completed', 'refunded', 'voided') NOT NULL DEFAULT 'completed',
    refund_of INT UNSIGNED NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_txn_number (tenant_id, transaction_number),
    INDEX idx_session (session_id),
    INDEX idx_tenant_created (tenant_id, created_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES pos_sessions(id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pos_transaction_payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT UNSIGNED NOT NULL,
    method ENUM('cash', 'card', 'other') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    reference VARCHAR(255) NULL,
    FOREIGN KEY (transaction_id) REFERENCES pos_transactions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
