-- Seed de organización (Fase 1): crea tablas si faltan + empresa demo + accesos
-- Ejecutar tras db_seed_full.sql (requiere esquema auth)

-- === Esquema (idempotente) ===
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);

-- Parche moneda operativa (BD existentes creadas antes de Fase 1.1)
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;

-- === Datos demo ===

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;

-- Asignar acceso a todos los usuarios con rol Administrador
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;

-- Acceso para wilmer78@gmail.com si existe
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 = '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;
