CREATE DATABASE IF NOT EXISTS ventas_bot;
USE ventas_bot;

CREATE TABLE IF NOT EXISTS agencias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    token_seguridad VARCHAR(100) NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agencia_id INT NOT NULL,
    serial_ticket VARCHAR(50) NOT NULL,
    fecha_venta DATE NOT NULL,
    hora_venta TIME NOT NULL,
    sistema VARCHAR(50) NOT NULL,
    monto_total DECIMAL(10,2) NOT NULL,
    anulado BOOLEAN DEFAULT FALSE,
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY (agencia_id, serial_ticket),
    FOREIGN KEY (agencia_id) REFERENCES agencias(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS jugadas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    loteria VARCHAR(100) NOT NULL,
    numero VARCHAR(20) NOT NULL,
    monto DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);