Multiempresa, Roles y Row Level Security

-- ============================================================
-- FARO-SQL-002 · V014__security_context_functions.sql
-- Session context helper functions
-- ============================================================

CREATE OR REPLACE FUNCTION faro.current_company_id()
RETURNS uuid AS $$
DECLARE
  v_company_id text;
BEGIN
  v_company_id := current_setting('app.company_id', true);

  IF v_company_id IS NULL OR v_company_id = '' THEN
    RETURN NULL;
  END IF;

  RETURN v_company_id::uuid;
EXCEPTION
  WHEN invalid_text_representation THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.current_user_id()
RETURNS uuid AS $$
DECLARE
  v_user_id text;
BEGIN
  v_user_id := current_setting('app.user_id', true);

  IF v_user_id IS NULL OR v_user_id = '' THEN
    RETURN NULL;
  END IF;

  RETURN v_user_id::uuid;
EXCEPTION
  WHEN invalid_text_representation THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.current_role_codes()
RETURNS text[] AS $$
DECLARE
  v_roles text;
BEGIN
  v_roles := current_setting('app.role_codes', true);

  IF v_roles IS NULL OR v_roles = '' THEN
    RETURN ARRAY[]::text[];
  END IF;

  RETURN string_to_array(v_roles, ',');
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.has_role(p_role_code text)
RETURNS boolean AS $$
BEGIN
  RETURN p_role_code = ANY(faro.current_role_codes());
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.has_any_role(p_role_codes text[])
RETURNS boolean AS $$
BEGIN
  RETURN faro.current_role_codes() && p_role_codes;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.is_service_role()
RETURNS boolean AS $$
BEGIN
  RETURN faro.has_any_role(ARRAY['integration_service', 'faro_owner']);
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.is_company_admin()
RETURNS boolean AS $$
BEGIN
  RETURN faro.has_any_role(ARRAY['company_admin', 'faro_owner']);
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.is_executive_role()
RETURNS boolean AS $$
BEGIN
  RETURN faro.has_any_role(ARRAY['director', 'general_manager', 'company_admin', 'faro_owner']);
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION faro.current_company_id() IS 'Returns current tenant/company id from app.company_id session setting';
COMMENT ON FUNCTION faro.current_user_id() IS 'Returns current user id from app.user_id session setting';
COMMENT ON FUNCTION faro.current_role_codes() IS 'Returns role codes from app.role_codes session setting';

-- ============================================================
-- FARO-SQL-002 · V015__application_db_roles_and_grants.sql
-- PostgreSQL roles and grants
-- ============================================================

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'faro_app') THEN
    CREATE ROLE faro_app NOINHERIT;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'faro_migration') THEN
    CREATE ROLE faro_migration NOINHERIT;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'faro_readonly') THEN
    CREATE ROLE faro_readonly NOINHERIT;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'faro_ingestion') THEN
    CREATE ROLE faro_ingestion NOINHERIT;
  END IF;
END $$;

GRANT USAGE ON SCHEMA faro TO faro_app;
GRANT USAGE ON SCHEMA audit TO faro_app;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA faro TO faro_app;
GRANT INSERT ON audit.audit_log TO faro_app;

GRANT USAGE ON ALL SEQUENCES IN SCHEMA faro TO faro_app;

GRANT USAGE ON SCHEMA faro TO faro_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA faro TO faro_readonly;

GRANT USAGE ON SCHEMA faro TO faro_ingestion;
GRANT SELECT, INSERT, UPDATE ON faro.data_sources TO faro_ingestion;
GRANT SELECT, INSERT, UPDATE ON faro.ingestion_batches TO faro_ingestion;
GRANT SELECT, INSERT, UPDATE ON faro.raw_records TO faro_ingestion;
GRANT SELECT, INSERT, UPDATE ON faro.source_fields TO faro_ingestion;

ALTER DEFAULT PRIVILEGES IN SCHEMA faro
GRANT SELECT, INSERT, UPDATE ON TABLES TO faro_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA faro
GRANT SELECT ON TABLES TO faro_readonly;

