-- ============================================================================
-- LOTSERVERFB: SISTEMA CENTRAL MULTI-CONSORCIO DDL
-- ============================================================================
CREATE TABLE consorcios (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(120) NOT NULL,
    rnc VARCHAR(20) UNIQUE NOT NULL,
    slug VARCHAR(60) UNIQUE NOT NULL,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE bancas (
    id SERIAL PRIMARY KEY,
    consorcio_id INT NOT NULL REFERENCES consorcios(id) ON DELETE CASCADE,
    codigo VARCHAR(20) NOT NULL,
    nombre VARCHAR(120) NOT NULL,
    limite_quiniela NUMERIC(12, 2) DEFAULT 200.00,
    limite_pale NUMERIC(12, 2) DEFAULT 100.00,
    limite_tripleta NUMERIC(12, 2) DEFAULT 50.00,
    comision_porcentaje NUMERIC(5, 2) DEFAULT 8.00,
    activo BOOLEAN DEFAULT TRUE,
    CONSTRAINT uq_banca_consorcio_codigo UNIQUE (consorcio_id, codigo)
);

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    consorcio_id INT REFERENCES consorcios(id) ON DELETE CASCADE,
    banca_id INT REFERENCES bancas(id) ON DELETE SET NULL,
    username VARCHAR(60) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    nombre_completo VARCHAR(150) NOT NULL,
    rol VARCHAR(20) NOT NULL CHECK (rol IN ('superadmin', 'dueno', 'supervisor', 'cajero')),
    turno_actual VARCHAR(10) DEFAULT 'DIA' CHECK (turno_actual IN ('DIA', 'NOCHE')),
    activo BOOLEAN DEFAULT TRUE,
    CONSTRAINT uq_usuario_consorcio_username UNIQUE (consorcio_id, username)
);

CREATE TABLE tickets (
    id BIGSERIAL PRIMARY KEY,
    consorcio_id INT NOT NULL REFERENCES consorcios(id) ON DELETE CASCADE,
    banca_id INT NOT NULL REFERENCES bancas(id) ON DELETE RESTRICT,
    usuario_id INT NOT NULL REFERENCES usuarios(id) ON DELETE RESTRICT,
    ticket_no VARCHAR(6) NOT NULL, -- Consecutivo 6 digitos: 000001 - 999999
    attempt_id VARCHAR(64) UNIQUE NOT NULL, -- Idempotencia
    fecha_hora TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    total NUMERIC(12, 2) NOT NULL,
    estado VARCHAR(15) DEFAULT 'ACTIVO' CHECK (estado IN ('ACTIVO', 'ANULADO', 'PAGADO')),
    codigo_seguridad VARCHAR(10) NOT NULL,
    CONSTRAINT uq_ticket_banca_consecutivo UNIQUE (banca_id, ticket_no)
);

CREATE TABLE ticket_loterias (
    id BIGSERIAL PRIMARY KEY,
    ticket_id BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    consorcio_id INT NOT NULL REFERENCES consorcios(id) ON DELETE CASCADE,
    banca_id INT NOT NULL REFERENCES bancas(id) ON DELETE RESTRICT,
    loteria_nombre VARCHAR(80) NOT NULL,
    serial_loteria VARCHAR(12) UNIQUE NOT NULL, -- 12 CARACTERES MAYUSCULAS
    subtotal NUMERIC(12, 2) NOT NULL,
    estado VARCHAR(15) DEFAULT 'ACTIVO'
);

CREATE TABLE cuadres_arqueos (
    id BIGSERIAL PRIMARY KEY,
    consorcio_id INT NOT NULL REFERENCES consorcios(id) ON DELETE CASCADE,
    banca_id INT NOT NULL REFERENCES bancas(id) ON DELETE RESTRICT,
    usuario_id INT NOT NULL REFERENCES usuarios(id) ON DELETE RESTRICT,
    fecha DATE NOT NULL,
    turno VARCHAR(10) NOT NULL CHECK (turno IN ('DIA', 'NOCHE')),
    billetes_2000 INT DEFAULT 0,
    billetes_1000 INT DEFAULT 0,
    billetes_500  INT DEFAULT 0,
    billetes_200  INT DEFAULT 0,
    billetes_100  INT DEFAULT 0,
    billetes_50   INT DEFAULT 0,
    monedas_25    INT DEFAULT 0,
    monedas_10    INT DEFAULT 0,
    monedas_5     INT DEFAULT 0,
    monedas_1     INT DEFAULT 0,
    total_fisico  NUMERIC(12, 2) NOT NULL,
    total_sistema NUMERIC(12, 2) NOT NULL,
    diferencia    NUMERIC(12, 2) NOT NULL,
    observaciones TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);