CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente VARCHAR(50) NOT NULL,
    sco_number VARCHAR(50) NOT NULL,
    sucursal VARCHAR(100) NOT NULL,
    pos_transaction_id INT NOT NULL,
    started_at DATETIME NOT NULL,
    end_transaction_at DATETIME NULL,
    closed_at DATETIME NOT NULL,
    status ENUM('completed', 'canceled') NOT NULL,
    item_count INT NOT NULL DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payment_type VARCHAR(50) NULL,
    payment_amount DECIMAL(10,2) NULL,
    split_payment BOOLEAN NOT NULL DEFAULT FALSE,
    age_verification_requested BOOLEAN NOT NULL DEFAULT FALSE,
    age_verification_value INT NULL,
    bag_item_count INT NOT NULL DEFAULT 0,
    
    -- restrict lower case
    CONSTRAINT chk_cliente_lower CHECK (cliente = LOWER(cliente)),

    -- anti-duplicados para el UPSERT
    UNIQUE KEY uq_cliente_sucursal_sco_pos (cliente, sucursal, sco_number, pos_transaction_id, (DATE(started_at))),

    -- index para acelerar Grafana
    KEY idx_cliente_sucursal_sco_started (cliente, sucursal, sco_number, started_at)
);