-- ============================================================
-- FARO-SQL-002 · V016__seed_roles_permissions.sql
-- Functional roles and permissions
-- ============================================================

-- This seed assumes companies already exist.
-- System roles may have company_id NULL.

INSERT INTO faro.roles (
  company_id,
  role_code,
  name,
  scope,
  description,
  is_system_role
)
VALUES
  (NULL, 'faro_owner', 'Owner FARO', 'system', 'Internal FARO owner role', true),
  (NULL, 'integration_service', 'Integration Service', 'service', 'Technical integration role', true),
  (NULL, 'company_admin', 'Administrador Empresa', 'company', 'Company administrator', true),
  (NULL, 'director', 'Director', 'company', 'Executive director', true),
  (NULL, 'general_manager', 'Gerente General', 'company', 'General manager', true),
  (NULL, 'area_manager', 'Responsable de Area', 'area', 'Area manager', true),
  (NULL, 'branch_manager', 'Responsable de Sucursal', 'branch', 'Branch manager', true),
  (NULL, 'finance_user', 'Usuario Finanzas', 'area', 'Finance user', true),
  (NULL, 'commercial_user', 'Usuario Comercial', 'area', 'Commercial user', true),
  (NULL, 'stock_user', 'Usuario Stock', 'area', 'Stock user', true),
  (NULL, 'hr_user', 'Usuario RRHH', 'area', 'HR user', true),
  (NULL, 'viewer', 'Lector', 'company', 'Read-only limited user', true),
  (NULL, 'external_auditor', 'Auditor Externo', 'company', 'External auditor', true)
ON CONFLICT (company_id, role_code) DO NOTHING;

INSERT INTO faro.permissions (permission_code, module_code, action_code, description)
VALUES
  ('users.read', 'users', 'read', 'Read users'),
  ('users.manage', 'users', 'manage', 'Manage users'),
  ('roles.read', 'roles', 'read', 'Read roles'),
  ('roles.manage', 'roles', 'manage', 'Manage roles'),
  ('sources.read', 'sources', 'read', 'Read data sources'),
  ('sources.manage', 'sources', 'manage', 'Manage data sources'),
  ('raw.read', 'raw', 'read', 'Read RAW data'),
  ('raw.write', 'raw', 'write', 'Write RAW data'),
  ('staging.read', 'staging', 'read', 'Read staging data'),
  ('staging.write', 'staging', 'write', 'Write staging data'),
  ('facts.read', 'facts', 'read', 'Read facts'),
  ('facts.write', 'facts', 'write', 'Write facts'),
  ('kpis.read', 'kpis', 'read', 'Read KPIs'),
  ('signals.read', 'signals', 'read', 'Read signals'),
  ('rules.read', 'rules', 'read', 'Read rules'),
  ('rules.manage', 'rules', 'manage', 'Manage rules'),
  ('tensions.read', 'tensions', 'read', 'Read tensions'),
  ('tensions.manage', 'tensions', 'manage', 'Manage tensions'),
  ('actions.read', 'actions', 'read', 'Read actions'),
  ('actions.manage', 'actions', 'manage', 'Manage actions'),
  ('evidence.read', 'evidence', 'read', 'Read evidence'),
  ('evidence.upload', 'evidence', 'upload', 'Upload evidence'),
  ('evidence.review', 'evidence', 'review', 'Review evidence'),
  ('score.read', 'score', 'read', 'Read score'),
  ('reports.read', 'reports', 'read', 'Read reports'),
  ('reports.generate', 'reports', 'generate', 'Generate reports'),
  ('audit.read', 'audit', 'read', 'Read audit logs'),
  ('admin.full', 'admin', 'full', 'Full administration')
ON CONFLICT (permission_code) DO NOTHING;

CREATE OR REPLACE FUNCTION faro.current_user_has_permission(p_permission_code text)
RETURNS boolean AS $$
DECLARE
  v_user_id uuid;
  v_company_id uuid;
  v_has_permission boolean;
