-- Inicializacion de PostgreSQL
-- Crea esquemas, extensiones y configuraciones base

-- Esquemas del sistema
CREATE SCHEMA IF NOT EXISTS auth;
CREATE SCHEMA IF NOT EXISTS workspace;
CREATE SCHEMA IF NOT EXISTS messaging;
CREATE SCHEMA IF NOT EXISTS files;
CREATE SCHEMA IF NOT EXISTS automation;
CREATE SCHEMA IF NOT EXISTS analytics;
CREATE SCHEMA IF NOT EXISTS ai;

-- Extensiones
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "vector";

-- Configuracion
SET timezone = 'UTC';
ALTER DATABASE chatapp SET timezone TO 'UTC';

-- ============================================================================
-- ROW LEVEL SECURITY (RLS)
-- Defensa en profundidad: incluso si la API olvida filtrar por workspace_id,
-- la BD rechazara el acceso a datos de otros workspaces.
-- ============================================================================

-- Funcion helper: obtiene el workspace_id del contexto de sesion
-- Se setea en cada request via: SET LOCAL app.current_workspace_id = '<uuid>'
CREATE OR REPLACE FUNCTION current_workspace_id() RETURNS UUID AS $$
BEGIN
  RETURN NULLIF(current_setting('app.current_workspace_id', true), '')::UUID;
END;
$$ LANGUAGE plpgsql STABLE;

-- Funcion helper: obtiene el user_id del contexto de sesion
CREATE OR REPLACE FUNCTION current_user_id() RETURNS UUID AS $$
BEGIN
  RETURN NULLIF(current_setting('app.current_user_id', true), '')::UUID;
END;
$$ LANGUAGE plpgsql STABLE;

-- Habilitar RLS en tablas con workspace_id
-- messaging.channels
ALTER TABLE messaging.channels ENABLE ROW LEVEL SECURITY;
CREATE POLICY channels_workspace_isolation ON messaging.channels
  USING (workspace_id = current_workspace_id());

-- messaging.channel_members
ALTER TABLE messaging.channel_members ENABLE ROW LEVEL SECURITY;
CREATE POLICY channel_members_workspace_isolation ON messaging.channel_members
  USING (workspace_id = current_workspace_id());

-- messaging.messages
ALTER TABLE messaging.messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY messages_workspace_isolation ON messaging.messages
  USING (workspace_id = current_workspace_id());

-- messaging.message_reactions
ALTER TABLE messaging.message_reactions ENABLE ROW LEVEL SECURITY;
CREATE POLICY reactions_workspace_isolation ON messaging.message_reactions
  USING (EXISTS (
    SELECT 1 FROM messaging.messages m
    WHERE m.id = message_id AND m.workspace_id = current_workspace_id()
  ));

-- messaging.message_pins
ALTER TABLE messaging.message_pins ENABLE ROW LEVEL SECURITY;
CREATE POLICY pins_workspace_isolation ON messaging.message_pins
  USING (EXISTS (
    SELECT 1 FROM messaging.channels c
    WHERE c.id = channel_id AND c.workspace_id = current_workspace_id()
  ));

-- messaging.channel_bookmarks
ALTER TABLE messaging.channel_bookmarks ENABLE ROW LEVEL SECURITY;
CREATE POLICY bookmarks_workspace_isolation ON messaging.channel_bookmarks
  USING (EXISTS (
    SELECT 1 FROM messaging.channels c
    WHERE c.id = channel_id AND c.workspace_id = current_workspace_id()
  ));

-- messaging.saved_messages
ALTER TABLE messaging.saved_messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY saved_messages_user_isolation ON messaging.saved_messages
  USING (user_id = current_user_id());

-- messaging.scheduled_messages
ALTER TABLE messaging.scheduled_messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY scheduled_messages_workspace_isolation ON messaging.scheduled_messages
  USING (workspace_id = current_workspace_id());

-- messaging.shared_channels
ALTER TABLE messaging.shared_channels ENABLE ROW LEVEL SECURITY;
CREATE POLICY shared_channels_isolation ON messaging.shared_channels
  USING (source_workspace_id = current_workspace_id()
      OR target_workspace_id = current_workspace_id());

-- files.files
ALTER TABLE files.files ENABLE ROW LEVEL SECURITY;
CREATE POLICY files_workspace_isolation ON files.files
  USING (workspace_id = current_workspace_id());

-- files.file_shares
ALTER TABLE files.file_shares ENABLE ROW LEVEL SECURITY;
CREATE POLICY file_shares_workspace_isolation ON files.file_shares
  USING (EXISTS (
    SELECT 1 FROM files.files f
    WHERE f.id = file_id AND f.workspace_id = current_workspace_id()
  ));

