-- =============================================================================
-- SCRIPT COMPLETO DE BASE DE DATOS - PROYECTO "CANJEA" (PostgreSQL)
-- Crea los esquemas (auth, public), tablas, índices y datos iniciales (seeds)
-- =============================================================================

-- =============================================================================
-- 1. CREACIÓN DE ESQUEMAS
-- =============================================================================
CREATE SCHEMA IF NOT EXISTS auth;

-- Habilitar extensión para generación de UUIDs si no está habilitada
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =============================================================================
-- 2. CREACIÓN DE TABLAS DE DOMINIO (Esquema public - dependencias previas)
-- =============================================================================

-- 2.1. Tabla de Ferreterías Autorizadas
CREATE TABLE IF NOT EXISTS public.ferreterias (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nombre VARCHAR(255) NOT NULL,
    rif VARCHAR(50) NOT NULL UNIQUE, -- Identificador tributario / NIT / RIF
    direccion TEXT,
    telefono VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- 3. CREACIÓN DE TABLAS DE AUTENTICACIÓN Y MENÚS (Esquema auth)
-- =============================================================================

-- 3.1. Roles del sistema
CREATE TABLE IF NOT EXISTS auth.roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name_es VARCHAR(100) NOT NULL UNIQUE,
    name_en VARCHAR(100) NOT NULL UNIQUE,
    description_es TEXT,
    description_en TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3.3. Tabla pivote muchos a muchos para usuarios y roles
CREATE TABLE IF NOT EXISTS auth.role_user (
    role_id UUID NOT NULL REFERENCES auth.roles(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, user_id)
);

-- 3.4. Módulos que agrupan páginas del menú dinámico
CREATE TABLE IF NOT EXISTS auth.module (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name_es VARCHAR(100) NOT NULL UNIQUE,
    name_en VARCHAR(100) NOT NULL UNIQUE,
    icon VARCHAR(100),
    order_index INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3.5. Páginas del menú dinámico asociadas a módulos
CREATE TABLE IF NOT EXISTS auth.page (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    module_id UUID NOT NULL REFERENCES auth.module(id) ON DELETE CASCADE,
    name_es VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    path VARCHAR(255) NOT NULL UNIQUE,
    icon VARCHAR(100),
    order_index INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3.6. Perfil de permisos de páginas por rol
CREATE TABLE IF NOT EXISTS auth.profile (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    role_id UUID NOT NULL REFERENCES auth.roles(id) ON DELETE CASCADE,
    page_id UUID NOT NULL REFERENCES auth.page(id) ON DELETE CASCADE,
    can_view BOOLEAN DEFAULT FALSE,
    can_create BOOLEAN DEFAULT FALSE,
    can_edit BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_role_page UNIQUE (role_id, page_id)
);

-- 3.7. Registro de logs de auditoría de seguridad
CREATE TABLE IF NOT EXISTS auth.logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    action VARCHAR(255) NOT NULL,
    actor_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    target_id VARCHAR(255) NULL,
    metadata JSONB NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3.9. Códigos generados para recuperación de contraseña
CREATE TABLE IF NOT EXISTS auth.password_reset_codes (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    code_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    consumed_at TIMESTAMP NULL
);

-- 3.10. Mensajes y plantillas de correo localizadas
CREATE TABLE IF NOT EXISTS auth.messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(100) NOT NULL UNIQUE,
    subject_es VARCHAR(255) NOT NULL,
    subject_en VARCHAR(255) NOT NULL,
    body_html_es TEXT NOT NULL,
    body_html_en TEXT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- 4. CREACIÓN DE TABLAS DE DOMINIO DE CERTIFICADOS Y CANJES
-- =============================================================================

-- 4.0. Tipos de certificado
CREATE TABLE IF NOT EXISTS public.certificado_tipos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    codigo VARCHAR(10) NOT NULL UNIQUE,
    nombre VARCHAR(50) NOT NULL,
    monto NUMERIC(12, 2) NOT NULL CHECK (monto >= 0),
    color VARCHAR(30) NOT NULL,
    fondo_archivo VARCHAR(120) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4.1. Tabla de Certificados Emitidos (Canje Único Total)
CREATE TABLE IF NOT EXISTS public.certificados (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    codigo_barras VARCHAR(100) NOT NULL UNIQUE, -- Código secuencial legible (ej. CERT-00001)
    beneficiario_nombre VARCHAR(255) NOT NULL,
    beneficiario_dni VARCHAR(50) NOT NULL, -- Cédula de identidad o DNI del beneficiario
    beneficiario_telefono VARCHAR(50),
    parroquia VARCHAR(100), -- Parroquia del beneficiario (Municipio Guaicaipuro)
    tipo_id UUID NOT NULL REFERENCES public.certificado_tipos(id) ON DELETE RESTRICT,
    ferreteria_id UUID REFERENCES public.ferreterias(id) ON DELETE RESTRICT,
    monto_inicial NUMERIC(12, 2) NOT NULL CHECK (monto_inicial >= 0),
    monto_actual NUMERIC(12, 2) NOT NULL CHECK (monto_actual >= 0),
    estado VARCHAR(20) NOT NULL DEFAULT 'ACTIVO', -- 'ACTIVO', 'AGOTADO', 'ANULADO'
    creado_por UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT check_monto_actual CHECK (monto_actual <= monto_inicial),
    CONSTRAINT check_monto_actual_canje_total CHECK (monto_actual = monto_inicial OR monto_actual = 0) -- Garantiza que solo sea 100% o 0% (Canje Total)
);

-- 4.2. Tabla de Canjes de Certificados
CREATE TABLE IF NOT EXISTS public.canjes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    certificado_id UUID NOT NULL REFERENCES public.certificados(id) ON DELETE RESTRICT,
    ferreteria_id UUID NOT NULL REFERENCES public.ferreterias(id) ON DELETE RESTRICT,
    monto NUMERIC(12, 2) NOT NULL CHECK (monto > 0), -- Coincide con el monto_inicial del certificado
    realizado_por UUID NOT NULL REFERENCES auth.users(id), -- Usuario de ferretería que valida
    descripcion TEXT, -- Detalles opcionales (factura, materiales entregados)
    beneficiario_cedula_foto TEXT, -- Foto de la cédula/DNI capturada al canjear (data URL base64)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- 5. CREACIÓN DE ÍNDICES OPTIMIZADOS
-- =============================================================================
CREATE INDEX IF NOT EXISTS idx_certificados_dni ON public.certificados(beneficiario_dni);
CREATE INDEX IF NOT EXISTS idx_certificados_codigo ON public.certificados(codigo_barras);
CREATE INDEX IF NOT EXISTS idx_certificados_estado ON public.certificados(estado);
CREATE INDEX IF NOT EXISTS idx_certificados_tipo ON public.certificados(tipo_id);
CREATE INDEX IF NOT EXISTS idx_certificados_ferreteria ON public.certificados(ferreteria_id);
CREATE INDEX IF NOT EXISTS idx_canjes_certificado ON public.canjes(certificado_id);
CREATE INDEX IF NOT EXISTS idx_canjes_ferreteria ON public.canjes(ferreteria_id);

-- =============================================================================
-- 6. CARGA DE DATOS INICIALES (SEEDS)
-- =============================================================================

-- 6.1. Insertar Roles del Sistema
INSERT INTO auth.roles (id, name_es, name_en, description_es, description_en, is_active)
VALUES 
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'Administrador', 'Administrator', 'Control total del sistema', 'Full system control', TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'Registrador', 'Registrar', 'Registro y emisión de certificados de la Alcaldía', 'Registration and certificate issuance', TRUE),
  ('2c8d5a32-bfc9-4eb7-ca96-e3f15f3d4e3e', 'Ferretería', 'Hardware Store', 'Acceso para validación y canje de certificados en tienda', 'Access to validate and redeem certificates in-store', TRUE)
ON CONFLICT (id) DO UPDATE 
SET name_es = EXCLUDED.name_es, name_en = EXCLUDED.name_en, description_es = EXCLUDED.description_es, description_en = EXCLUDED.description_en;

-- 6.2. Insertar Módulos del Menú Dinámico
INSERT INTO auth.module (id, name_es, name_en, icon, order_index, is_active)
VALUES
  ('a5b6c7d8-e9f0-1a2b-3c4d-5e6f7a8b9c0d', 'Gestión Canjea', 'Canjea Management', 'card_membership', 10, TRUE)
ON CONFLICT (id) DO UPDATE
SET name_es = EXCLUDED.name_es, name_en = EXCLUDED.name_en, icon = EXCLUDED.icon, order_index = EXCLUDED.order_index;

-- 6.3. Insertar Páginas del Menú Dinámico
INSERT INTO auth.page (id, module_id, name_es, name_en, path, icon, order_index, is_active)
VALUES
  ('c1d2e3f4-a5b6-7c8d-9e0f-1a2b3c4d5e6f', 'a5b6c7d8-e9f0-1a2b-3c4d-5e6f7a8b9c0d', 'Ferreterías', 'Hardware Stores', '/ferreterias', 'store', 1, TRUE),
  ('d2e3f4a5-b6c7-8d9e-0f1a-2b3c4d5e6f7a', 'a5b6c7d8-e9f0-1a2b-3c4d-5e6f7a8b9c0d', 'Certificados', 'Certificates', '/certificados', 'qr_code_2', 2, TRUE),
  ('e3f4a5b6-c7d8-9e0f-1a2b-3c4d5e6f7a8b', 'a5b6c7d8-e9f0-1a2b-3c4d-5e6f7a8b9c0d', 'Escanear QR', 'Scan QR', '/escanear', 'qr_code_scanner', 3, TRUE),
  ('f4a5b6c7-d8e9-0f1a-2b3c-4d5e6f7a8b9c', 'a5b6c7d8-e9f0-1a2b-3c4d-5e6f7a8b9c0d', 'Reporte Canjes', 'Redemptions Report', '/reportes', 'analytics', 4, TRUE)
ON CONFLICT (id) DO UPDATE
SET module_id = EXCLUDED.module_id, name_es = EXCLUDED.name_es, name_en = EXCLUDED.name_en, path = EXCLUDED.path, icon = EXCLUDED.icon, order_index = EXCLUDED.order_index;

-- 6.4. Configurar Permisos por Rol (auth.profile)
-- Administrador: Acceso a Ferreterías, Certificados, Reportes y Escaneo
INSERT INTO auth.profile (role_id, page_id, can_view, can_create, can_edit, can_delete) VALUES
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'c1d2e3f4-a5b6-7c8d-9e0f-1a2b3c4d5e6f', TRUE, TRUE, TRUE, TRUE), -- Ferreterías (CRUD)
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'd2e3f4a5-b6c7-8d9e-0f1a-2b3c4d5e6f7a', TRUE, TRUE, TRUE, TRUE), -- Certificados (CRUD)
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'e3f4a5b6-c7d8-9e0f-1a2b-3c4d5e6f7a8b', TRUE, TRUE, FALSE, FALSE), -- Escanear
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'f4a5b6c7-d8e9-0f1a-2b3c-4d5e6f7a8b9c', TRUE, FALSE, FALSE, FALSE) -- Reportes
ON CONFLICT (role_id, page_id) DO UPDATE
SET can_view = EXCLUDED.can_view, can_create = EXCLUDED.can_create, can_edit = EXCLUDED.can_edit, can_delete = EXCLUDED.can_delete;