BEGIN
  v_user_id := faro.current_user_id();
  v_company_id := faro.current_company_id();

  IF v_user_id IS NULL OR v_company_id IS NULL THEN
    RETURN false;
  END IF;

  SELECT EXISTS (
    SELECT 1
    FROM faro.user_roles ur
    JOIN faro.role_permissions rp
      ON rp.role_id = ur.role_id
    JOIN faro.permissions p
      ON p.permission_id = rp.permission_id
    WHERE ur.user_id = v_user_id
      AND ur.company_id = v_company_id
      AND ur.status = 'active'
      AND p.permission_code = p_permission_code
  )
  INTO v_has_permission;

  RETURN COALESCE(v_has_permission, false);
END;
$$ LANGUAGE plpgsql STABLE;

-- Enable RLS en 33 tablas operativas
ALTER TABLE faro.companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.branches ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.areas ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.user_roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.raci_assignments ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.data_sources ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.source_fields ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.ingestion_batches ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.raw_records ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.suppliers ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.employees ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.staging_sales ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.staging_stock ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.staging_receivables ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.staging_purchases ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.fact_sales ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.fact_stock_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.fact_receivables ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.fact_purchases ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.kpi_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.signal_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.rule_definitions ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.rule_evaluations ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.tensions ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.evidence ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.action_status_history ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.score_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.notifications ENABLE ROW LEVEL SECURITY;

-- FORCE RLS: aplica RLS al owner de la tabla.
-- Usar con cuidado en produccion tras definir estrategia de migracion.
ALTER TABLE faro.companies FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.users FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.fact_sales FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.tensions FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.actions FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.evidence FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.score_snapshots FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.reports FORCE ROW LEVEL SECURITY;

-- Companies: el usuario solo ve su empresa (faro_owner bypass).
DROP POLICY IF EXISTS company_isolation_companies_select ON faro.companies;
CREATE POLICY company_isolation_companies_select
ON faro.companies
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  OR faro.has_role('faro_owner')
);

-- Branches: lectura por empresa; escritura solo company_admin.
DROP POLICY IF EXISTS company_isolation_branches_write ON faro.branches;
CREATE POLICY company_isolation_branches_write
ON faro.branches
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
);

-- Areas, Users, User_roles, Raci, Data sources, Source fields
-- siguen el mismo patron (omitido por brevedad; ver migracion fuente).

-- Data sources: company_admin O integration_service pueden escribir.
DROP POLICY IF EXISTS company_isolation_data_sources_write ON faro.data_sources;
CREATE POLICY company_isolation_data_sources_write
ON faro.data_sources
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
  )
);

-- RAW records: doble restriccion (rol tecnico O permiso raw.read).
DROP POLICY IF EXISTS company_isolation_raw_records_select ON faro.raw_records;
CREATE POLICY company_isolation_raw_records_select
ON faro.raw_records
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('raw.read')
  )
);

DROP POLICY IF EXISTS company_isolation_raw_records_write ON faro.raw_records;
CREATE POLICY company_isolation_raw_records_write
ON faro.raw_records
FOR INSERT
TO faro_app
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.current_user_has_permission('raw.write')
    OR faro.is_company_admin()
  )
);

-- Empleados (PII): doble filtro empresa + rol/permiso/propio registro.
DROP POLICY IF EXISTS company_isolation_employees_select ON faro.employees;
CREATE POLICY company_isolation_employees_select
ON faro.employees
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_any_role(ARRAY['hr_user', 'company_admin', 'integration_service'])
    OR user_id = faro.current_user_id()
  )
);

-- Staging sales (mismo patron para stock / receivables / purchases).
DROP POLICY IF EXISTS company_isolation_staging_sales_all ON faro.staging_sales;
CREATE POLICY company_isolation_staging_sales_all
ON faro.staging_sales
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.read')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.write')
  )
);

-- Facts ventas: lectura para ejecutivos, comercial, branch_manager, area_manager.
DROP POLICY IF EXISTS company_isolation_fact_sales_select ON faro.fact_sales;
CREATE POLICY company_isolation_fact_sales_select
ON faro.fact_sales
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_any_role(ARRAY['commercial_user', 'branch_manager', 'area_manager'])
    OR faro.current_user_has_permission('facts.read')
  )
);

