-- Script de Inicialización y Seed Completo (PostgreSQL)
-- Crea roles, módulos, páginas, perfiles de permisos, plantillas de correo y usuario administrador de prueba

-- 0. Columna para foto de perfil (almacenada en BD)
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS photo TEXT NULL;

-- 1. Crear Roles Básicos (idempotente por id)
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 de la plataforma', 'Full platform control', TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'Gerente', 'Manager', 'Acceso a operaciones contables y de negocio', 'Business and accounting operations', TRUE),
  ('2c8d5a32-bfc9-4eb7-ca96-e3f15f3d4e3e', 'Cliente', 'Client', 'Acceso para clientes externos', 'External client access', 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,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

-- 2. Crear Módulos del Menú (idempotente por id)
INSERT INTO auth.module (id, name_es, name_en, icon, order_index, is_active)
VALUES
  ('3d9e6a32-afc0-4eb8-da97-f4f26f4d5e4f', 'Configuración', 'Configuration', 'settings', 10, TRUE),
  ('4e0f7a32-9fc1-4eb9-ea98-05f37f5d6e50', 'Contabilidad', 'Accounting', 'account_balance', 20, 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,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

-- 3. Crear Páginas del Menú (idempotente por id)
INSERT INTO auth.page (id, module_id, name_es, name_en, path, icon, order_index, is_active)
VALUES
  ('5f1f8a32-8fc2-4eba-fa99-16f48f6d7e61', '3d9e6a32-afc0-4eb8-da97-f4f26f4d5e4f', 'Usuarios', 'Users', '/security/users', 'people', 1, TRUE),
  ('6f2f9a32-7fc3-4ebb-0fa9-27f59f7d8e72', '3d9e6a32-afc0-4eb8-da97-f4f26f4d5e4f', 'Roles y Permisos', 'Roles & Permissions', '/security/roles', 'security', 2, TRUE),
  ('7f3f0a32-6fc4-4ebc-1fa0-38f60f8d9e83', '3d9e6a32-afc0-4eb8-da97-f4f26f4d5e4f', 'Plantillas de Correo', 'Email Templates', '/security/messages', 'email', 3, TRUE),
  ('a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d', '3d9e6a32-afc0-4eb8-da97-f4f26f4d5e4f', 'Módulos', 'Modules', '/security/modules', 'view_module', 4, TRUE),
  ('b2c3d4e5-f6a7-4b8c-9d0e-1f2a3b4c5d6e', '3d9e6a32-afc0-4eb8-da97-f4f26f4d5e4f', 'Páginas', 'Pages', '/security/pages', 'web', 5, TRUE),
  ('c3d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e7f', '3d9e6a32-afc0-4eb8-da97-f4f26f4d5e4f', 'Logs de Auditoría', 'Audit Logs', '/security/logs', 'history', 6, TRUE),
  ('d4e5f6a7-b8c9-4d0e-1f2a-3b4c5d6e7f8a', '3d9e6a32-afc0-4eb8-da97-f4f26f4d5e4f', 'Empresas', 'Companies', '/organization/companies', 'business', 7, TRUE),
  ('8f4f1a32-5fc5-4ebd-2fa1-49f71f9dae94', '4e0f7a32-9fc1-4eb9-ea98-05f37f5d6e50', 'Plan de Cuentas', 'Chart of Accounts', '/accounting/chart', 'account_tree', 1, TRUE),
  ('9f5f2a32-4fc6-ebea-3fa2-5af82fadbfa5', '4e0f7a32-9fc1-4eb9-ea98-05f37f5d6e50', 'Asientos Contables', 'Journal Entries', '/accounting/journals', 'history_edu', 2, 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,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

-- 4. Configurar Permisos del Administrador (Acceso Total)
INSERT INTO auth.profile (role_id, page_id, can_view, can_create, can_edit, can_delete)
VALUES
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', '5f1f8a32-8fc2-4eba-fa99-16f48f6d7e61', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', '6f2f9a32-7fc3-4ebb-0fa9-27f59f7d8e72', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', '7f3f0a32-6fc4-4ebc-1fa0-38f60f8d9e83', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'b2c3d4e5-f6a7-4b8c-9d0e-1f2a3b4c5d6e', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'c3d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e7f', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'd4e5f6a7-b8c9-4d0e-1f2a-3b4c5d6e7f8a', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', '8f4f1a32-5fc5-4ebd-2fa1-49f71f9dae94', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', '9f5f2a32-4fc6-ebea-3fa2-5af82fadbfa5', TRUE, TRUE, TRUE, TRUE)
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;

-- 5. Crear Plantilla de Recuperación de Clave (idempotente por code)
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', 'Password Reset Code',
   '<div style="font-family: Arial, sans-serif; padding: 20px;"><h2>Recuperación de Contraseña</h2><p>Hemos recibido una solicitud para restablecer tu contraseña.</p><p>Usa el siguiente código de seguridad de 6 dígitos:</p><h3 style="color: #1976D2; font-size: 24px; letter-spacing: 2px;">{{code}}</h3><p>Este código expira en 15 minutos. Si no realizaste esta solicitud, puedes ignorar este correo de forma segura.</p></div>',
   '<div style="font-family: Arial, sans-serif; padding: 20px;"><h2>Password Recovery</h2><p>We received a request to reset your password.</p><p>Use the following 6-digit security code:</p><h3 style="color: #1976D2; font-size: 24px; letter-spacing: 2px;">{{code}}</h3><p>This code expires in 15 minutes. If you did not request this, you can safely ignore this email.</p></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,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

-- 6. Crear Usuario Administrador de Prueba (admin@example.com / Secret123!)
-- Upsert por email: evita error si el UUID fijo ya está usado por otro usuario.
INSERT INTO auth.users (name, email, password_hash, language, is_active)
VALUES (
  'Administrador de Sistema',
  'admin@example.com',
  '$2y$10$sAeuPY7c85Er3kf0boDCBel8UnbQIKy9WDN8sCf2Fff/HJiM6OFpi',
  'es',
  TRUE
)
ON CONFLICT (email) DO UPDATE
SET
  name = EXCLUDED.name,
  password_hash = EXCLUDED.password_hash,
  language = EXCLUDED.language,
  is_active = EXCLUDED.is_active;

-- 7. Vincular Usuario Administrador al Rol Administrador
INSERT INTO auth.role_user (role_id, user_id)
SELECT '8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', u.id
FROM auth.users u
WHERE u.email = 'admin@example.com'
ON CONFLICT (role_id, user_id) DO NOTHING;

-- 8. Organización multi-empresa (tablas + empresa demo)
-- Ver también: api/docs/db_seed_organization.sql (mismo contenido, ejecutable por separado)

CREATE TABLE IF NOT EXISTS companies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    legal_name VARCHAR(255) NOT NULL,
    trade_name VARCHAR(255) NULL,
    tax_id VARCHAR(50) NOT NULL UNIQUE,
    base_currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    email VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    address TEXT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS branches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) NULL,
    address TEXT NULL,
    phone VARCHAR(50) NULL,
    is_main BOOLEAN NOT NULL DEFAULT FALSE,
    operating_currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (company_id, code)
);

CREATE TABLE IF NOT EXISTS user_access (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    branch_id UUID NULL REFERENCES branches(id) ON DELETE SET NULL,
    can_manage BOOLEAN NOT NULL DEFAULT FALSE,
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, company_id)
);