-- files.file_comments
ALTER TABLE files.file_comments ENABLE ROW LEVEL SECURITY;
CREATE POLICY file_comments_workspace_isolation ON files.file_comments
  USING (EXISTS (
    SELECT 1 FROM files.files f
    WHERE f.id = file_id AND f.workspace_id = current_workspace_id()
  ));

-- workspace.workspaces
ALTER TABLE workspace.workspaces ENABLE ROW LEVEL SECURITY;
CREATE POLICY workspaces_member_isolation ON workspace.workspaces
  USING (EXISTS (
    SELECT 1 FROM workspace.workspace_members wm
    WHERE wm.workspace_id = id
      AND wm.user_id = current_user_id()
      AND wm.deactivated_at IS NULL
  ));

-- workspace.workspace_members
ALTER TABLE workspace.workspace_members ENABLE ROW LEVEL SECURITY;
CREATE POLICY workspace_members_isolation ON workspace.workspace_members
  USING (workspace_id = current_workspace_id());

-- workspace.user_groups
ALTER TABLE workspace.user_groups ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_groups_workspace_isolation ON workspace.user_groups
  USING (workspace_id = current_workspace_id());

-- workspace.lists
ALTER TABLE workspace.lists ENABLE ROW LEVEL SECURITY;
CREATE POLICY lists_workspace_isolation ON workspace.lists
  USING (workspace_id = current_workspace_id());

-- workspace.canvases
ALTER TABLE workspace.canvases ENABLE ROW LEVEL SECURITY;
CREATE POLICY canvases_workspace_isolation ON workspace.canvases
  USING (workspace_id = current_workspace_id());

-- workspace.huddles
ALTER TABLE workspace.huddles ENABLE ROW LEVEL SECURITY;
CREATE POLICY huddles_workspace_isolation ON workspace.huddles
  USING (workspace_id = current_workspace_id());

-- workspace.reminders
ALTER TABLE workspace.reminders ENABLE ROW LEVEL SECURITY;
CREATE POLICY reminders_user_isolation ON workspace.reminders
  USING (user_id = current_user_id());

-- workspace.notification_preferences
ALTER TABLE workspace.notification_preferences ENABLE ROW LEVEL SECURITY;
CREATE POLICY notif_prefs_user_isolation ON workspace.notification_preferences
  USING (user_id = current_user_id() AND workspace_id = current_workspace_id());

-- workspace.data_retention_policies
ALTER TABLE workspace.data_retention_policies ENABLE ROW LEVEL SECURITY;
CREATE POLICY retention_workspace_isolation ON workspace.data_retention_policies
  USING (workspace_id = current_workspace_id());

-- workspace.legal_holds
ALTER TABLE workspace.legal_holds ENABLE ROW LEVEL SECURITY;
CREATE POLICY legal_holds_workspace_isolation ON workspace.legal_holds
  USING (workspace_id = current_workspace_id());

-- workspace.information_barriers
ALTER TABLE workspace.information_barriers ENABLE ROW LEVEL SECURITY;
CREATE POLICY barriers_workspace_isolation ON workspace.information_barriers
  USING (workspace_id = current_workspace_id());

-- automation.workflows
ALTER TABLE automation.workflows ENABLE ROW LEVEL SECURITY;
CREATE POLICY workflows_workspace_isolation ON automation.workflows
  USING (workspace_id = current_workspace_id());

-- automation.apps
ALTER TABLE automation.apps ENABLE ROW LEVEL SECURITY;
CREATE POLICY apps_workspace_isolation ON automation.apps
  USING (workspace_id = current_workspace_id());

-- analytics.audit_logs
ALTER TABLE analytics.audit_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY audit_logs_workspace_isolation ON analytics.audit_logs
  USING (workspace_id = current_workspace_id());

-- ai.message_embeddings
ALTER TABLE ai.message_embeddings ENABLE ROW LEVEL SECURITY;
CREATE POLICY embeddings_workspace_isolation ON ai.message_embeddings
  USING (workspace_id = current_workspace_id());

-- ai.ai_summaries
ALTER TABLE ai.ai_summaries ENABLE ROW LEVEL SECURITY;
CREATE POLICY ai_summaries_workspace_isolation ON ai.ai_summaries
  USING (workspace_id = current_workspace_id());

-- Permisos: el rol de la aplicacion puede bypassar RLS cuando sea necesario
-- (ej. admin queries cross-workspace). Por defecto, RLS aplica.
-- GRANT BYPASSRLS ON DATABASE chatapp TO chatapp_admin;