-- =========================
-- LIMPIEZA (por si existen)
-- =========================
DROP TABLE IF EXISTS asistente;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;

-- =========================
-- TABLA: products
-- =========================
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Productos iniciales
INSERT INTO products (name, price) VALUES
('Inscripcion 2do Congreso CAID', 6000.00),
('Inscripcion 2do Congreso CAID Descuento', 3000.00);

-- =========================
-- TABLA: orders
-- =========================
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    -- Identificadores
    orden_id VARCHAR(50) NOT NULL UNIQUE,
    transaction_id VARCHAR(50) NOT NULL UNIQUE,

    -- Relación producto
    product_id INT NOT NULL,

    -- Datos de compra
    quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
    amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0),

    -- Cliente
    full_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(150) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,

    -- Información adicional
    sede VARCHAR(100) NOT NULL,
    numero_expediente VARCHAR(30) NOT NULL,
    tipo_participante ENUM('Profesional','Estudiante','Publico en general','Familia CAID'),

    -- CardNet session
    session VARCHAR(100) NULL,
    session_key VARCHAR(255) NULL,

    -- Respuesta de pago (CRÍTICO)
    response_code VARCHAR(10) NULL,
    remote_response_code VARCHAR(10) NULL,

    -- Datos de autorización
    authorization_code VARCHAR(50) NULL,
    tx_token VARCHAR(100) NULL,
    rrn VARCHAR(50) NULL,

    -- Estado del pago
    status ENUM('PENDING','PAID','FAILED','CANCELLED') DEFAULT 'PENDING',

    -- Auditoría avanzada (opcional pero recomendado)
    cardnet_raw_response JSON NULL,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,

    -- Relaciones
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- =========================
-- ÍNDICES (rendimiento)
-- =========================
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_email ON orders(customer_email);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- =========================
-- TABLA: asistentes
-- =========================
CREATE TABLE asistente (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    orden_id VARCHAR(50) NOT NULL,

    full_name VARCHAR(70) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (orden_id) REFERENCES orders(orden_id) ON DELETE CASCADE
);