


SELECT pg_catalog.set_config('search_path', '', false);


CREATE EXTENSION IF NOT EXISTS "pg_cron" WITH SCHEMA "pg_catalog";






COMMENT ON SCHEMA "public" IS 'standard public schema';



CREATE EXTENSION IF NOT EXISTS "pg_net" WITH SCHEMA "public";






CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";






CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";






CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";






CREATE TYPE "public"."audit_event_type" AS ENUM (
    'LOGIN_SUCCESS',
    'LOGIN_FAILURE',
    'LOGOUT',
    'PERMISSION_CHANGE',
    'CONFIG_CHANGE',
    'DATA_EXPORT',
    'SYSTEM_ERROR'
);


ALTER TYPE "public"."audit_event_type" OWNER TO "postgres";


CREATE TYPE "public"."audit_severity" AS ENUM (
    'INFO',
    'WARN',
    'CRITICAL'
);


ALTER TYPE "public"."audit_severity" OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."handle_new_user"() RETURNS "trigger"
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
BEGIN
    BEGIN
        INSERT INTO public.profiles (id, email, display_name, photo_url)
        VALUES (
            NEW.id,
            NEW.email,
            COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'name', split_part(NEW.email, '@', 1)),
            NEW.raw_user_meta_data->>'avatar_url'
        );
    EXCEPTION WHEN OTHERS THEN
        -- Em caso de erro, apenas loga um aviso (WARNING) nos logs do Postgres
        -- mas NÃO aborta a transação, permitindo que o usuário seja criado.
        RAISE WARNING 'Erro ao criar perfil automaticamente para usuário %: %', NEW.id, SQLERRM;
    END;
    RETURN NEW;
END;
$$;


ALTER FUNCTION "public"."handle_new_user"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."handle_new_user_settings"() RETURNS "trigger"
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
BEGIN
    INSERT INTO public.settings (user_id, id, evolution_instances)
    VALUES (new.id, new.id, '[]'::jsonb)
    ON CONFLICT (user_id) DO NOTHING;
    RETURN new;
END;
$$;


ALTER FUNCTION "public"."handle_new_user_settings"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."is_super_admin"() RETURNS boolean
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM super_admins WHERE user_id = auth.uid()
    );
END;
$$;


ALTER FUNCTION "public"."is_super_admin"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."is_workspace_member"("target_ws_id" "uuid") RETURNS boolean
    LANGUAGE "sql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 
    FROM workspace_members 
    WHERE workspace_id = target_ws_id 
    AND user_id = auth.uid()
    AND status = 'active'
  );
$$;


ALTER FUNCTION "public"."is_workspace_member"("target_ws_id" "uuid") OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."is_workspace_member_secure"("ws_id" "uuid") RETURNS boolean
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
BEGIN
    -- Esta consulta roda com privilégios de sistema, sem acionar a policy novamente
    RETURN EXISTS (
        SELECT 1 FROM public.workspace_members 
        WHERE workspace_id = ws_id 
        AND user_id = auth.uid() 
        AND status = 'active'
    );
END;
$$;


ALTER FUNCTION "public"."is_workspace_member_secure"("ws_id" "uuid") OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."is_workspace_owner"("target_ws_id" "uuid") RETURNS boolean
    LANGUAGE "sql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
  SELECT EXISTS (
    SELECT 1 
    FROM workspaces 
    WHERE id = target_ws_id 
    AND owner_id = auth.uid()
  );
$$;


ALTER FUNCTION "public"."is_workspace_owner"("target_ws_id" "uuid") OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."sync_card_title_name"() RETURNS "trigger"
    LANGUAGE "plpgsql"
    AS $$
BEGIN
  -- Se name for nulo e title existir, usa title como name
  IF NEW.name IS NULL AND NEW.title IS NOT NULL THEN
    NEW.name := NEW.title;
  END IF;
  
  -- Se title for nulo e name existir, usa name como title (vice-versa)
  IF NEW.title IS NULL AND NEW.name IS NOT NULL THEN
    NEW.title := NEW.name;
  END IF;
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION "public"."sync_card_title_name"() OWNER TO "postgres";




CREATE TABLE IF NOT EXISTS "public"."activation_codes" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "code" "text" NOT NULL,
    "duration_days" integer DEFAULT 30,
    "max_uses" integer DEFAULT 1,
    "uses_count" integer DEFAULT 0,
    "is_active" boolean DEFAULT true,
    "expires_at" timestamp with time zone,
    "created_by" "uuid",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "notes" "text",
    "target_email" "text",
    "used_by_email" "text",
    "used_by" "uuid",
    "used" boolean DEFAULT false,
    "used_at" timestamp with time zone,
    "reseller_id" "uuid"
);


ALTER TABLE "public"."activation_codes" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."booking_settings" (
    "user_id" "uuid" NOT NULL,
    "enabled" boolean DEFAULT false,
    "title" "text",
    "description" "text",
    "duration_minutes" integer DEFAULT 60,
    "available_hours" "jsonb" DEFAULT '{}'::"jsonb",
    "blocked_dates" "text"[] DEFAULT '{}'::"text"[],
    "buffer_minutes" integer DEFAULT 0,
    "max_advance_days" integer DEFAULT 30,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "reminders" "jsonb",
    "availability" "jsonb" DEFAULT '[]'::"jsonb",
    "public_code" "text",
    "duration" integer DEFAULT 30,
    "business_name" "text",
    "professional_name" "text",
    "professional_role" "text",
    "professional_photo_url" "text",
    "logo_url" "text",
    "welcome_message" "text",
    "address" "text",
    "contact_phone" "text"
);


ALTER TABLE "public"."booking_settings" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."bookings" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "user_id" "uuid",
    "workspace_id" "uuid",
    "service_id" "text",
    "team_member_id" "uuid",
    "client_name" "text" NOT NULL,
    "client_phone" "text" NOT NULL,
    "client_email" "text",
    "date" "date" NOT NULL,
    "start_time" time without time zone NOT NULL,
    "end_time" time without time zone NOT NULL,
    "status" "text" DEFAULT 'confirmed'::"text",
    "notes" "text",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "booking_type" "text" DEFAULT 'personal'::"text",
    "duration" integer DEFAULT 30,
    "scheduled_at" timestamp with time zone,
    "reminders_sent" "jsonb" DEFAULT '[]'::"jsonb",
    CONSTRAINT "bookings_status_check" CHECK (("status" = ANY (ARRAY['confirmed'::"text", 'cancelled'::"text", 'completed'::"text", 'no_show'::"text"])))
);


ALTER TABLE "public"."bookings" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."branding_cache" (
    "id" "text" PRIMARY KEY DEFAULT 'current',
    "reseller_id" "uuid",
    "brand_name" "text" NOT NULL DEFAULT 'Respy',
    "logo_url" "text",
    "favicon_url" "text",
    "color_primary" "text" DEFAULT '#f97316',
    "color_secondary" "text" DEFAULT '#ea580c',
    "support_email" "text",
    "support_phone" "text",
    "footer_text" "text",
    "synced_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"()
);

ALTER TABLE "public"."branding_cache" OWNER TO "postgres";

INSERT INTO "public"."branding_cache" ("id", "brand_name", "color_primary", "color_secondary")
VALUES ('current', 'Respy', '#f97316', '#ea580c')
ON CONFLICT ("id") DO NOTHING;

ALTER TABLE "public"."branding_cache" ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anon pode ler branding"
  ON "public"."branding_cache" FOR SELECT TO anon USING (true);

CREATE POLICY "Todos podem ler branding"
  ON "public"."branding_cache" FOR SELECT TO authenticated USING (true);

CREATE POLICY "Service role pode atualizar branding"
  ON "public"."branding_cache" FOR ALL TO service_role USING (true) WITH CHECK (true);

GRANT SELECT ON TABLE "public"."branding_cache" TO "anon";
GRANT SELECT ON TABLE "public"."branding_cache" TO "authenticated";
GRANT ALL ON TABLE "public"."branding_cache" TO "service_role";


CREATE TABLE IF NOT EXISTS "public"."campaign_jobs" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "campaign_id" "uuid" NOT NULL,
    "card_id" "uuid",
    "phone" "text" NOT NULL,
    "status" "text" DEFAULT 'pending'::"text",
    "error_message" "text",
    "sent_at" timestamp with time zone,
    "created_at" timestamp with time zone DEFAULT "now"(),
    CONSTRAINT "campaign_jobs_status_check" CHECK (("status" = ANY (ARRAY['pending'::"text", 'sent'::"text", 'failed'::"text"])))
);


