-- ============================================================
-- Schema para Sistema de Gestión de Impagos
-- ============================================================
-- Base de datos: impagos_db
-- Versión: 2.0 (actualizado con sesiones, pagos parciales, PDFs)
-- ============================================================

-- Crear base de datos si no existe
CREATE DATABASE IF NOT EXISTS impagos_db 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

USE impagos_db;

-- ============================================================
-- TABLA: sessions (sesiones de usuario)
-- ============================================================
CREATE TABLE IF NOT EXISTS sessions (
  id VARCHAR(64) NOT NULL PRIMARY KEY,  -- SHA-256 del token
  user_id INT UNSIGNED NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_sessions_user (user_id),
  INDEX idx_sessions_expires (expires_at),
  
  CONSTRAINT fk_sessions_user 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: invoices (facturas)
-- ============================================================
CREATE TABLE IF NOT EXISTS invoices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client VARCHAR(255) NOT NULL,
  client_raw VARCHAR(255) NULL,
  invoice_no VARCHAR(128) NOT NULL,
  invoice_date DATE NOT NULL,
  invoice_year INT GENERATED ALWAYS AS (YEAR(invoice_date)) STORED,
  amount DECIMAL(18,2) NOT NULL,
  
  -- Campos de pago (calculados/actualizados por triggers o app)
  paid_date DATE NULL,
  paid_amount DECIMAL(18,2) NULL DEFAULT 0,
  paid_state ENUM('unpaid', 'partial', 'paid') NOT NULL DEFAULT 'unpaid',
  
  -- Provisión
  provision_year INT NULL,
  
  -- Timestamps
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  -- Índices
  UNIQUE KEY uq_client_invoice_year (client, invoice_no, invoice_year),
  INDEX idx_invoices_client (client),
  INDEX idx_invoices_date (invoice_date),
  INDEX idx_invoices_year (invoice_year),
  INDEX idx_invoices_paid_state (paid_state),
  INDEX idx_invoices_provision (provision_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: invoice_payments (pagos parciales/totales)
-- ============================================================
CREATE TABLE IF NOT EXISTS invoice_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  paid_date DATE NOT NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_payments_invoice (invoice_id),
  INDEX idx_payments_date (paid_date),
  
  CONSTRAINT fk_payments_invoice 
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: actions (acciones/reclamaciones sobre facturas)
-- ============================================================
CREATE TABLE IF NOT EXISTS actions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  action_type VARCHAR(80) NOT NULL,
  action_date DATE NULL,
  note TEXT NULL,
  payment_id BIGINT UNSIGNED NULL,  -- Referencia a pago si es acción de pago
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_actions_invoice (invoice_id),
  INDEX idx_actions_date (action_date),
  INDEX idx_actions_type (action_type),
  INDEX idx_actions_payment (payment_id),
  
  CONSTRAINT fk_actions_invoice 
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_actions_payment 
    FOREIGN KEY (payment_id) REFERENCES invoice_payments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: invoice_files (archivos PDF asociados a facturas)
-- ============================================================
CREATE TABLE IF NOT EXISTS invoice_files (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  rel_path VARCHAR(512) NOT NULL,  -- Ruta relativa al directorio de facturas
  original_name VARCHAR(255) NULL,
  mime_type VARCHAR(100) NULL DEFAULT 'application/pdf',
  file_size BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_files_invoice (invoice_id),
  
  CONSTRAINT fk_files_invoice 
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: security_logs (logs de auditoría de seguridad)
-- ============================================================
CREATE TABLE IF NOT EXISTS security_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event VARCHAR(50) NOT NULL,
  ip VARCHAR(45) NULL,
  user_id INT UNSIGNED NULL,
  username VARCHAR(100) NULL,
  details JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_security_event (event),
  INDEX idx_security_user (user_id),
  INDEX idx_security_date (created_at),
  INDEX idx_security_ip (ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LIMPIEZA AUTOMÁTICA (eventos programados)
-- ============================================================
-- Limpiar sesiones expiradas (ejecutar diariamente)
-- DELETE FROM sessions WHERE expires_at < NOW();

-- Limpiar logs de seguridad antiguos (>90 días)
-- DELETE FROM security_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- ============================================================
-- NOTAS DE MIGRACIÓN
-- ============================================================
-- Si vienes de una versión anterior sin invoice_year generado:
-- ALTER TABLE invoices ADD COLUMN invoice_year INT GENERATED ALWAYS AS (YEAR(invoice_date)) STORED AFTER invoice_date;
-- ALTER TABLE invoices DROP INDEX uq_client_invoice;
-- ALTER TABLE invoices ADD UNIQUE KEY uq_client_invoice_year (client, invoice_no, invoice_year);

-- Si no tienes la columna payment_id en actions:
-- ALTER TABLE actions ADD COLUMN payment_id BIGINT UNSIGNED NULL AFTER note;
-- ALTER TABLE actions ADD INDEX idx_actions_payment (payment_id);
-- ALTER TABLE actions ADD CONSTRAINT fk_actions_payment FOREIGN KEY (payment_id) REFERENCES invoice_payments(id) ON DELETE SET NULL;