-- Registrador: Acceso a Certificados y Escaneo (Solo visualización)
INSERT INTO auth.profile (role_id, page_id, can_view, can_create, can_edit, can_delete) VALUES
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'd2e3f4a5-b6c7-8d9e-0f1a-2b3c4d5e6f7a', TRUE, TRUE, TRUE, FALSE), -- Certificados (Crear/Editar)
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'e3f4a5b6-c7d8-9e0f-1a2b-3c4d5e6f7a8b', TRUE, FALSE, FALSE, FALSE) -- Escanear (Verificar estado)
ON CONFLICT (role_id, page_id) DO UPDATE
SET can_view = EXCLUDED.can_view, can_create = EXCLUDED.can_create, can_edit = EXCLUDED.can_edit, can_delete = EXCLUDED.can_delete;

-- Ferretería: Acceso exclusivo a Escaneo y Consulta
INSERT INTO auth.profile (role_id, page_id, can_view, can_create, can_edit, can_delete) VALUES
  ('2c8d5a32-bfc9-4eb7-ca96-e3f15f3d4e3e', 'e3f4a5b6-c7d8-9e0f-1a2b-3c4d5e6f7a8b', TRUE, TRUE, FALSE, FALSE) -- Escanear y Canjear
ON CONFLICT (role_id, page_id) DO UPDATE
SET can_view = EXCLUDED.can_view, can_create = EXCLUDED.can_create, can_edit = EXCLUDED.can_edit, can_delete = EXCLUDED.can_delete;