ALTER TABLE "public"."campaign_jobs" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."campaigns" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "workspace_id" "uuid",
    "name" "text" NOT NULL,
    "description" "text",
    "target_filter" "jsonb" DEFAULT '{}'::"jsonb",
    "scheduled_for" timestamp with time zone,
    "status" "text" DEFAULT 'draft'::"text",
    "stats" "jsonb" DEFAULT '{"sent": 0, "total": 0, "failed": 0}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "content" "jsonb" DEFAULT '{}'::"jsonb",
    "scheduled_at" timestamp with time zone,
    "selected_columns" "text"[],
    "selected_tag_ids" "text"[],
    "target_type" "text" DEFAULT 'column'::"text",
    "target_value" "text",
    "delay_seconds" integer DEFAULT 10,
    "instance_name" "text",
    "recurrence" "text" DEFAULT 'none'::"text",
    "next_execution_at" timestamp with time zone,
    "progress" "jsonb" DEFAULT '{"sent": 0, "total": 0, "failed": 0}'::"jsonb",
    "total" integer DEFAULT 0,
    "sent" integer DEFAULT 0,
    "failed" integer DEFAULT 0,
    "created_by" "uuid",
    "started_at" timestamp with time zone,
    "completed_at" timestamp with time zone,
    "last_executed_at" timestamp with time zone,
    CONSTRAINT "campaigns_status_check" CHECK (("status" = ANY (ARRAY['draft'::"text", 'scheduled'::"text", 'running'::"text", 'completed'::"text", 'cancelled'::"text"])))
);


ALTER TABLE "public"."campaigns" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."contacts" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "user_id" "uuid",
    "workspace_id" "text",
    "name" "text",
    "phone" "text",
    "email" "text",
    "source" "text",
    "notes" "text",
    "tags" "text"[],
    "assigned_to" "text",
    "assigned_to_name" "text",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "last_update_date" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."contacts" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."debug_logs" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "context" "text",
    "message" "text",
    "data" "jsonb" DEFAULT '{}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."debug_logs" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."global_settings" (
    "id" "text" NOT NULL,
    "value" "jsonb" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."global_settings" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kanban_card_activities" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "card_id" "uuid" NOT NULL,
    "user_id" "uuid",
    "action" "text" NOT NULL,
    "description" "text",
    "old_value" "text",
    "new_value" "text",
    "metadata" "jsonb" DEFAULT '{}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "timestamp" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."kanban_card_activities" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kanban_card_messages" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "card_id" "uuid" NOT NULL,
    "content" "text",
    "sender" "text" NOT NULL,
    "message_type" "text" DEFAULT 'text'::"text",
    "media_url" "text",
    "media_mime_type" "text",
    "external_id" "text",
    "is_read" boolean DEFAULT false,
    "metadata" "jsonb" DEFAULT '{}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "user_id" "uuid",
    CONSTRAINT "kanban_card_messages_message_type_check" CHECK (("message_type" = ANY (ARRAY['text'::"text", 'image'::"text", 'audio'::"text", 'video'::"text", 'document'::"text", 'sticker'::"text"]))),
    CONSTRAINT "kanban_card_messages_sender_check" CHECK (("sender" = ANY (ARRAY['user'::"text", 'contact'::"text", 'system'::"text", 'ai'::"text"])))
);


ALTER TABLE "public"."kanban_card_messages" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kanban_card_notes" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "card_id" "uuid" NOT NULL,
    "text" "text" NOT NULL,
    "author_id" "uuid" DEFAULT "auth"."uid"(),
    "author_name" "text"
);


ALTER TABLE "public"."kanban_card_notes" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."kanban_cards" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "workspace_id" "text",
    "assigned_to" "text",
    "name" "text",
    "phone" "text" NOT NULL,
    "email" "text",
    "photo_url" "text",
    "column_name" "text" DEFAULT 'Novo Lead'::"text",
    "column_order" integer DEFAULT 0,
    "custom_fields" "jsonb" DEFAULT '{}'::"jsonb",
    "last_message_at" timestamp with time zone,
    "is_ai_enabled" boolean DEFAULT false,
    "ai_context" "jsonb" DEFAULT '{}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "status" "text" DEFAULT '📥 Novo Lead'::"text",
    "instance_name" "text",
    "associated_instance" "text",
    "auto_created" boolean DEFAULT false,
    "follow_up_step" integer DEFAULT 0,
    "assigned_to_name" "text",
    "last_message_received_at" timestamp with time zone,
    "resolved" boolean DEFAULT false,
    "resolved_at" timestamp with time zone,
    "resolved_by" "uuid",
    "creation_date" timestamp with time zone DEFAULT "now"(),
    "last_update_date" timestamp with time zone DEFAULT "now"(),
    "description" "text",
    "ia_enabled" boolean DEFAULT false,
    "title" "text",
    "last_message_sent_timestamp" timestamp with time zone,
    "follow_up_anchor_at" timestamp with time zone,
    "address" "text",
    "website" "text",
    "rating" "text",
    "reviews" "text",
    "value" numeric DEFAULT 0,
    "tags" "jsonb" DEFAULT '[]'::"jsonb"
);


ALTER TABLE "public"."kanban_cards" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."member_presence" (
    "id" "text" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "workspace_id" "uuid",
    "status" "text" DEFAULT 'online'::"text",
    "last_seen" timestamp with time zone DEFAULT "now"(),
    CONSTRAINT "member_presence_status_check" CHECK (("status" = ANY (ARRAY['online'::"text", 'away'::"text", 'offline'::"text"])))
);


ALTER TABLE "public"."member_presence" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."notifications" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "user_id" "uuid",
    "title" "text" NOT NULL,
    "message" "text",
    "type" "text" DEFAULT 'info'::"text",
    "is_read" boolean DEFAULT false,
    "link" "text",
    "metadata" "jsonb" DEFAULT '{}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "card_id" "uuid"
);


ALTER TABLE "public"."notifications" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."partners" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "name" "text" NOT NULL,
    "email" "text" NOT NULL,
    "code" "text" NOT NULL,
    "commission_percent" numeric(5,2) DEFAULT 10,
    "is_active" boolean DEFAULT true,
    "total_referrals" integer DEFAULT 0,
    "total_earnings" numeric(10,2) DEFAULT 0,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "prefix" "text",
    "brand_name" "text",
    "logo_url" "text",
    "favicon_url" "text",
    "color_primary" "text" DEFAULT '#f97316',
    "color_secondary" "text" DEFAULT '#ea580c',
    "support_email" "text",
    "support_phone" "text",
    "footer_text" "text",
    "updated_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."partners" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."profiles" (
    "id" "uuid" NOT NULL,
    "email" "text" NOT NULL,
    "display_name" "text",
    "photo_url" "text",
    "phone" "text",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."profiles" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."security_audits" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "actor_id" "uuid",
    "event_type" "text" NOT NULL,
    "severity" "public"."audit_severity" DEFAULT 'INFO'::"public"."audit_severity",
    "ip_address" "text",
    "user_agent" "text",
    "metadata" "jsonb" DEFAULT '{}'::"jsonb",
    CONSTRAINT "event_type_length" CHECK (("char_length"("event_type") < 50))
);


ALTER TABLE "public"."security_audits" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."settings" (
    "user_id" "uuid" NOT NULL,
    "ia_enabled" boolean DEFAULT false,
    "ia_prompt" "text",
    "ia_model" "text" DEFAULT 'gpt-4'::"text",
    "instance_id" "text",
    "instance_name" "text",
    "instance_status" "text" DEFAULT 'disconnected'::"text",
    "webhook_url" "text",
    "notifications_enabled" boolean DEFAULT true,
    "business_hours" "jsonb" DEFAULT '{}'::"jsonb",
    "auto_reply_message" "text",
    "settings_json" "jsonb" DEFAULT '{}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "id" "uuid",
    "evolution_instances" "jsonb" DEFAULT '[]'::"jsonb",
    "webhook_id" "text",
    "follow_up" "jsonb",
    "follow_up_target_columns" "text"[],
    "follow_up_target_tag_ids" "text"[],
    "follow_up_instance_name" "text",
    "follow_up_auto_enabled" boolean DEFAULT false
);


