-- ============================================================
-- EXENT SIGNAL DASHBOARD: Row Level Security
-- ============================================================
-- Executar no Supabase SQL Editor.
-- Laravel (postgres superuser) bypassa RLS automaticamente.
-- Estas policies protegem acesso via PostgREST / Supabase API.
--
-- RBAC atual:
--   - profiles: usuários (Exent + clientes), is_exent flag
--   - project_users: vínculo direto profile <-> project (fonte de verdade)
--   - profile_groups: grupos atribuídos ao usuário (metadata para UI)
--   - project_groups + project_group_items: definição dos grupos
--
-- Esquema legado (clients, client_project, fact_leads_daily) foi removido.
-- ============================================================

BEGIN;

-- ============================================================
-- STEP 0: Cleanup do schema legado
-- ============================================================
-- Tabelas que existiam no modelo antigo (ClientController) e não fazem
-- mais parte do app. Idempotente: roda mesmo em ambientes que já dropparam.


-- ============================================================
-- STEP 1: Helper Functions
-- ============================================================

CREATE OR REPLACE FUNCTION public.is_exent_user()
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid()
      AND is_exent = true
  );
$$;

CREATE OR REPLACE FUNCTION public.user_project_ids()
RETURNS SETOF uuid
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT project_id FROM public.project_users
  WHERE profile_id = auth.uid();
$$;

-- ============================================================
-- STEP 2: Performance Indexes
-- ============================================================

CREATE INDEX IF NOT EXISTS idx_profiles_id_is_exent
  ON public.profiles (id) WHERE is_exent = true;

CREATE INDEX IF NOT EXISTS idx_project_users_profile_id
  ON public.project_users (profile_id);

CREATE INDEX IF NOT EXISTS idx_project_users_profile_project
  ON public.project_users (profile_id, project_id);

CREATE INDEX IF NOT EXISTS idx_profile_groups_profile_id
  ON public.profile_groups (profile_id);

-- ============================================================
-- STEP 3: Enable RLS on All Tables
-- ============================================================

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.project_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.project_groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.project_group_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profile_groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.integration_credentials ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.credential_secrets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.integration_assets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.project_integrations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.sync_runs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.fact_marketing_daily ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.fact_crm_daily ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.dashboard_blocks ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.template_blocks ENABLE ROW LEVEL SECURITY;

-- ============================================================
-- STEP 4: RLS Policies
-- ============================================================

-- 4.1 profiles
CREATE POLICY profiles_select_own ON public.profiles
  FOR SELECT TO authenticated
  USING (id = auth.uid());

CREATE POLICY profiles_select_exent ON public.profiles
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

-- 4.2 projects
CREATE POLICY projects_select_exent ON public.projects
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY projects_select_client ON public.projects
  FOR SELECT TO authenticated
  USING (id IN (SELECT public.user_project_ids()));

-- 4.3 project_users
CREATE POLICY project_users_select_own ON public.project_users
  FOR SELECT TO authenticated
  USING (profile_id = auth.uid());

CREATE POLICY project_users_select_exent ON public.project_users
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY project_users_manage_exent ON public.project_users
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.4 project_groups (Exent gerencia; cliente lê grupos que contêm projetos seus)
CREATE POLICY project_groups_select_exent ON public.project_groups
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY project_groups_select_client ON public.project_groups
  FOR SELECT TO authenticated
  USING (EXISTS (
    SELECT 1 FROM public.project_group_items pgi
    WHERE pgi.group_id = project_groups.id
      AND pgi.project_id IN (SELECT public.user_project_ids())
  ));

CREATE POLICY project_groups_manage_exent ON public.project_groups
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.5 project_group_items (Exent gerencia; cliente lê itens dos seus projetos)
CREATE POLICY pgi_select_exent ON public.project_group_items
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY pgi_select_client ON public.project_group_items
  FOR SELECT TO authenticated
  USING (project_id IN (SELECT public.user_project_ids()));

CREATE POLICY pgi_manage_exent ON public.project_group_items
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.6 profile_groups (usuário vê os próprios; Exent gerencia)
CREATE POLICY profile_groups_select_own ON public.profile_groups
  FOR SELECT TO authenticated
  USING (profile_id = auth.uid());

CREATE POLICY profile_groups_select_exent ON public.profile_groups
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY profile_groups_manage_exent ON public.profile_groups
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.7 integration_credentials (Exent only)
CREATE POLICY ic_exent_only ON public.integration_credentials
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.8 credential_secrets (Exent only)
CREATE POLICY cs_exent_only ON public.credential_secrets
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.9 integration_assets (Exent only)
CREATE POLICY ia_exent_only ON public.integration_assets
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.10 project_integrations
CREATE POLICY pi_select_exent ON public.project_integrations
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY pi_select_client ON public.project_integrations
  FOR SELECT TO authenticated
  USING (project_id IN (SELECT public.user_project_ids()));

