Gobierno, seguridad, auditoria y permisos

-- Set session context (run within transaction)
SELECT set_config('app.company_id', $1, true);
SELECT set_config('app.user_id', $2, true);
SELECT set_config('app.role_codes', $3, true);

-- Examples for Empresa Demo Cuyo S.A.
-- $1 = '10000000-0000-0000-0000-000000000001'  (company_id)
-- $2 = '12000000-0000-0000-0000-000000000001'  (user_id)
-- $3 = 'director,general_manager'              (role_codes CSV)

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
    RAISE EXCEPTION 'app.company_id is not set';
  END IF;

  RETURN v_company_id::uuid;
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;
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 trim(v_roles) = '' THEN
    RETURN ARRAY[]::text[];
  END IF;

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

CREATE TABLE IF NOT EXISTS faro.permissions (
  permission_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  permission_code text NOT NULL UNIQUE,
  module_code text NOT NULL,
  resource_code text NOT NULL,
  action_code text NOT NULL,

  name text NOT NULL,
  description text NOT NULL,

  risk_level text NOT NULL DEFAULT 'medium' CHECK (
    risk_level IN ('low', 'medium', 'high', 'critical')
  ),

  is_system boolean NOT NULL DEFAULT true,
  is_active boolean NOT NULL DEFAULT true,

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_permissions_module
ON faro.permissions(module_code, is_active);

CREATE TABLE IF NOT EXISTS faro.roles (
  role_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NULL,
  -- company_id NULL solo para roles de sistema globales

  role_code text NOT NULL,
  name text NOT NULL,
  description text NOT NULL,

  role_scope text NOT NULL DEFAULT 'company' CHECK (
    role_scope IN ('system', 'company', 'area', 'branch')
  ),

  is_system boolean NOT NULL DEFAULT false,
  is_active boolean NOT NULL DEFAULT true,

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  UNIQUE(company_id, role_code)
);

CREATE TABLE IF NOT EXISTS faro.role_permissions (
  role_permission_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NULL,
  role_id uuid NOT NULL REFERENCES faro.roles(role_id),
  permission_id uuid NOT NULL REFERENCES faro.permissions(permission_id),

  granted boolean NOT NULL DEFAULT true,

  created_at timestamptz NOT NULL DEFAULT now(),

  UNIQUE(role_id, permission_id)
);

-- granted = false permite revocar explicitamente
-- un permiso heredado en una empresa especifica

CREATE TABLE IF NOT EXISTS faro.user_roles (
  user_role_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NOT NULL,
  user_id uuid NOT NULL,
  role_id uuid NOT NULL REFERENCES faro.roles(role_id),

  area_id uuid NULL,
  branch_id uuid NULL,

  is_active boolean NOT NULL DEFAULT true,

  assigned_by uuid NULL,
  assigned_at timestamptz NOT NULL DEFAULT now(),

  expires_at timestamptz NULL,

  created_at timestamptz NOT NULL DEFAULT now(),

  UNIQUE(company_id, user_id, role_id, area_id, branch_id)
);

CREATE INDEX IF NOT EXISTS idx_user_roles_company_user
ON faro.user_roles(company_id, user_id, is_active);

CREATE INDEX IF NOT EXISTS idx_user_roles_company_role
ON faro.user_roles(company_id, role_id, is_active);

CREATE OR REPLACE FUNCTION faro.has_permission(
  p_user_id uuid,
  p_company_id uuid,
  p_permission_code text
)
RETURNS boolean AS $$
DECLARE
  v_has_permission boolean;
BEGIN
  SELECT EXISTS (
    SELECT 1
    FROM faro.user_roles ur
    JOIN faro.roles r
      ON r.role_id = ur.role_id
     AND r.is_active = true
    JOIN faro.role_permissions rp
      ON rp.role_id = r.role_id
     AND rp.granted = true
    JOIN faro.permissions p
      ON p.permission_id = rp.permission_id
     AND p.is_active = true
    WHERE ur.company_id = p_company_id
      AND ur.user_id = p_user_id
      AND ur.is_active = true
      AND p.permission_code = p_permission_code
      AND (
        ur.expires_at IS NULL
        OR ur.expires_at > now()
      )
  )
  INTO v_has_permission;

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

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE IF NOT EXISTS audit.audit_log (
  audit_log_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NULL,
  user_id uuid NULL,

  actor_type text NOT NULL DEFAULT 'user' CHECK (
    actor_type IN ('user', 'system', 'integration', 'ai')
  ),

  action text NOT NULL,
  entity_schema text NULL,
  entity_table text NULL,
  entity_type text NULL,
  entity_id text NULL,

  risk_level text NOT NULL DEFAULT 'medium' CHECK (
    risk_level IN ('low', 'medium', 'high', 'critical')
  ),

  before_data jsonb NULL,
  after_data jsonb NULL,
  diff_data jsonb NULL,

  request_id text NULL,
  ip_address inet NULL,
  user_agent text NULL,

  source_module text NULL,
  source_reference text NULL,

  success boolean NOT NULL DEFAULT true,
  error_message text NULL,

  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,

  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_audit_log_company_time
ON audit.audit_log(company_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_audit_log_user_time
ON audit.audit_log(user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_audit_log_entity
ON audit.audit_log(company_id, entity_type, entity_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_audit_log_action
ON audit.audit_log(company_id, action, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_audit_log_risk
ON audit.audit_log(company_id, risk_level, created_at DESC);

CREATE OR REPLACE FUNCTION audit.log_event(
  p_company_id uuid,
  p_user_id uuid,
  p_actor_type text,
  p_action text,
  p_entity_schema text DEFAULT NULL,
  p_entity_table text DEFAULT NULL,
  p_entity_type text DEFAULT NULL,
  p_entity_id text DEFAULT NULL,
  p_risk_level text DEFAULT 'medium',
  p_before_data jsonb DEFAULT NULL,
  p_after_data jsonb DEFAULT NULL,
  p_diff_data jsonb DEFAULT NULL,
  p_source_module text DEFAULT NULL,
  p_source_reference text DEFAULT NULL,
  p_success boolean DEFAULT true,
  p_error_message text DEFAULT NULL,
  p_metadata jsonb DEFAULT '{}'::jsonb
)
RETURNS uuid AS $$
DECLARE
  v_audit_log_id uuid;
BEGIN
  INSERT INTO audit.audit_log (
    company_id, user_id, actor_type, action,
    entity_schema, entity_table, entity_type, entity_id,
    risk_level, before_data, after_data, diff_data,
    source_module, source_reference, success, error_message, metadata
  )
  VALUES (
    p_company_id, p_user_id, p_actor_type, p_action,
    p_entity_schema, p_entity_table, p_entity_type, p_entity_id,
    p_risk_level, p_before_data, p_after_data, p_diff_data,
    p_source_module, p_source_reference, p_success, p_error_message,
    COALESCE(p_metadata, '{}'::jsonb)
  )
  RETURNING audit_log_id INTO v_audit_log_id;

  RETURN v_audit_log_id;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE IF NOT EXISTS faro.api_keys (
  api_key_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NOT NULL,

  name text NOT NULL,
  key_prefix text NOT NULL,
  key_hash text NOT NULL,
  -- key_hash = bcrypt/argon2 sobre el token completo
  -- key_prefix = primeros 8 chars, para identificar visualmente

  scopes text[] NOT NULL DEFAULT ARRAY[]::text[],

  created_by uuid NULL,

  last_used_at timestamptz NULL,
  expires_at timestamptz NULL,

  is_active boolean NOT NULL DEFAULT true,

  created_at timestamptz NOT NULL DEFAULT now(),
  revoked_at timestamptz NULL,
  revoked_by uuid NULL,

  UNIQUE(company_id, key_prefix)
);

CREATE INDEX IF NOT EXISTS idx_api_keys_company
ON faro.api_keys(company_id, is_active);

CREATE TABLE IF NOT EXISTS audit.security_events (
  security_event_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NULL,
  user_id uuid NULL,

  event_type text NOT NULL CHECK (
    event_type IN (
      'login_success',
      'login_failed',
      'permission_denied',
      'rls_violation_attempt',
      'suspicious_download',
      'api_key_created',
      'api_key_revoked',
      'ai_policy_violation',
      'rate_limit_exceeded',
      'password_reset',
      'mfa_required',
      'mfa_failed',
      'session_expired'
    )
  ),

  severity text NOT NULL CHECK (
    severity IN ('low', 'medium', 'high', 'critical')
  ),

  description text NOT NULL,

  ip_address inet NULL,
  user_agent text NULL,

  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,

  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_security_events_company_time
ON audit.security_events(company_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_security_events_type
ON audit.security_events(event_type, severity, created_at DESC);

export async function setDbSessionContext(params: {
  client: any;
  companyId: string;
  userId: string;
  roleCodes: string[];
}) {
  await params.client.query(
    `SELECT set_config('app.company_id', $1, true)`,
    [params.companyId]
  );

  await params.client.query(
    `SELECT set_config('app.user_id', $1, true)`,
    [params.userId]
  );

  await params.client.query(
    `SELECT set_config('app.role_codes', $1, true)`,
    [params.roleCodes.join(",")]
  );
}

export async function requirePermission(params: {
  client: any;
  companyId: string;
  userId: string;
  permissionCode: string;
}) {
  const result = await params.client.query(
    `SELECT faro.has_permission($1, $2, $3) AS allowed`,
    [params.userId, params.companyId, params.permissionCode]
  );

  if (!result.rows[0]?.allowed) {
    const error = new Error(
      `PERMISSION_DENIED: ${params.permissionCode}`
    );
    (error as any).statusCode = 403;
    throw error;
  }
}

export async function auditEvent(params: {
  client: any;
  companyId: string;
  userId?: string | null;
  actorType?: "user" | "system" | "integration" | "ai";
  action: string;
  entityType?: string | null;
  entityId?: string | null;
  riskLevel?: "low" | "medium" | "high" | "critical";
  beforeData?: unknown;
  afterData?: unknown;
  sourceModule?: string | null;
  sourceReference?: string | null;
  metadata?: Record<string, unknown>;
}) {
  await params.client.query(
    `
    SELECT audit.log_event(
      $1, $2, $3, $4,
      NULL, NULL,
      $5, $6,
      $7,
      $8::jsonb, $9::jsonb, NULL,
      $10, $11,
      true, NULL,
      $12::jsonb
    )
    `,
    [
      params.companyId,
      params.userId ?? null,
      params.actorType ?? "user",
      params.action,
      params.entityType ?? null,
      params.entityId ?? null,
      params.riskLevel ?? "medium",
      params.beforeData ? JSON.stringify(params.beforeData) : null,
      params.afterData ? JSON.stringify(params.afterData) : null,
      params.sourceModule ?? null,
      params.sourceReference ?? null,
      JSON.stringify(params.metadata ?? {})
    ]
  );
}

export async function withFaroSecurity<T>(params: {
  request: Request;
  requiredPermission?: string;
  handler: (ctx: {
    client: any;
    session: { companyId: string; userId: string; roleCodes: string[] };
    requestId: string;
  }) => Promise<T>;
}) {
  const requestId = crypto.randomUUID();
  const session = await getSessionContext();

  if (!session?.companyId || !session?.userId) {
    throw new Error("UNAUTHORIZED");
  }

  const client = await db.connect();

  try {
    await client.query("BEGIN");

    await setDbSessionContext({
      client,
      companyId: session.companyId,
      userId: session.userId,
      roleCodes: session.roleCodes
    });

    if (params.requiredPermission) {
      await requirePermission({
        client,
        companyId: session.companyId,
        userId: session.userId,
        permissionCode: params.requiredPermission
      });
    }

    const result = await params.handler({ client, session, requestId });

    await client.query("COMMIT");
    return result;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

CREATE TABLE IF NOT EXISTS faro.data_retention_policies (
  data_retention_policy_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  company_id uuid NOT NULL,

  data_category text NOT NULL CHECK (
    data_category IN (
      'raw_data', 'staging_data', 'evidence_files',
      'reports', 'audit_logs', 'ai_requests',
      'notifications', 'execution_events'
    )
  ),

  retention_days integer NOT NULL,
  archive_after_days integer NULL,
  delete_after_days integer NULL,

  legal_hold boolean NOT NULL DEFAULT false,
  is_active boolean NOT NULL DEFAULT true,

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  UNIQUE(company_id, data_category)
);

{
  "error": "PERMISSION_DENIED",
  "message": "No tenes permiso para realizar esta accion.",
  "request_id": "req_a4f8c2..."
}