ALTER TABLE "public"."settings" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."super_admins" (
    "user_id" "uuid" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."super_admins" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."tags" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "workspace_id" "uuid" NOT NULL,
    "name" "text" NOT NULL,
    "color" "text" DEFAULT '#6b7280'::"text",
    "description" "text",
    "created_by" "uuid"
);


ALTER TABLE "public"."tags" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."tickets" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "subject" "text" NOT NULL,
    "description" "text",
    "status" "text" DEFAULT 'open'::"text",
    "priority" "text" DEFAULT 'medium'::"text",
    "messages" "jsonb" DEFAULT '[]'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "resolved_at" timestamp with time zone,
    CONSTRAINT "tickets_priority_check" CHECK (("priority" = ANY (ARRAY['low'::"text", 'medium'::"text", 'high'::"text", 'urgent'::"text"]))),
    CONSTRAINT "tickets_status_check" CHECK (("status" = ANY (ARRAY['open'::"text", 'in_progress'::"text", 'resolved'::"text", 'closed'::"text"])))
);


ALTER TABLE "public"."tickets" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."workspace_booking_settings" (
    "workspace_id" "uuid" NOT NULL,
    "enabled" boolean DEFAULT false,
    "title" "text",
    "description" "text",
    "services" "jsonb" DEFAULT '[]'::"jsonb",
    "available_hours" "jsonb" DEFAULT '{}'::"jsonb",
    "blocked_dates" "text"[] DEFAULT '{}'::"text"[],
    "team_members" "jsonb" DEFAULT '[]'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "reminders" "jsonb",
    "availability" "jsonb" DEFAULT '[]'::"jsonb",
    "public_code" "text",
    "duration" integer DEFAULT 30,
    "business_name" "text",
    "professional_name" "text",
    "professional_role" "text",
    "professional_photo_url" "text",
    "logo_url" "text",
    "welcome_message" "text",
    "address" "text",
    "contact_phone" "text"
);


ALTER TABLE "public"."workspace_booking_settings" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."workspace_invites" (
    "id" "text" NOT NULL,
    "token" "text" NOT NULL,
    "workspace_id" "uuid" NOT NULL,
    "workspace_name" "text" NOT NULL,
    "role" "text" NOT NULL,
    "created_by" "uuid",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "expires_at" timestamp with time zone NOT NULL,
    "used" boolean DEFAULT false,
    "used_by" "uuid",
    "used_at" timestamp with time zone,
    "email" "text",
    CONSTRAINT "workspace_invites_role_check" CHECK (("role" = ANY (ARRAY['admin'::"text", 'attendant'::"text"])))
);


ALTER TABLE "public"."workspace_invites" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."workspace_members" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "workspace_id" "uuid" NOT NULL,
    "user_id" "uuid" NOT NULL,
    "role" "text" DEFAULT 'atendente'::"text" NOT NULL,
    "status" "text" DEFAULT 'pending'::"text" NOT NULL,
    "invited_by" "uuid",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "email" "text",
    "attendant_name" "text",
    "display_name" "text",
    CONSTRAINT "workspace_members_role_check" CHECK (("role" = ANY (ARRAY['owner'::"text", 'admin'::"text", 'attendant'::"text", 'active'::"text"]))),
    CONSTRAINT "workspace_members_status_check" CHECK (("status" = ANY (ARRAY['active'::"text", 'pending'::"text", 'inactive'::"text"])))
);


ALTER TABLE "public"."workspace_members" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."workspaces" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "owner_id" "uuid" NOT NULL,
    "name" "text" NOT NULL,
    "instance_id" "text",
    "instance_status" "text" DEFAULT 'disconnected'::"text",
    "settings" "jsonb" DEFAULT '{}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "max_atendentes" integer,
    "max_instancias" integer
);


ALTER TABLE "public"."workspaces" OWNER TO "postgres";


ALTER TABLE ONLY "public"."activation_codes"
    ADD CONSTRAINT "activation_codes_code_key" UNIQUE ("code");



ALTER TABLE ONLY "public"."activation_codes"
    ADD CONSTRAINT "activation_codes_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."booking_settings"
    ADD CONSTRAINT "booking_settings_pkey" PRIMARY KEY ("user_id");



ALTER TABLE ONLY "public"."bookings"
    ADD CONSTRAINT "bookings_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."campaign_jobs"
    ADD CONSTRAINT "campaign_jobs_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."campaigns"
    ADD CONSTRAINT "campaigns_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."contacts"
    ADD CONSTRAINT "contacts_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."debug_logs"
    ADD CONSTRAINT "debug_logs_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."global_settings"
    ADD CONSTRAINT "global_settings_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kanban_card_activities"
    ADD CONSTRAINT "kanban_card_activities_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kanban_card_messages"
    ADD CONSTRAINT "kanban_card_messages_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kanban_card_notes"
    ADD CONSTRAINT "kanban_card_notes_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."kanban_cards"
    ADD CONSTRAINT "kanban_cards_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."member_presence"
    ADD CONSTRAINT "member_presence_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."notifications"
    ADD CONSTRAINT "notifications_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."partners"
    ADD CONSTRAINT "partners_code_key" UNIQUE ("code");



ALTER TABLE ONLY "public"."partners"
    ADD CONSTRAINT "partners_email_key" UNIQUE ("email");



ALTER TABLE ONLY "public"."partners"
    ADD CONSTRAINT "partners_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."partners"
    ADD CONSTRAINT "partners_prefix_key" UNIQUE ("prefix");



ALTER TABLE ONLY "public"."profiles"
    ADD CONSTRAINT "profiles_email_key" UNIQUE ("email");



ALTER TABLE ONLY "public"."profiles"
    ADD CONSTRAINT "profiles_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."security_audits"
    ADD CONSTRAINT "security_audits_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."settings"
    ADD CONSTRAINT "settings_pkey" PRIMARY KEY ("user_id");



ALTER TABLE ONLY "public"."super_admins"
    ADD CONSTRAINT "super_admins_pkey" PRIMARY KEY ("user_id");



ALTER TABLE ONLY "public"."tags"
    ADD CONSTRAINT "tags_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."tickets"
    ADD CONSTRAINT "tickets_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."workspace_booking_settings"
    ADD CONSTRAINT "workspace_booking_settings_pkey" PRIMARY KEY ("workspace_id");



ALTER TABLE ONLY "public"."workspace_invites"
    ADD CONSTRAINT "workspace_invites_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."workspace_members"
    ADD CONSTRAINT "workspace_members_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."workspace_members"
    ADD CONSTRAINT "workspace_members_workspace_id_user_id_key" UNIQUE ("workspace_id", "user_id");



ALTER TABLE ONLY "public"."workspaces"
    ADD CONSTRAINT "workspaces_pkey" PRIMARY KEY ("id");



CREATE INDEX "contacts_phone_idx" ON "public"."contacts" USING "btree" ("phone");



CREATE INDEX "contacts_user_id_idx" ON "public"."contacts" USING "btree" ("user_id");



CREATE INDEX "idx_activities_card_id" ON "public"."kanban_card_activities" USING "btree" ("card_id");



CREATE INDEX "idx_bookings_date" ON "public"."bookings" USING "btree" ("date");



CREATE INDEX "idx_campaign_jobs_campaign_id" ON "public"."campaign_jobs" USING "btree" ("campaign_id");



CREATE INDEX "idx_kanban_cards_phone" ON "public"."kanban_cards" USING "btree" ("phone");



CREATE INDEX "idx_kanban_cards_user_id" ON "public"."kanban_cards" USING "btree" ("user_id");



CREATE INDEX "idx_kanban_cards_workspace_id" ON "public"."kanban_cards" USING "btree" ("workspace_id");



CREATE INDEX "idx_messages_card_id" ON "public"."kanban_card_messages" USING "btree" ("card_id");



CREATE UNIQUE INDEX "idx_messages_card_external_unique" ON "public"."kanban_card_messages" USING "btree" ("card_id", "external_id") WHERE ("external_id" IS NOT NULL);



CREATE INDEX "idx_messages_created_at" ON "public"."kanban_card_messages" USING "btree" ("created_at");



CREATE INDEX "idx_messages_user_id" ON "public"."kanban_card_messages" USING "btree" ("user_id");



CREATE INDEX "idx_workspace_invites_email" ON "public"."workspace_invites" USING "btree" ("email");