-- Facts cobranza: rol finanzas, ejecutivo o permiso facts.read.
DROP POLICY IF EXISTS company_isolation_fact_receivables_select ON faro.fact_receivables;
CREATE POLICY company_isolation_fact_receivables_select
ON faro.fact_receivables
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_role('finance_user')
    OR faro.current_user_has_permission('facts.read')
  )
);

-- Facts stock + purchases siguen el mismo patron (stock_user / area_manager).

-- Rule definitions: globales (company_id NULL) + por empresa.
DROP POLICY IF EXISTS rule_definitions_select_policy ON faro.rule_definitions;
CREATE POLICY rule_definitions_select_policy
ON faro.rule_definitions
FOR SELECT
TO faro_app
USING (
  company_id IS NULL
  OR company_id = faro.current_company_id()
);

DROP POLICY IF EXISTS rule_definitions_write_policy ON faro.rule_definitions;
CREATE POLICY rule_definitions_write_policy
ON faro.rule_definitions
FOR ALL
TO faro_app
USING (
  (
    company_id = faro.current_company_id()
    AND faro.is_company_admin()
  )
  OR faro.has_role('faro_owner')
)
WITH CHECK (
  (
    company_id = faro.current_company_id()
    AND faro.is_company_admin()
  )
  OR faro.has_role('faro_owner')
);

-- Tensions: ejecutivo O responsable O permiso tensions.read O area/branch manager.
DROP POLICY IF EXISTS company_isolation_tensions_select ON faro.tensions;
CREATE POLICY company_isolation_tensions_select
ON faro.tensions
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('tensions.read')
    OR faro.has_any_role(ARRAY['area_manager', 'branch_manager'])
  )
);

-- Tensions UPDATE: solo ejecutivo, responsable o quien tiene tensions.manage.
DROP POLICY IF EXISTS company_isolation_tensions_update ON faro.tensions;
CREATE POLICY company_isolation_tensions_update
ON faro.tensions
FOR UPDATE
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('tensions.manage')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('tensions.manage')
  )
);

-- Actions: ejecutivo O responsable O approver O permiso O area/branch manager.
DROP POLICY IF EXISTS company_isolation_actions_select ON faro.actions;
CREATE POLICY company_isolation_actions_select
ON faro.actions
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR approver_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('actions.read')
    OR faro.has_any_role(ARRAY['area_manager', 'branch_manager'])
  )
);

-- Evidence: ejecutivo O submitted_by O reviewed_by O permiso evidence.read.
DROP POLICY IF EXISTS company_isolation_evidence_select ON faro.evidence;
CREATE POLICY company_isolation_evidence_select
ON faro.evidence
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR submitted_by = faro.current_user_id()
    OR reviewed_by = faro.current_user_id()
    OR faro.current_user_has_permission('evidence.read')
  )
);

-- Score snapshots: lectura por empresa; escritura solo service o admin.
DROP POLICY IF EXISTS company_isolation_score_snapshots_select ON faro.score_snapshots;
CREATE POLICY company_isolation_score_snapshots_select
ON faro.score_snapshots
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
);

-- Reports: lectura por rol/permiso; escritura por ejecutivo o service o permiso.
DROP POLICY IF EXISTS company_isolation_reports_select ON faro.reports;
CREATE POLICY company_isolation_reports_select
ON faro.reports
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.current_user_has_permission('reports.read')
    OR faro.has_any_role(ARRAY['area_manager', 'branch_manager', 'viewer'])
  )
);

-- Notifications: cada usuario ve solo las suyas (salvo ejecutivos).
DROP POLICY IF EXISTS company_isolation_notifications_select ON faro.notifications;
CREATE POLICY company_isolation_notifications_select
ON faro.notifications
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    user_id = faro.current_user_id()
    OR faro.is_executive_role()
  )
);

