-- =====================================================
-- Sistema de Control de Tarjetas de Crédito - Contabilidad
-- Base de datos: control_tarjetas
-- =====================================================

CREATE DATABASE IF NOT EXISTS speedtravelec_controltarjetas
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE speedtravelec_controltarjetas;

-- ---------------------------------------------------
-- Usuarios y roles
-- ---------------------------------------------------
CREATE TABLE usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    rol ENUM('admin','contador','vendedora','conciliador') NOT NULL DEFAULT 'vendedora',
    activo TINYINT(1) NOT NULL DEFAULT 1,
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Tarjetas de crédito de la empresa
-- ---------------------------------------------------
CREATE TABLE tarjetas_credito (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,          -- Ej: "Visa Empresa 1"
    banco VARCHAR(100) NOT NULL,
    ultimos_4_digitos CHAR(4) NOT NULL,
    -- Datos sensibles SIEMPRE encriptados a nivel de aplicación (AES-256-GCM).
    -- NUNCA se guarda el CVV/CVC, ni encriptado (prohibido por PCI-DSS).
    numero_completo_enc TEXT NULL,
    titular_enc TEXT NULL,
    vencimiento_enc TEXT NULL,
    cupo_total DECIMAL(12,2) NOT NULL,
    saldo_disponible DECIMAL(12,2) NOT NULL,
    dia_corte TINYINT NOT NULL,            -- día del mes (1-31)
    dia_max_pago TINYINT NOT NULL,         -- día del mes (1-31)
    responsable_id INT NULL,               -- quién administra esta tarjeta (contador/admin)
    -- Contacto para solicitar el código de verificación al usar la tarjeta.
    -- Solo el admin puede modificar estos dos campos (ver lógica en la app).
    codigo_contacto_nombre VARCHAR(150) NULL,
    codigo_contacto_valor VARCHAR(150) NULL,   -- teléfono o email de esa persona
    activa TINYINT(1) NOT NULL DEFAULT 1,
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (responsable_id) REFERENCES usuarios(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Asignación de tarjetas a vendedoras (muchos a muchos)
-- Una vendedora solo puede ver/usar las tarjetas que tiene asignadas.
-- ---------------------------------------------------
CREATE TABLE tarjeta_vendedora (
    tarjeta_id INT NOT NULL,
    vendedora_id INT NOT NULL,
    asignado_por INT NOT NULL,
    fecha_asignacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tarjeta_id, vendedora_id),
    FOREIGN KEY (tarjeta_id) REFERENCES tarjetas_credito(id) ON DELETE CASCADE,
    FOREIGN KEY (vendedora_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (asignado_por) REFERENCES usuarios(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Auditoría: quién y cuándo reveló el número completo de una tarjeta
-- ---------------------------------------------------
CREATE TABLE tarjeta_acceso_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tarjeta_id INT NOT NULL,
    usuario_id INT NOT NULL,
    ip VARCHAR(45) NULL,
    fecha DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tarjeta_id) REFERENCES tarjetas_credito(id),
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Proveedores
-- ---------------------------------------------------
CREATE TABLE proveedores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    ruc VARCHAR(20) NULL,
    contacto VARCHAR(150) NULL,
    telefono VARCHAR(30) NULL,
    email VARCHAR(150) NULL,
    cuenta_bancaria VARCHAR(100) NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Consumos (lo que registran las vendedoras)
-- El saldo de la tarjeta NUNCA se toca directamente desde aquí,
-- solo se descuenta cuando un contador/admin VERIFICA el consumo.
-- ---------------------------------------------------
CREATE TABLE consumos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tarjeta_id INT NOT NULL,
    vendedora_id INT NOT NULL,
    proveedor_id INT NULL,
    cliente VARCHAR(150) NULL,
    concepto VARCHAR(255) NOT NULL,
    valor DECIMAL(12,2) NOT NULL,
    comprobante_path VARCHAR(255) NULL,    -- ruta del archivo subido
    estado ENUM('pendiente','verificado','rechazado') NOT NULL DEFAULT 'pendiente',
    motivo_rechazo VARCHAR(255) NULL,
    fecha_consumo DATE NOT NULL,
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    verificado_por INT NULL,
    fecha_verificacion DATETIME NULL,
    -- Conciliación bancaria: solo marca el consumo como revisado contra el
    -- estado de cuenta del banco. NUNCA modifica el saldo de la tarjeta.
    conciliado TINYINT(1) NOT NULL DEFAULT 0,
    conciliado_por INT NULL,
    fecha_conciliacion DATETIME NULL,
    FOREIGN KEY (tarjeta_id) REFERENCES tarjetas_credito(id),
    FOREIGN KEY (vendedora_id) REFERENCES usuarios(id),
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id),
    FOREIGN KEY (verificado_por) REFERENCES usuarios(id),
    FOREIGN KEY (conciliado_por) REFERENCES usuarios(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Historial de saldos (auditoría obligatoria de cada cambio)
-- ---------------------------------------------------
CREATE TABLE saldo_historico (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tarjeta_id INT NOT NULL,
    consumo_id INT NULL,                   -- si el cambio viene de un consumo verificado
    saldo_anterior DECIMAL(12,2) NOT NULL,
    saldo_nuevo DECIMAL(12,2) NOT NULL,
    motivo VARCHAR(255) NOT NULL,          -- Ej: "Consumo verificado #45", "Ajuste manual", "Pago recibido"
    usuario_id INT NOT NULL,
    fecha DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tarjeta_id) REFERENCES tarjetas_credito(id),
    FOREIGN KEY (consumo_id) REFERENCES consumos(id),
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Facturas de proveedores
-- ---------------------------------------------------
CREATE TABLE facturas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    proveedor_id INT NOT NULL,
    numero_factura VARCHAR(50) NOT NULL,
    valor DECIMAL(12,2) NOT NULL,
    fecha_emision DATE NOT NULL,
    fecha_vencimiento DATE NOT NULL,
    estado ENUM('pendiente','pagada','vencida','anulada') NOT NULL DEFAULT 'pendiente',
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Pagos realizados a proveedores (vincula factura + tarjeta usada)
-- ---------------------------------------------------
CREATE TABLE pagos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    factura_id INT NOT NULL,
    tarjeta_id INT NULL,                   -- null si el pago no fue con tarjeta (transferencia, etc.)
    valor DECIMAL(12,2) NOT NULL,
    metodo_pago ENUM('tarjeta','transferencia','efectivo','otro') NOT NULL DEFAULT 'tarjeta',
    fecha_pago DATE NOT NULL,
    comprobante_path VARCHAR(255) NULL,
    registrado_por INT NOT NULL,
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- Conciliación bancaria del pago (solo si fue con tarjeta)
    conciliado TINYINT(1) NOT NULL DEFAULT 0,
    conciliado_por INT NULL,
    fecha_conciliacion DATETIME NULL,
    FOREIGN KEY (factura_id) REFERENCES facturas(id),
    FOREIGN KEY (tarjeta_id) REFERENCES tarjetas_credito(id),
    FOREIGN KEY (registrado_por) REFERENCES usuarios(id),
    FOREIGN KEY (conciliado_por) REFERENCES usuarios(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------
-- Índices para reportes rápidos
-- ---------------------------------------------------
CREATE INDEX idx_consumos_estado ON consumos(estado);
CREATE INDEX idx_consumos_tarjeta ON consumos(tarjeta_id);
CREATE INDEX idx_facturas_estado ON facturas(estado);
CREATE INDEX idx_facturas_vencimiento ON facturas(fecha_vencimiento);

-- ---------------------------------------------------
-- Usuario admin inicial (cambiar password luego del primer login)
-- password: Admin1234 (ya hasheado con password_hash de PHP)
-- ---------------------------------------------------
-- El hash real se genera en el script de instalación (ver install.php)