CREATE OR REPLACE TRIGGER "trg_sync_card_title_name" BEFORE INSERT OR UPDATE ON "public"."kanban_cards" FOR EACH ROW EXECUTE FUNCTION "public"."sync_card_title_name"();



ALTER TABLE ONLY "public"."activation_codes"
    ADD CONSTRAINT "activation_codes_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."activation_codes"
    ADD CONSTRAINT "activation_codes_reseller_id_fkey" FOREIGN KEY ("reseller_id") REFERENCES "public"."partners"("id");



CREATE INDEX IF NOT EXISTS "idx_activation_codes_reseller"
    ON "public"."activation_codes" ("reseller_id");



ALTER TABLE ONLY "public"."booking_settings"
    ADD CONSTRAINT "booking_settings_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."bookings"
    ADD CONSTRAINT "bookings_team_member_id_fkey" FOREIGN KEY ("team_member_id") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."bookings"
    ADD CONSTRAINT "bookings_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."campaign_jobs"
    ADD CONSTRAINT "campaign_jobs_campaign_id_fkey" FOREIGN KEY ("campaign_id") REFERENCES "public"."campaigns"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."campaign_jobs"
    ADD CONSTRAINT "campaign_jobs_card_id_fkey" FOREIGN KEY ("card_id") REFERENCES "public"."kanban_cards"("id") ON DELETE SET NULL;