CREATE POLICY pi_manage_exent ON public.project_integrations
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.11 sync_runs
CREATE POLICY sr_select_exent ON public.sync_runs
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY sr_select_client ON public.sync_runs
  FOR SELECT TO authenticated
  USING (project_id IN (SELECT public.user_project_ids()));

CREATE POLICY sr_manage_exent ON public.sync_runs
  FOR ALL TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

-- 4.12 fact_marketing_daily
CREATE POLICY fmd_select_exent ON public.fact_marketing_daily
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY fmd_select_client ON public.fact_marketing_daily
  FOR SELECT TO authenticated
  USING (project_id IN (SELECT public.user_project_ids()));

CREATE POLICY fmd_insert_exent ON public.fact_marketing_daily
  FOR INSERT TO authenticated
  WITH CHECK (public.is_exent_user());

CREATE POLICY fmd_update_exent ON public.fact_marketing_daily
  FOR UPDATE TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

CREATE POLICY fmd_delete_exent ON public.fact_marketing_daily
  FOR DELETE TO authenticated
  USING (public.is_exent_user());

-- 4.13 fact_crm_daily
CREATE POLICY fcd_select_exent ON public.fact_crm_daily
  FOR SELECT TO authenticated
  USING (public.is_exent_user());

CREATE POLICY fcd_select_client ON public.fact_crm_daily
  FOR SELECT TO authenticated
  USING (project_id IN (SELECT public.user_project_ids()));

CREATE POLICY fcd_insert_exent ON public.fact_crm_daily
  FOR INSERT TO authenticated
  WITH CHECK (public.is_exent_user());

CREATE POLICY fcd_update_exent ON public.fact_crm_daily
  FOR UPDATE TO authenticated
  USING (public.is_exent_user())
  WITH CHECK (public.is_exent_user());

CREATE POLICY fcd_delete_exent ON public.fact_crm_daily
  FOR DELETE TO authenticated
  USING (public.is_exent_user());

-- 4.14 dashboard_blocks (read-only para todos autenticados)
CREATE POLICY db_select_authenticated ON public.dashboard_blocks
  FOR SELECT TO authenticated
  USING (true);

-- 4.15 templates (read-only para todos autenticados)
CREATE POLICY templates_select_authenticated ON public.templates
  FOR SELECT TO authenticated
  USING (true);

-- 4.16 template_blocks (read-only para todos autenticados)
CREATE POLICY tb_select_authenticated ON public.template_blocks
  FOR SELECT TO authenticated
  USING (true);

-- ============================================================
-- STEP 5: Lock Down Anon Role
-- ============================================================

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM anon;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM anon;

-- ============================================================
-- STEP 6: Recreate Views with security_invoker
-- ============================================================
-- NOTA: Ajustar as colunas conforme suas views reais.
-- Estas são templates baseadas no schema documentado.

DROP VIEW IF EXISTS public.vw_overview_daily;
DROP VIEW IF EXISTS public.vw_channel_performance_daily;
DROP VIEW IF EXISTS public.vw_campaign_performance_daily;

CREATE VIEW public.vw_overview_daily
WITH (security_invoker = true)
AS
SELECT
  fmd.project_id,
  fmd.metric_date,
  COALESCE(SUM(fmd.sessions), 0) AS total_sessions,
  COALESCE(SUM(fmd.users_count), 0) AS total_users,
  COALESCE(SUM(fmd.impressions), 0) AS total_impressions,
  COALESCE(SUM(fmd.clicks), 0) AS total_clicks,
  COALESCE(SUM(fmd.spend), 0) AS total_spend,
  COALESCE(SUM(fmd.conversions), 0) AS total_conversions
FROM public.fact_marketing_daily fmd
GROUP BY fmd.project_id, fmd.metric_date;

CREATE VIEW public.vw_channel_performance_daily
WITH (security_invoker = true)
AS
SELECT
  project_id,
  metric_date,
  channel,
  SUM(sessions) AS total_sessions,
  SUM(users_count) AS total_users,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  SUM(spend) AS total_spend,
  SUM(conversions) AS total_conversions
FROM public.fact_marketing_daily
GROUP BY project_id, metric_date, channel;

CREATE VIEW public.vw_campaign_performance_daily
WITH (security_invoker = true)
AS
SELECT
  project_id,
  metric_date,
  channel,
  campaign_name,
  SUM(sessions) AS total_sessions,
  SUM(users_count) AS total_users,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  SUM(spend) AS total_spend,
  SUM(conversions) AS total_conversions
FROM public.fact_marketing_daily
GROUP BY project_id, metric_date, channel, campaign_name;

-- ============================================================
-- STEP 7: Grant SELECT on Views to Authenticated
-- ============================================================

GRANT SELECT ON public.vw_overview_daily TO authenticated;
GRANT SELECT ON public.vw_channel_performance_daily TO authenticated;
GRANT SELECT ON public.vw_campaign_performance_daily TO authenticated;

COMMIT;