CREATE INDEX IF NOT EXISTS idx_branches_company_id ON branches(company_id);
CREATE INDEX IF NOT EXISTS idx_user_access_user_id ON user_access(user_id);
CREATE INDEX IF NOT EXISTS idx_user_access_company_id ON user_access(company_id);

ALTER TABLE branches ADD COLUMN IF NOT EXISTS operating_currency VARCHAR(3) NULL;
UPDATE branches b
SET operating_currency = UPPER(c.base_currency)
FROM companies c
WHERE c.id = b.company_id
  AND (b.operating_currency IS NULL OR TRIM(b.operating_currency) = '');
UPDATE branches SET operating_currency = 'USD' WHERE operating_currency IS NULL OR TRIM(operating_currency) = '';
ALTER TABLE branches ALTER COLUMN operating_currency SET DEFAULT 'USD';
ALTER TABLE branches ALTER COLUMN operating_currency SET NOT NULL;

INSERT INTO companies (id, legal_name, trade_name, tax_id, base_currency, email, phone, address, is_active)
VALUES (
  'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a',
  'Empresa Demo S.A.S.',
  'Demo ERP',
  '900123456-1',
  'USD',
  'contacto@demo.local',
  '+57 300 000 0000',
  'Calle Principal 123',
  TRUE
)
ON CONFLICT (id) DO UPDATE
SET legal_name = EXCLUDED.legal_name,
    trade_name = EXCLUDED.trade_name,
    tax_id = EXCLUDED.tax_id,
    base_currency = EXCLUDED.base_currency,
    email = EXCLUDED.email,
    phone = EXCLUDED.phone,
    address = EXCLUDED.address,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO branches (id, company_id, name, code, address, is_main, operating_currency, is_active)
VALUES (
  'e2f3a4b5-c6d7-4e8f-9a0b-1c2d3e4f5a6b',
  'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a',
  'Sede Principal',
  'MAIN',
  'Calle Principal 123',
  TRUE,
  'USD',
  TRUE
)
ON CONFLICT (id) DO UPDATE
SET company_id = EXCLUDED.company_id,
    name = EXCLUDED.name,
    code = EXCLUDED.code,
    address = EXCLUDED.address,
    is_main = EXCLUDED.is_main,
    operating_currency = EXCLUDED.operating_currency,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO user_access (user_id, company_id, branch_id, can_manage, is_default)
SELECT ru.user_id,
       'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a',
       'e2f3a4b5-c6d7-4e8f-9a0b-1c2d3e4f5a6b',
       TRUE,
       TRUE
FROM auth.role_user ru
JOIN auth.roles r ON r.id = ru.role_id
WHERE UPPER(r.name_en) IN ('ADMIN', 'ADMINISTRATOR', 'ADMINISTRADOR')
   OR UPPER(r.name_es) IN ('ADMIN', 'ADMINISTRATOR', 'ADMINISTRADOR')
ON CONFLICT (user_id, company_id) DO UPDATE
SET branch_id = EXCLUDED.branch_id,
    can_manage = EXCLUDED.can_manage,
    is_default = EXCLUDED.is_default;

INSERT INTO user_access (user_id, company_id, branch_id, can_manage, is_default)
SELECT u.id,
       'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a',
       'e2f3a4b5-c6d7-4e8f-9a0b-1c2d3e4f5a6b',
       TRUE,
       TRUE
FROM auth.users u
WHERE u.email IN ('admin@example.com', 'wilmer78@gmail.com')
ON CONFLICT (user_id, company_id) DO UPDATE
SET branch_id = EXCLUDED.branch_id,
    can_manage = EXCLUDED.can_manage,
    is_default = EXCLUDED.is_default;