ALTER TABLE ONLY "public"."campaigns"
    ADD CONSTRAINT "campaigns_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."campaigns"
    ADD CONSTRAINT "campaigns_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."contacts"
    ADD CONSTRAINT "contacts_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."kanban_card_activities"
    ADD CONSTRAINT "kanban_card_activities_card_id_fkey" FOREIGN KEY ("card_id") REFERENCES "public"."kanban_cards"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."kanban_card_activities"
    ADD CONSTRAINT "kanban_card_activities_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."kanban_card_messages"
    ADD CONSTRAINT "kanban_card_messages_card_id_fkey" FOREIGN KEY ("card_id") REFERENCES "public"."kanban_cards"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."kanban_card_messages"
    ADD CONSTRAINT "kanban_card_messages_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."kanban_cards"
    ADD CONSTRAINT "kanban_cards_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."member_presence"
    ADD CONSTRAINT "member_presence_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."member_presence"
    ADD CONSTRAINT "member_presence_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."notifications"
    ADD CONSTRAINT "notifications_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."profiles"
    ADD CONSTRAINT "profiles_id_fkey" FOREIGN KEY ("id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."security_audits"
    ADD CONSTRAINT "security_audits_actor_id_fkey" FOREIGN KEY ("actor_id") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."settings"
    ADD CONSTRAINT "settings_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."super_admins"
    ADD CONSTRAINT "super_admins_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."tags"
    ADD CONSTRAINT "tags_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."tickets"
    ADD CONSTRAINT "tickets_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."workspace_booking_settings"
    ADD CONSTRAINT "workspace_booking_settings_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."workspace_invites"
    ADD CONSTRAINT "workspace_invites_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."workspace_invites"
    ADD CONSTRAINT "workspace_invites_used_by_fkey" FOREIGN KEY ("used_by") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."workspace_invites"
    ADD CONSTRAINT "workspace_invites_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."workspace_members"
    ADD CONSTRAINT "workspace_members_invited_by_fkey" FOREIGN KEY ("invited_by") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."workspace_members"
    ADD CONSTRAINT "workspace_members_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."workspace_members"
    ADD CONSTRAINT "workspace_members_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."workspaces"
    ADD CONSTRAINT "workspaces_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;







CREATE POLICY "Access messages based on card access" ON "public"."kanban_card_messages" USING ((EXISTS ( SELECT 1
   FROM "public"."kanban_cards" "kc"
  WHERE ("kc"."id" = "kanban_card_messages"."card_id"))));



CREATE POLICY "Acesso Notas" ON "public"."kanban_card_notes" TO "authenticated" USING (true) WITH CHECK (true);



CREATE POLICY "Allow Insert for Authenticated" ON "public"."kanban_card_messages" FOR INSERT WITH CHECK (("auth"."role"() = 'authenticated'::"text"));



CREATE POLICY "Allow Select for Authenticated" ON "public"."kanban_card_messages" FOR SELECT USING (("auth"."role"() = 'authenticated'::"text"));



CREATE POLICY "Allow Update for Authenticated" ON "public"."kanban_card_messages" FOR UPDATE USING (("auth"."role"() = 'authenticated'::"text"));



CREATE POLICY "Allow all" ON "public"."debug_logs" USING (true) WITH CHECK (true);






CREATE POLICY "Dono ou SuperAdmin pode deletar bookings" ON "public"."bookings" FOR DELETE USING ((("user_id" = "auth"."uid"()) OR "public"."is_super_admin"()));



CREATE POLICY "Dono ou SuperAdmin pode gerenciar bookings" ON "public"."bookings" FOR UPDATE USING ((("user_id" = "auth"."uid"()) OR "public"."is_super_admin"()));



CREATE POLICY "Dono pode atualizar workspace" ON "public"."workspaces" FOR UPDATE USING ((("owner_id" = "auth"."uid"()) OR "public"."is_super_admin"()));



CREATE POLICY "Dono pode deletar workspace" ON "public"."workspaces" FOR DELETE USING ((("owner_id" = "auth"."uid"()) OR "public"."is_super_admin"()));



CREATE POLICY "Dono pode fazer CRUD em settings" ON "public"."settings" USING (("auth"."uid"() = "user_id")) WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Dono pode gerenciar suas booking settings" ON "public"."booking_settings" USING ((("user_id" = "auth"."uid"()) OR "public"."is_super_admin"()));



CREATE POLICY "Donos podem ver seus workspaces" ON "public"."workspaces" FOR SELECT USING (("owner_id" = "auth"."uid"()));



CREATE POLICY "Enable insert for authenticated users only" ON "public"."security_audits" FOR INSERT TO "authenticated" WITH CHECK (true);



CREATE POLICY "Enable insert for service role" ON "public"."security_audits" FOR INSERT TO "service_role" WITH CHECK (true);



CREATE POLICY "Enable select for service role only" ON "public"."security_audits" FOR SELECT TO "service_role" USING (true);






CREATE POLICY "Leitura pública de super_admins" ON "public"."super_admins" FOR SELECT USING (true);






CREATE POLICY "Membros do workspace podem ver perfis de outros membros" ON "public"."profiles" FOR SELECT USING ((EXISTS ( SELECT 1
   FROM ("public"."workspace_members" "wm1"
     JOIN "public"."workspace_members" "wm2" ON (("wm1"."workspace_id" = "wm2"."workspace_id")))
  WHERE (("wm1"."user_id" = "auth"."uid"()) AND ("wm2"."user_id" = "profiles"."id") AND ("wm1"."status" = 'active'::"text") AND ("wm2"."status" = 'active'::"text")))));



CREATE POLICY "Membros podem criar tags" ON "public"."tags" FOR INSERT TO "authenticated" WITH CHECK ((EXISTS ( SELECT 1
   FROM "public"."workspace_members" "wm"
  WHERE (("wm"."workspace_id" = "tags"."workspace_id") AND ("wm"."user_id" = "auth"."uid"()) AND ("wm"."status" = 'active'::"text")))));






CREATE POLICY "Membros podem deletar tags" ON "public"."tags" FOR DELETE TO "authenticated" USING ((EXISTS ( SELECT 1
   FROM "public"."workspace_members" "wm"
  WHERE (("wm"."workspace_id" = "tags"."workspace_id") AND ("wm"."user_id" = "auth"."uid"()) AND ("wm"."status" = 'active'::"text")))));






CREATE POLICY "Membros podem editar tags" ON "public"."tags" FOR UPDATE TO "authenticated" USING ((EXISTS ( SELECT 1
   FROM "public"."workspace_members" "wm"
  WHERE (("wm"."workspace_id" = "tags"."workspace_id") AND ("wm"."user_id" = "auth"."uid"()) AND ("wm"."status" = 'active'::"text")))));






CREATE POLICY "Membros podem ver colegas de workspace" ON "public"."workspace_members" FOR SELECT USING ((("user_id" = "auth"."uid"()) OR "public"."is_workspace_member_secure"("workspace_id") OR (EXISTS ( SELECT 1
   FROM "public"."workspaces" "ws"
  WHERE (("ws"."id" = "workspace_members"."workspace_id") AND ("ws"."owner_id" = "auth"."uid"())))) OR (EXISTS ( SELECT 1
   FROM "public"."super_admins"
  WHERE ("super_admins"."user_id" = "auth"."uid"())))));



CREATE POLICY "Membros podem ver settings do owner" ON "public"."settings" FOR SELECT TO "authenticated" USING ((("auth"."uid"() = "id") OR (EXISTS ( SELECT 1
   FROM ("public"."workspace_members" "wm"
     JOIN "public"."workspaces" "w" ON (("w"."id" = "wm"."workspace_id")))
  WHERE (("wm"."user_id" = "auth"."uid"()) AND ("w"."owner_id" = "settings"."id") AND ("wm"."status" = 'active'::"text"))))));



CREATE POLICY "Membros podem ver tags" ON "public"."tags" FOR SELECT TO "authenticated" USING ((EXISTS ( SELECT 1
   FROM "public"."workspace_members" "wm"
  WHERE (("wm"."workspace_id" = "tags"."workspace_id") AND ("wm"."user_id" = "auth"."uid"()) AND ("wm"."status" = 'active'::"text")))));






CREATE POLICY "Público pode ver booking_settings habilitadas" ON "public"."booking_settings" FOR SELECT USING (("enabled" = true));



CREATE POLICY "Público pode ver workspace booking habilitadas" ON "public"."workspace_booking_settings" FOR SELECT USING (("enabled" = true));



CREATE POLICY "Qualquer um pode criar atividades" ON "public"."kanban_card_activities" FOR INSERT WITH CHECK (true);



CREATE POLICY "Qualquer um pode criar bookings" ON "public"."bookings" FOR INSERT WITH CHECK (true);



CREATE POLICY "Qualquer um pode gerenciar presença" ON "public"."member_presence" USING (true);



CREATE POLICY "Qualquer um pode ver bookings" ON "public"."bookings" FOR SELECT USING (true);



CREATE POLICY "Qualquer um pode ver parceiros" ON "public"."partners" FOR SELECT USING (true);








CREATE POLICY "Qualquer usuário pode verificar se é superadmin" ON "public"."super_admins" FOR SELECT USING (("auth"."uid"() IS NOT NULL));



CREATE POLICY "SuperAdmin pode deletar tickets" ON "public"."tickets" FOR DELETE USING ("public"."is_super_admin"());



CREATE POLICY "SuperAdmins podem escrever global_settings" ON "public"."global_settings" USING ((EXISTS ( SELECT 1
   FROM "public"."super_admins"
  WHERE ("super_admins"."user_id" = "auth"."uid"())))) WITH CHECK ((EXISTS ( SELECT 1
   FROM "public"."super_admins"
  WHERE ("super_admins"."user_id" = "auth"."uid"()))));



CREATE POLICY "SuperAdmins podem gerenciar códigos" ON "public"."activation_codes" USING ("public"."is_super_admin"());



CREATE POLICY "SuperAdmins podem gerenciar parceiros" ON "public"."partners" USING ("public"."is_super_admin"());







CREATE POLICY "SuperAdmins podem gerenciar settings globais" ON "public"."global_settings" USING ("public"."is_super_admin"());



CREATE POLICY "SuperAdmins podem gerenciar superadmins" ON "public"."super_admins" USING ("public"."is_super_admin"());



CREATE POLICY "SuperAdmins podem ver todos tickets" ON "public"."tickets" FOR SELECT USING ("public"."is_super_admin"());



CREATE POLICY "SuperAdmins podem ver todos workspaces" ON "public"."workspaces" FOR SELECT USING ("public"."is_super_admin"());



CREATE POLICY "Todos podem ler global_settings" ON "public"."global_settings" FOR SELECT USING (true);



CREATE POLICY "Users can delete cards" ON "public"."kanban_cards" FOR DELETE USING (true);



CREATE POLICY "Users can delete own contacts" ON "public"."contacts" FOR DELETE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can delete own notifications" ON "public"."notifications" FOR DELETE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can insert cards" ON "public"."kanban_cards" FOR INSERT WITH CHECK (true);



CREATE POLICY "Users can insert own contacts" ON "public"."contacts" FOR INSERT WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can insert own notifications" ON "public"."notifications" FOR INSERT WITH CHECK (true);



CREATE POLICY "Users can insert own settings" ON "public"."settings" FOR INSERT WITH CHECK (("auth"."uid"() = "id"));



CREATE POLICY "Users can select cards" ON "public"."kanban_cards" FOR SELECT USING (true);



CREATE POLICY "Users can update cards" ON "public"."kanban_cards" FOR UPDATE USING (true);



CREATE POLICY "Users can update own contacts" ON "public"."contacts" FOR UPDATE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can update own notifications" ON "public"."notifications" FOR UPDATE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can update own settings" ON "public"."settings" FOR UPDATE USING (("auth"."uid"() = "id"));



CREATE POLICY "Users can view own contacts" ON "public"."contacts" FOR SELECT USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can view own notifications" ON "public"."notifications" FOR SELECT USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can view own settings" ON "public"."settings" FOR SELECT USING (("auth"."uid"() = "id"));



CREATE POLICY "Usuário ou SuperAdmin pode atualizar tickets" ON "public"."tickets" FOR UPDATE USING ((("user_id" = "auth"."uid"()) OR "public"."is_super_admin"()));









CREATE POLICY "Usuário pode criar tickets" ON "public"."tickets" FOR INSERT WITH CHECK (("user_id" = "auth"."uid"()));



CREATE POLICY "Usuário pode criar workspace próprio" ON "public"."workspaces" FOR INSERT WITH CHECK (("owner_id" = "auth"."uid"()));









CREATE POLICY "Usuário pode gerenciar suas settings" ON "public"."settings" USING ((("user_id" = "auth"."uid"()) OR "public"."is_super_admin"()));






CREATE POLICY "Usuário pode ver seus tickets" ON "public"."tickets" FOR SELECT USING (("user_id" = "auth"."uid"()));



CREATE POLICY "Usuário pode ver suas settings" ON "public"."settings" FOR SELECT USING ((("user_id" = "auth"."uid"()) OR "public"."is_super_admin"()));



CREATE POLICY "Usuários autenticados podem gerenciar jobs" ON "public"."campaign_jobs" USING (("auth"."uid"() IS NOT NULL));



CREATE POLICY "Usuários autenticados podem ver atividades" ON "public"."kanban_card_activities" FOR SELECT USING (("auth"."uid"() IS NOT NULL));



CREATE POLICY "Usuários autenticados podem ver campanhas" ON "public"."campaigns" FOR SELECT USING (("auth"."uid"() IS NOT NULL));



CREATE POLICY "Usuários autenticados podem ver presença" ON "public"."member_presence" FOR SELECT USING (("auth"."uid"() IS NOT NULL));



CREATE POLICY "Usuários podem atualizar campanhas" ON "public"."campaigns" FOR UPDATE USING (("auth"."uid"() IS NOT NULL));



CREATE POLICY "Usuários podem atualizar seu próprio perfil" ON "public"."profiles" FOR UPDATE USING (("auth"."uid"() = "id"));



CREATE POLICY "Usuários podem criar campanhas" ON "public"."campaigns" FOR INSERT WITH CHECK (("auth"."uid"() IS NOT NULL));



CREATE POLICY "Usuários podem deletar campanhas" ON "public"."campaigns" FOR DELETE USING (("auth"."uid"() IS NOT NULL));



CREATE POLICY "Usuários podem ver settings globais" ON "public"."global_settings" FOR SELECT USING (("auth"."uid"() IS NOT NULL));



CREATE POLICY "Usuários podem ver seu próprio perfil" ON "public"."profiles" FOR SELECT USING (("auth"."uid"() = "id"));



ALTER TABLE "public"."activation_codes" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "anyone_can_insert_bookings" ON "public"."bookings" FOR INSERT WITH CHECK (true);



CREATE POLICY "anyone_can_view_bookings" ON "public"."bookings" FOR SELECT USING (true);



CREATE POLICY "auth_create_invites" ON "public"."workspace_invites" FOR INSERT TO "authenticated" WITH CHECK (true);



CREATE POLICY "auth_update_invites" ON "public"."workspace_invites" FOR UPDATE TO "authenticated" USING (true);



ALTER TABLE "public"."booking_settings" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "booking_settings_all" ON "public"."booking_settings" USING (true);



ALTER TABLE "public"."bookings" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."campaign_jobs" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."campaigns" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "campaigns_all_access" ON "public"."campaigns" TO "authenticated" USING (true) WITH CHECK (true);



ALTER TABLE "public"."contacts" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."debug_logs" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."global_settings" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kanban_card_activities" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kanban_card_messages" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kanban_card_notes" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."kanban_cards" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."member_presence" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "members_delete_policy" ON "public"."workspace_members" FOR DELETE USING ("public"."is_workspace_owner"("workspace_id"));



CREATE POLICY "members_read_policy" ON "public"."workspace_members" FOR SELECT USING ((("user_id" = "auth"."uid"()) OR "public"."is_workspace_member"("workspace_id") OR "public"."is_workspace_owner"("workspace_id")));



CREATE POLICY "members_update_policy" ON "public"."workspace_members" FOR UPDATE USING ((("user_id" = "auth"."uid"()) OR "public"."is_workspace_owner"("workspace_id")));



CREATE POLICY "members_insert_policy" ON "public"."workspace_members" FOR INSERT WITH CHECK (
    -- Allow users to insert themselves (crucial for "Create Workspace" flow)
    (auth.uid() = user_id)
    OR
    -- Allow admins/owners to add other members
    EXISTS (
        SELECT 1 FROM public.workspace_members wm 
        WHERE wm.workspace_id = workspace_members.workspace_id 
        AND wm.user_id = auth.uid() 
        AND wm.status = 'active'
        AND wm.role IN ('owner', 'admin')
    )
);



ALTER TABLE "public"."notifications" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."partners" ENABLE ROW LEVEL SECURITY;





ALTER TABLE "public"."profiles" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "public_read_invites" ON "public"."workspace_invites" FOR SELECT USING (true);



CREATE POLICY "read_own_invites_email" ON "public"."workspace_invites" FOR SELECT TO "authenticated" USING (("email" = ("auth"."jwt"() ->> 'email'::"text")));





ALTER TABLE "public"."security_audits" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."settings" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."super_admins" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."tags" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."tickets" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "workspace_booking_all" ON "public"."workspace_booking_settings" USING (true);



ALTER TABLE "public"."workspace_booking_settings" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "workspace_booking_settings_all" ON "public"."workspace_booking_settings" USING (true);


ALTER TABLE "public"."workspace_invites" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."workspace_members" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."workspaces" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "workspaces_read_policy" ON "public"."workspaces" FOR SELECT USING ((("owner_id" = "auth"."uid"()) OR "public"."is_workspace_member"("id")));





ALTER PUBLICATION "supabase_realtime" OWNER TO "postgres";






ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."bookings";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."kanban_card_messages";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."kanban_cards";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."member_presence";






GRANT USAGE ON SCHEMA "public" TO "postgres";
GRANT USAGE ON SCHEMA "public" TO "anon";
GRANT USAGE ON SCHEMA "public" TO "authenticated";
GRANT USAGE ON SCHEMA "public" TO "service_role";

















































































































































































GRANT ALL ON FUNCTION "public"."handle_new_user"() TO "anon";
GRANT ALL ON FUNCTION "public"."handle_new_user"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."handle_new_user"() TO "service_role";



GRANT ALL ON FUNCTION "public"."handle_new_user_settings"() TO "anon";
GRANT ALL ON FUNCTION "public"."handle_new_user_settings"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."handle_new_user_settings"() TO "service_role";



GRANT ALL ON FUNCTION "public"."is_super_admin"() TO "anon";
GRANT ALL ON FUNCTION "public"."is_super_admin"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."is_super_admin"() TO "service_role";



GRANT ALL ON FUNCTION "public"."is_workspace_member"("target_ws_id" "uuid") TO "anon";
GRANT ALL ON FUNCTION "public"."is_workspace_member"("target_ws_id" "uuid") TO "authenticated";
GRANT ALL ON FUNCTION "public"."is_workspace_member"("target_ws_id" "uuid") TO "service_role";



GRANT ALL ON FUNCTION "public"."is_workspace_member_secure"("ws_id" "uuid") TO "anon";
GRANT ALL ON FUNCTION "public"."is_workspace_member_secure"("ws_id" "uuid") TO "authenticated";
GRANT ALL ON FUNCTION "public"."is_workspace_member_secure"("ws_id" "uuid") TO "service_role";



GRANT ALL ON FUNCTION "public"."is_workspace_owner"("target_ws_id" "uuid") TO "anon";
GRANT ALL ON FUNCTION "public"."is_workspace_owner"("target_ws_id" "uuid") TO "authenticated";
GRANT ALL ON FUNCTION "public"."is_workspace_owner"("target_ws_id" "uuid") TO "service_role";



GRANT ALL ON FUNCTION "public"."sync_card_title_name"() TO "anon";
GRANT ALL ON FUNCTION "public"."sync_card_title_name"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."sync_card_title_name"() TO "service_role";
























GRANT ALL ON TABLE "public"."activation_codes" TO "anon";
GRANT ALL ON TABLE "public"."activation_codes" TO "authenticated";
GRANT ALL ON TABLE "public"."activation_codes" TO "service_role";



GRANT ALL ON TABLE "public"."booking_settings" TO "anon";
GRANT ALL ON TABLE "public"."booking_settings" TO "authenticated";
GRANT ALL ON TABLE "public"."booking_settings" TO "service_role";



GRANT ALL ON TABLE "public"."bookings" TO "anon";
GRANT ALL ON TABLE "public"."bookings" TO "authenticated";
GRANT ALL ON TABLE "public"."bookings" TO "service_role";



GRANT ALL ON TABLE "public"."campaign_jobs" TO "anon";
GRANT ALL ON TABLE "public"."campaign_jobs" TO "authenticated";
GRANT ALL ON TABLE "public"."campaign_jobs" TO "service_role";



GRANT ALL ON TABLE "public"."campaigns" TO "anon";
GRANT ALL ON TABLE "public"."campaigns" TO "authenticated";
GRANT ALL ON TABLE "public"."campaigns" TO "service_role";



GRANT ALL ON TABLE "public"."contacts" TO "anon";
GRANT ALL ON TABLE "public"."contacts" TO "authenticated";
GRANT ALL ON TABLE "public"."contacts" TO "service_role";



GRANT ALL ON TABLE "public"."debug_logs" TO "anon";
GRANT ALL ON TABLE "public"."debug_logs" TO "authenticated";
GRANT ALL ON TABLE "public"."debug_logs" TO "service_role";



GRANT ALL ON TABLE "public"."global_settings" TO "anon";
GRANT ALL ON TABLE "public"."global_settings" TO "authenticated";
GRANT ALL ON TABLE "public"."global_settings" TO "service_role";



GRANT ALL ON TABLE "public"."kanban_card_activities" TO "anon";
GRANT ALL ON TABLE "public"."kanban_card_activities" TO "authenticated";
GRANT ALL ON TABLE "public"."kanban_card_activities" TO "service_role";



GRANT ALL ON TABLE "public"."kanban_card_messages" TO "anon";
GRANT ALL ON TABLE "public"."kanban_card_messages" TO "authenticated";
GRANT ALL ON TABLE "public"."kanban_card_messages" TO "service_role";



GRANT ALL ON TABLE "public"."kanban_card_notes" TO "anon";
GRANT ALL ON TABLE "public"."kanban_card_notes" TO "authenticated";
GRANT ALL ON TABLE "public"."kanban_card_notes" TO "service_role";



GRANT ALL ON TABLE "public"."kanban_cards" TO "anon";
GRANT ALL ON TABLE "public"."kanban_cards" TO "authenticated";
GRANT ALL ON TABLE "public"."kanban_cards" TO "service_role";



GRANT ALL ON TABLE "public"."member_presence" TO "anon";
GRANT ALL ON TABLE "public"."member_presence" TO "authenticated";
GRANT ALL ON TABLE "public"."member_presence" TO "service_role";



GRANT ALL ON TABLE "public"."notifications" TO "anon";
GRANT ALL ON TABLE "public"."notifications" TO "authenticated";
GRANT ALL ON TABLE "public"."notifications" TO "service_role";



GRANT ALL ON TABLE "public"."partners" TO "anon";
GRANT ALL ON TABLE "public"."partners" TO "authenticated";
GRANT ALL ON TABLE "public"."partners" TO "service_role";







GRANT ALL ON TABLE "public"."profiles" TO "anon";
GRANT ALL ON TABLE "public"."profiles" TO "authenticated";
GRANT ALL ON TABLE "public"."profiles" TO "service_role";



GRANT ALL ON TABLE "public"."security_audits" TO "anon";
GRANT ALL ON TABLE "public"."security_audits" TO "authenticated";
GRANT ALL ON TABLE "public"."security_audits" TO "service_role";



GRANT ALL ON TABLE "public"."settings" TO "anon";
GRANT ALL ON TABLE "public"."settings" TO "authenticated";
GRANT ALL ON TABLE "public"."settings" TO "service_role";



GRANT ALL ON TABLE "public"."super_admins" TO "anon";
GRANT ALL ON TABLE "public"."super_admins" TO "authenticated";
GRANT ALL ON TABLE "public"."super_admins" TO "service_role";



GRANT ALL ON TABLE "public"."tags" TO "anon";
GRANT ALL ON TABLE "public"."tags" TO "authenticated";
GRANT ALL ON TABLE "public"."tags" TO "service_role";



GRANT ALL ON TABLE "public"."tickets" TO "anon";
GRANT ALL ON TABLE "public"."tickets" TO "authenticated";
GRANT ALL ON TABLE "public"."tickets" TO "service_role";



GRANT ALL ON TABLE "public"."workspace_booking_settings" TO "anon";
GRANT ALL ON TABLE "public"."workspace_booking_settings" TO "authenticated";
GRANT ALL ON TABLE "public"."workspace_booking_settings" TO "service_role";



GRANT ALL ON TABLE "public"."workspace_invites" TO "anon";
GRANT ALL ON TABLE "public"."workspace_invites" TO "authenticated";
GRANT ALL ON TABLE "public"."workspace_invites" TO "service_role";



GRANT ALL ON TABLE "public"."workspace_members" TO "anon";
GRANT ALL ON TABLE "public"."workspace_members" TO "authenticated";
GRANT ALL ON TABLE "public"."workspace_members" TO "service_role";



GRANT ALL ON TABLE "public"."workspaces" TO "anon";
GRANT ALL ON TABLE "public"."workspaces" TO "authenticated";
GRANT ALL ON TABLE "public"."workspaces" TO "service_role";









ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "service_role";






ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "service_role";






ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "service_role";































-- ==============================================================================
-- 🛡ï¸ RESPY V2 - CONSOLIDATED FIXES & SECURITY HARDENING
-- ==============================================================================
-- Este script consolida todas as correções de permissão (RLS) e Storage.
-- Ele deve ser executado APÓS a criação das tabelas para limpar regras antigas
-- e aplicar o padrão seguro "Golden Standard".

-- ==============================================================================
-- 1. STORAGE: GARANTIR BUCKETS E POLÃTICAS
-- ==============================================================================

-- Garantir existência dos 5 Buckets Públicos
INSERT INTO storage.buckets (id, name, public) VALUES 
  ('booking', 'booking', true),
  ('campaigns', 'campaigns', true),
  ('chat-media', 'chat-media', true),
  ('uploads', 'uploads', true),
  ('workspaceChats', 'workspaceChats', true)
ON CONFLICT (id) DO UPDATE SET public = true;

-- Limpar Políticas Antigas de Storage (Aggressive Cleanup)
DROP POLICY IF EXISTS "Booking Images Public Read" ON storage.objects;
DROP POLICY IF EXISTS "Booking Images Auth Upload" ON storage.objects;
DROP POLICY IF EXISTS "Booking Images Owner Manage" ON storage.objects;
DROP POLICY IF EXISTS "Booking Images Owner Delete" ON storage.objects;
DROP POLICY IF EXISTS "Booking Public Read" ON storage.objects;
DROP POLICY IF EXISTS "Booking Auth Insert" ON storage.objects;
DROP POLICY IF EXISTS "Booking Auth Update" ON storage.objects;
DROP POLICY IF EXISTS "Booking Auth Delete" ON storage.objects;
DROP POLICY IF EXISTS "Public Read Access" ON storage.objects;
DROP POLICY IF EXISTS "Authenticated Insert" ON storage.objects;
DROP POLICY IF EXISTS "Public Read Access Uploads" ON storage.objects;
DROP POLICY IF EXISTS "Authenticated Insert Uploads" ON storage.objects;
DROP POLICY IF EXISTS "Public Read Access Chat Media" ON storage.objects;
DROP POLICY IF EXISTS "Authenticated Insert Chat Media" ON storage.objects;
-- Cleanup policies found in verification scan
DROP POLICY IF EXISTS "Booking Images Admin Upload" ON storage.objects;
DROP POLICY IF EXISTS "Booking Images Admin Delete" ON storage.objects;
DROP POLICY IF EXISTS "Booking Images Public Read 2" ON storage.objects;
DROP POLICY IF EXISTS "Booking Admin All" ON storage.objects;
DROP POLICY IF EXISTS "Review Images Public Read" ON storage.objects;
DROP POLICY IF EXISTS "Auth Upload Buckets" ON storage.objects;
DROP POLICY IF EXISTS "Public Access Buckets" ON storage.objects;
DROP POLICY IF EXISTS "Owner Delete" ON storage.objects;
DROP POLICY IF EXISTS "Owner Update" ON storage.objects;
DROP POLICY IF EXISTS "Owner Delete Buckets" ON storage.objects;

-- Criar Políticas UNIFICADAS por Bucket

-- A) Booking (Regras Específicas: Update/Delete livre para auth para suportar o componente atual)
CREATE POLICY "Booking Public Read" ON storage.objects FOR SELECT TO public USING (bucket_id = 'booking');
CREATE POLICY "Booking Auth Insert" ON storage.objects FOR INSERT TO authenticated WITH CHECK (bucket_id = 'booking');
CREATE POLICY "Booking Auth Update" ON storage.objects FOR UPDATE TO authenticated USING (bucket_id = 'booking');
CREATE POLICY "Booking Auth Delete" ON storage.objects FOR DELETE TO authenticated USING (bucket_id = 'booking');