DROP POLICY IF EXISTS company_isolation_notifications_write ON faro.notifications;
CREATE POLICY company_isolation_notifications_write
ON faro.notifications
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_executive_role()
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_executive_role()
  )
);

-- Vista publica de ventas SIN margen ni costo (rol comercial / branch).
CREATE OR REPLACE VIEW faro.v_sales_public AS
SELECT
  sale_id, company_id, branch_id, customer_id, product_id, employee_id,
  sale_date, document_type, document_number,
  quantity, gross_amount, discount_amount, net_amount,
  discount_pct, currency_code, confidence_score, created_at
FROM faro.fact_sales;

GRANT SELECT ON faro.v_sales_public TO faro_app;

-- Vista ejecutiva de ventas CON margen (filtra por rol).
CREATE OR REPLACE VIEW faro.v_sales_executive AS
SELECT
  sale_id, company_id, branch_id, customer_id, product_id, employee_id,
  sale_date, document_type, document_number,
  quantity, gross_amount, discount_amount, net_amount,
  cost_amount, margin_amount, margin_pct,
  discount_pct, currency_code, confidence_score, created_at
FROM faro.fact_sales
WHERE
  faro.is_executive_role()
  OR faro.current_user_has_permission('facts.read');

GRANT SELECT ON faro.v_sales_executive TO faro_app;

-- Vista de acciones propias.
CREATE OR REPLACE VIEW faro.v_my_actions AS
SELECT a.*
FROM faro.actions a
WHERE
  a.company_id = faro.current_company_id()
  AND (
    a.responsible_user_id = faro.current_user_id()
    OR a.approver_user_id = faro.current_user_id()
    OR faro.is_executive_role()
  );

GRANT SELECT ON faro.v_my_actions TO faro_app;

-- Bandeja de tensiones enriquecida con responsable, area y sucursal.
CREATE OR REPLACE VIEW faro.v_tension_inbox AS
SELECT
  t.tension_id, t.company_id, t.tension_code, t.title, t.description,
  t.severity, t.priority_score, t.confidence_score, t.status,
  t.detected_at, t.due_at, t.responsible_user_id,
  u.full_name AS responsible_name,
  t.area_id, a.name AS area_name,
  t.branch_id, b.name AS branch_name,
  t.score_impact
FROM faro.tensions t
LEFT JOIN faro.users u ON u.user_id = t.responsible_user_id
LEFT JOIN faro.areas a ON a.area_id = t.area_id
LEFT JOIN faro.branches b ON b.branch_id = t.branch_id
WHERE
  t.company_id = faro.current_company_id();

GRANT SELECT ON faro.v_tension_inbox TO faro_app;

-- Vista de diagnostico del contexto RLS activo.
CREATE OR REPLACE VIEW faro.v_rls_context_debug AS
SELECT
  faro.current_company_id() AS current_company_id,
  faro.current_user_id() AS current_user_id,
  faro.current_role_codes() AS current_role_codes,
  faro.is_company_admin() AS is_company_admin,
  faro.is_executive_role() AS is_executive_role,
  faro.is_service_role() AS is_service_role;

GRANT SELECT ON faro.v_rls_context_debug TO faro_app;

-- ============================================================
-- FARO-SQL-002 · V023__rls_test_two_tenants.sql
-- Two tenant isolation test (UUIDs fijos repetibles).
-- ============================================================

INSERT INTO faro.companies (company_id, company_code, legal_name, display_name, status)
VALUES
  ('00000000-0000-0000-0000-000000000001', 'TENANT_A', 'Tenant A SA', 'Tenant A', 'pilot'),
  ('00000000-0000-0000-0000-000000000002', 'TENANT_B', 'Tenant B SA', 'Tenant B', 'pilot')
ON CONFLICT (company_id) DO NOTHING;

INSERT INTO faro.users (user_id, company_id, email, full_name, status)
VALUES
  ('11111111-1111-1111-1111-111111111111', '00000000-0000-0000-0000-000000000001',
   'director.a@test.local', 'Director Tenant A', 'active'),
  ('22222222-2222-2222-2222-222222222222', '00000000-0000-0000-0000-000000000002',
   'director.b@test.local', 'Director Tenant B', 'active')