-- 6.5. Crear Plantilla de Correo para Recuperación de Clave (PASSWORD_RESET)
INSERT INTO auth.messages (id, code, subject_es, subject_en, body_html_es, body_html_en, is_active)
VALUES
  ('a0b1c2d3-e4f5-4a5b-6c7d-8e9f0a1b2c3d', 'PASSWORD_RESET', 'Código de recuperación de contraseña - Canjea', 'Password Reset Code - Canjea',
   '<div style="font-family: Arial, sans-serif; padding: 20px; background-color: #f5f5f5;"><div style="max-width: 600px; margin: 0 auto; background-color: #ffffff; padding: 20px; border-radius: 8px; border: 1px solid #ddd;"><h2>Recuperación de Contraseña - Canjea</h2><p>Hola,</p><p>Hemos recibido una solicitud para restablecer tu contraseña en la plataforma Canjea.</p><p>Usa el siguiente código de seguridad de 6 dígitos para continuar:</p><h3 style="color: #1976D2; font-size: 28px; letter-spacing: 3px; text-align: center; margin: 20px 0;">{{code}}</h3><p>Este código es válido por 15 minutos. Si no realizaste esta solicitud, puedes ignorar este correo de forma segura.</p><hr style="border: 0; border-top: 1px solid #eee; margin: 20px 0;" /><p style="font-size: 12px; color: #777; text-align: center;">Este es un correo automático. Por favor, no respondas a este mensaje.</p></div></div>',
   '<div style="font-family: Arial, sans-serif; padding: 20px; background-color: #f5f5f5;"><div style="max-width: 600px; margin: 0 auto; background-color: #ffffff; padding: 20px; border-radius: 8px; border: 1px solid #ddd;"><h2>Password Recovery - Canjea</h2><p>Hello,</p><p>We received a request to reset your password on the Canjea platform.</p><p>Use the following 6-digit security code to proceed:</p><h3 style="color: #1976D2; font-size: 28px; letter-spacing: 3px; text-align: center; margin: 20px 0;">{{code}}</h3><p>This code is valid for 15 minutes. If you did not request this, you can safely ignore this email.</p><hr style="border: 0; border-top: 1px solid #eee; margin: 20px 0;" /><p style="font-size: 12px; color: #777; text-align: center;">This is an automatic email. Please do not reply to this message.</p></div></div>',
   TRUE)