-- B) Campaigns (Public Read, Auth Write)
CREATE POLICY "Campaigns Public Read" ON storage.objects FOR SELECT TO public USING (bucket_id = 'campaigns');
CREATE POLICY "Campaigns Auth Insert" ON storage.objects FOR INSERT TO authenticated WITH CHECK (bucket_id = 'campaigns');

-- C) Chat-Media (Public Read, Auth Write, Owner Manage)
CREATE POLICY "ChatMedia Public Read" ON storage.objects FOR SELECT TO public USING (bucket_id = 'chat-media');
CREATE POLICY "ChatMedia Auth Insert" ON storage.objects FOR INSERT TO authenticated WITH CHECK (bucket_id = 'chat-media');
CREATE POLICY "ChatMedia Owner Update" ON storage.objects FOR UPDATE TO authenticated USING (bucket_id = 'chat-media' AND auth.uid() = owner);
CREATE POLICY "ChatMedia Owner Delete" ON storage.objects FOR DELETE TO authenticated USING (bucket_id = 'chat-media' AND auth.uid() = owner);

-- D) Uploads (Public Read, Auth Write)
CREATE POLICY "Uploads Public Read" ON storage.objects FOR SELECT TO public USING (bucket_id = 'uploads');
CREATE POLICY "Uploads Auth Insert" ON storage.objects FOR INSERT TO authenticated WITH CHECK (bucket_id = 'uploads');