ON CONFLICT (company_id, email) DO NOTHING;

INSERT INTO faro.data_sources (
  source_id, company_id, source_code, name,
  source_type, format_type, update_frequency, access_method, status
)
VALUES
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '00000000-0000-0000-0000-000000000001',
   'SALES_A', 'Ventas Tenant A', 'csv', 'csv', 'weekly', 'manual_upload', 'active'),
  ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '00000000-0000-0000-0000-000000000002',
   'SALES_B', 'Ventas Tenant B', 'csv', 'csv', 'weekly', 'manual_upload', 'active')
ON CONFLICT (company_id, source_code) DO NOTHING;

INSERT INTO faro.kpi_snapshots (
  company_id, kpi_code, period_start, period_end,
  dimension_type, dimension_id, value, status, confidence_score
)
VALUES
  ('00000000-0000-0000-0000-000000000001', 'KPI-SAL-001',
   '2026-05-01', '2026-05-31', 'company', NULL, 1000000, 'ok', 90),
  ('00000000-0000-0000-0000-000000000002', 'KPI-SAL-001',
   '2026-05-01', '2026-05-31', 'company', NULL, 2000000, 'ok', 90)
ON CONFLICT (company_id, kpi_code, period_start, period_end, dimension_type, dimension_id)
DO NOTHING;

BEGIN;

SELECT set_config('app.company_id', '00000000-0000-0000-0000-000000000001', true);
SELECT set_config('app.user_id', '11111111-1111-1111-1111-111111111111', true);
SELECT set_config('app.role_codes', 'director', true);

-- Debe ver solo Tenant A.
SELECT company_code FROM faro.companies;

-- Debe devolver solo SALES_A.
SELECT source_code, name FROM faro.data_sources ORDER BY source_code;

-- Debe devolver value = 1000000.
SELECT kpi_code, value FROM faro.kpi_snapshots WHERE kpi_code = 'KPI-SAL-001';

COMMIT;

BEGIN;

SELECT set_config('app.company_id', '00000000-0000-0000-0000-000000000002', true);
SELECT set_config('app.user_id', '22222222-2222-2222-2222-222222222222', true);
SELECT set_config('app.role_codes', 'director', true);

-- Debe ver solo Tenant B.
SELECT company_code FROM faro.companies;

-- Debe devolver solo SALES_B.
SELECT source_code, name FROM faro.data_sources ORDER BY source_code;

-- Debe devolver value = 2000000.
SELECT kpi_code, value FROM faro.kpi_snapshots WHERE kpi_code = 'KPI-SAL-001';

COMMIT;

BEGIN;

RESET app.company_id;
RESET app.user_id;
RESET app.role_codes;

-- Debe devolver 0 filas.
SELECT * FROM faro.data_sources;

-- Debe devolver 0 filas.
SELECT * FROM faro.kpi_snapshots;

COMMIT;

BEGIN;

SELECT set_config('app.company_id', '00000000-0000-0000-0000-000000000001', true);
SELECT set_config('app.user_id', '11111111-1111-1111-1111-111111111111', true);
SELECT set_config('app.role_codes', 'company_admin', true);

-- Debe fallar porque intenta insertar company_id de Tenant B
-- mientras el contexto es Tenant A.
INSERT INTO faro.data_sources (
  company_id, source_code, name,
  source_type, format_type, update_frequency, access_method, status
)
VALUES (
  '00000000-0000-0000-0000-000000000002',
  'ILLEGAL_SOURCE',
  'Fuente ilegal cruzada',
  'csv', 'csv', 'weekly', 'manual_upload', 'active'
);

-- Resultado esperado:
-- ERROR: new row violates row-level security policy

ROLLBACK;

-- Ver contexto actual.
SELECT * FROM faro.v_rls_context_debug;

-- Auditar tablas con RLS activo.
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'faro'
ORDER BY tablename;

-- Listar policies activas en el schema faro.
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'faro'
ORDER BY tablename, policyname;