ON CONFLICT (code) DO UPDATE
SET subject_es = EXCLUDED.subject_es, subject_en = EXCLUDED.subject_en, body_html_es = EXCLUDED.body_html_es, body_html_en = EXCLUDED.body_html_en;

-- 6.5.1. Tipos de certificado (estándar + especiales + materiales)
INSERT INTO public.certificado_tipos (id, codigo, nombre, monto, color, fondo_archivo)
VALUES
    ('a1000001-0000-4000-8000-000000000001', 'A', 'Tipo A', 150.00, '#2E7D32', 'fondo_certificadoA.jpeg'),
    ('a1000001-0000-4000-8000-000000000002', 'B', 'Tipo B', 200.00, '#C4A574', 'fondo_certificadoB.jpeg'),
    ('a1000001-0000-4000-8000-000000000003', 'C', 'Tipo C', 250.00, '#F9A825', 'fondo_certificadoC.jpeg'),
    ('a1000001-0000-4000-8000-000000000004', 'AE', 'Tipo A Especial', 150.00, '#0D47A1', 'fondo_certificadoAespecial.jpeg'),
    ('a1000001-0000-4000-8000-000000000005', 'BE', 'Tipo B Especial', 200.00, '#1565C0', 'fondo_certificadoBespecial.jpeg'),
    ('a1000001-0000-4000-8000-000000000006', 'CE', 'Tipo C Especial', 250.00, '#E65100', 'fondo_certificadoCespecial.jpeg'),
    ('a1000001-0000-4000-8000-000000000007', 'MA', 'Tipo A Materiales', 150.00, '#1A237E', 'certA.jpeg'),
    ('a1000001-0000-4000-8000-000000000008', 'MB', 'Tipo B Materiales', 200.00, '#283593', 'certB.jpeg'),
    ('a1000001-0000-4000-8000-000000000009', 'MC', 'Tipo C Materiales', 250.00, '#3949AB', 'certC.jpeg'),
    ('a1000001-0000-4000-8000-00000000000a', 'D', 'Tipo D', 0.00, '#0D47A1', 'certD.jpeg')
ON CONFLICT (codigo) DO NOTHING;

-- 6.6. Crear Ferretería de Prueba
INSERT INTO public.ferreterias (id, nombre, rif, direccion, telefono, is_active)
VALUES 
  ('f7b6a5b6-c7d8-9e0f-1a2b-3c4d5e6f7a8b', 'Ferretería El Tornillo Alegre', 'J-12345678-9', 'Av. Bolívar, Local 12, Ciudad Bolívar', '0285-6321458', TRUE)
ON CONFLICT (rif) DO NOTHING;

-- 6.8. Vincular Usuarios a sus Respectivos Roles
-- Admin a Administrador
INSERT INTO auth.role_user (role_id, user_id)
VALUES ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'd7b6c7d8-e9f0-1a2b-3c4d-5e6f7a8b9c0d')
ON CONFLICT (role_id, user_id) DO NOTHING;

-- Registrador a Registrador
INSERT INTO auth.role_user (role_id, user_id)
VALUES ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'e7b6c7d8-e9f0-1a2b-3c4d-5e6f7a8b9c0e')
ON CONFLICT (role_id, user_id) DO NOTHING;

-- Ferretero a Ferretería
INSERT INTO auth.role_user (role_id, user_id)
VALUES ('2c8d5a32-bfc9-4eb7-ca96-e3f15f3d4e3e', 'f7b6c7d8-e9f0-1a2b-3c4d-5e6f7a8b9c0f')
ON CONFLICT (role_id, user_id) DO NOTHING;