-- E) WorkspaceChats (Public Read, Auth Write)
CREATE POLICY "WorkspaceChats Public Read" ON storage.objects FOR SELECT TO public USING (bucket_id = 'workspaceChats');
CREATE POLICY "WorkspaceChats Auth Insert" ON storage.objects FOR INSERT TO authenticated WITH CHECK (bucket_id = 'workspaceChats');


-- ==============================================================================
-- 2. BOOKING TABLES: RLS CLEANUP & FIX
-- ==============================================================================

-- Habilitar RLS
ALTER TABLE public.booking_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.workspace_booking_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.bookings ENABLE ROW LEVEL SECURITY;

-- CLEANUP: booking_settings
DROP POLICY IF EXISTS "Public Read Booking Settings" ON public.booking_settings;
DROP POLICY IF EXISTS "Owner Manage Booking Settings" ON public.booking_settings;
DROP POLICY IF EXISTS "Dono pode gerenciar suas booking settings" ON public.booking_settings;
DROP POLICY IF EXISTS "Público pode ver booking_settings habilitadas" ON public.booking_settings;
DROP POLICY IF EXISTS "booking_settings_all" ON public.booking_settings; -- 🚨 Perigosa
DROP POLICY IF EXISTS "Users can insert their own settings" ON public.booking_settings;
DROP POLICY IF EXISTS "Users can update their own settings" ON public.booking_settings;
DROP POLICY IF EXISTS "Users can delete their own settings" ON public.booking_settings;
DROP POLICY IF EXISTS "Users can view their own settings" ON public.booking_settings;

-- FIX: booking_settings
CREATE POLICY "Public Read Booking Settings" ON public.booking_settings FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "Owner Manage Booking Settings" ON public.booking_settings FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- CLEANUP: workspace_booking_settings
DROP POLICY IF EXISTS "Public Read Workspace Settings" ON public.workspace_booking_settings;
DROP POLICY IF EXISTS "Team Read Workspace Settings" ON public.workspace_booking_settings;
DROP POLICY IF EXISTS "Admin Manage Workspace Settings" ON public.workspace_booking_settings;
DROP POLICY IF EXISTS "Público pode ver workspace booking habilitadas" ON public.workspace_booking_settings;
DROP POLICY IF EXISTS "workspace_booking_all" ON public.workspace_booking_settings; -- 🚨 Perigosa
DROP POLICY IF EXISTS "workspace_booking_settings_all" ON public.workspace_booking_settings; -- 🚨 Perigosa

-- FIX: workspace_booking_settings
CREATE POLICY "Public Read Workspace Settings" ON public.workspace_booking_settings FOR SELECT TO anon, authenticated USING (true);

CREATE POLICY "Team Read Workspace Settings" ON public.workspace_booking_settings FOR SELECT TO authenticated USING (
    EXISTS (SELECT 1 FROM public.workspace_members wm WHERE wm.workspace_id = workspace_booking_settings.workspace_id AND wm.user_id = auth.uid() AND wm.status = 'active')
);

CREATE POLICY "Admin Manage Workspace Settings" ON public.workspace_booking_settings FOR ALL TO authenticated USING (
    EXISTS (SELECT 1 FROM public.workspace_members wm WHERE wm.workspace_id = workspace_booking_settings.workspace_id AND wm.user_id = auth.uid() AND wm.status = 'active' AND wm.role IN ('owner', 'admin'))
) WITH CHECK (
    EXISTS (SELECT 1 FROM public.workspace_members wm WHERE wm.workspace_id = workspace_booking_settings.workspace_id AND wm.user_id = auth.uid() AND wm.status = 'active' AND wm.role IN ('owner', 'admin'))
);

-- CLEANUP: bookings
DROP POLICY IF EXISTS "Public Create Bookings" ON public.bookings;
DROP POLICY IF EXISTS "Public Read Bookings" ON public.bookings;
DROP POLICY IF EXISTS "Team Manage Bookings" ON public.bookings;
DROP POLICY IF EXISTS "Owner Manage Bookings" ON public.bookings;
DROP POLICY IF EXISTS "Dono ou SuperAdmin pode deletar bookings" ON public.bookings;
DROP POLICY IF EXISTS "Dono ou SuperAdmin pode gerenciar bookings" ON public.bookings;
DROP POLICY IF EXISTS "Qualquer um pode criar bookings" ON public.bookings;
DROP POLICY IF EXISTS "Qualquer um pode ver bookings" ON public.bookings;
DROP POLICY IF EXISTS "anyone_can_insert_bookings" ON public.bookings;
DROP POLICY IF EXISTS "anyone_can_view_bookings" ON public.bookings;
DROP POLICY IF EXISTS "Public Read Busy Slots" ON public.bookings;

-- FIX: bookings
CREATE POLICY "Public Create Bookings" ON public.bookings FOR INSERT TO anon, authenticated WITH CHECK (true);
CREATE POLICY "Public Read Bookings" ON public.bookings FOR SELECT TO anon, authenticated USING (true);

CREATE POLICY "Team Manage Bookings" ON public.bookings FOR ALL TO authenticated USING (
    (workspace_id IS NOT NULL AND EXISTS (SELECT 1 FROM public.workspace_members wm WHERE wm.workspace_id = bookings.workspace_id AND wm.user_id = auth.uid() AND wm.status = 'active'))
    OR (workspace_id IS NULL AND auth.uid() = user_id)
) WITH CHECK (
    (workspace_id IS NOT NULL AND EXISTS (SELECT 1 FROM public.workspace_members wm WHERE wm.workspace_id = bookings.workspace_id AND wm.user_id = auth.uid() AND wm.status = 'active'))
    OR (workspace_id IS NULL AND auth.uid() = user_id)
);

-- =====================================================
-- INTERNAL CRON (pg_cron + pg_net)
-- =====================================================

create or replace function public.trigger_internal_cron(
  p_path text,
  p_method text default 'POST'
)
returns void
language plpgsql
security definer
set search_path = public
as $fn$
declare
  cfg jsonb;
  base_url text;
  cron_secret text;
  headers jsonb;
  final_url text;
begin
  select value into cfg
  from public.global_settings
  where id = 'internal_cron_config';

  base_url := trim(coalesce(cfg->>'base_url', ''));
  cron_secret := trim(coalesce(cfg->>'cron_secret', ''));

  if base_url = '' then
    raise notice '[RESPY CRON] internal_cron_config.base_url not set; skipping %', p_path;
    return;
  end if;

  final_url := rtrim(base_url, '/') || p_path;
  headers := jsonb_build_object('Content-Type', 'application/json');

  if cron_secret <> '' then
    headers := headers
      || jsonb_build_object('Authorization', 'Bearer ' || cron_secret)
      || jsonb_build_object('X-Internal-Call', cron_secret);
  end if;

  if upper(coalesce(p_method, 'POST')) = 'GET' then
    perform net.http_get(
      url := final_url,
      headers := headers,
      timeout_milliseconds := 30000
    );
  else
    perform net.http_post(
      url := final_url,
      headers := headers,
      body := '{}'::jsonb,
      timeout_milliseconds := 30000
    );
  end if;
end;
$fn$;

create or replace function public.set_internal_cron_config(
  p_base_url text,
  p_cron_secret text default null
)
returns void
language plpgsql
security definer
set search_path = public
as $fn$
begin
  insert into public.global_settings (id, value, created_at, updated_at)
  values (
    'internal_cron_config',
    jsonb_build_object(
      'base_url', rtrim(trim(p_base_url), '/'),
      'cron_secret', coalesce(trim(p_cron_secret), '')
    ),
    now(),
    now()
  )
  on conflict (id)
  do update set
    value = excluded.value,
    updated_at = now();
end;
$fn$;

create or replace function public.ensure_respy_cron_jobs()
returns void
language plpgsql
security definer
set search_path = public
as $fn$
declare
  v_job_id bigint;
begin
  for v_job_id in
    select jobid
    from cron.job
    where jobname in (
      'respy_follow_up',
      'respy_booking_reminders',
      'respy_campaigns',
      'respy_media_cleanup'
    )
  loop
    perform cron.unschedule(v_job_id);
  end loop;

  perform cron.schedule(
    'respy_follow_up',
    '* * * * *',
    $cmd$select public.trigger_internal_cron('/api/cron/run-follow-up');$cmd$
  );

  perform cron.schedule(
    'respy_booking_reminders',
    '* * * * *',
    $cmd$select public.trigger_internal_cron('/api/cron/run-booking-reminders');$cmd$
  );

  perform cron.schedule(
    'respy_campaigns',
    '* * * * *',
    $cmd$select public.trigger_internal_cron('/api/cron/run-campaigns');$cmd$
  );

  perform cron.schedule(
    'respy_media_cleanup',
    '0 3 * * *',
    $cmd$select public.trigger_internal_cron('/api/cron/cleanup-media');$cmd$
  );
end;
$fn$;

grant execute on function public.trigger_internal_cron(text, text) to service_role;
grant execute on function public.set_internal_cron_config(text, text) to service_role;
grant execute on function public.ensure_respy_cron_jobs() to service_role;

-- Create/update cron jobs immediately when schema is applied.
-- If internal_cron_config.base_url is missing, jobs will run as no-op until
-- set_internal_cron_config() is called (for example via npm run setup:cron).
select public.ensure_respy_cron_jobs();
