metaads-pro/supabase/migration.sql

-- ============================================
-- MetaAds Pro — Supabase Migration
-- Execute this in Supabase SQL Editor
-- ============================================

-- 1. Automation Rules
CREATE TABLE IF NOT EXISTS automation_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  enabled BOOLEAN DEFAULT true,
  metric TEXT NOT NULL CHECK (metric IN ('cpl', 'cpc', 'ctr', 'roas', 'cpm', 'spend')),
  operator TEXT NOT NULL CHECK (operator IN ('>', '<', '>=', '<=')),
  threshold NUMERIC NOT NULL,
  min_spend NUMERIC DEFAULT 0,
  min_impressions INTEGER DEFAULT 0,
  action TEXT NOT NULL CHECK (action IN ('pause', 'reduce_budget', 'increase_budget', 'notify')),
  action_value NUMERIC,
  cooldown_hours INTEGER DEFAULT 24,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- 2. Agent Activity Log
CREATE TABLE IF NOT EXISTS agent_activity_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rule_id UUID REFERENCES automation_rules(id) ON DELETE SET NULL,
  campaign_id TEXT NOT NULL,
  campaign_name TEXT NOT NULL,
  action TEXT NOT NULL,
  details JSONB DEFAULT '{}',
  status TEXT DEFAULT 'executed' CHECK (status IN ('executed', 'pending', 'failed', 'skipped')),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 3. Agent-Created Campaigns
CREATE TABLE IF NOT EXISTS agent_campaigns (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  meta_campaign_id TEXT,
  meta_adset_id TEXT,
  meta_creative_id TEXT,
  meta_ad_id TEXT,
  product TEXT NOT NULL,
  audience TEXT,
  objective TEXT,
  budget_daily NUMERIC,
  copy_variations JSONB DEFAULT '[]',
  image_urls JSONB DEFAULT '[]',
  targeting JSONB DEFAULT '{}',
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'pending', 'published', 'active', 'paused', 'failed')),
  approved_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 4. Indexes
CREATE INDEX IF NOT EXISTS idx_activity_campaign ON agent_activity_log(campaign_id);
CREATE INDEX IF NOT EXISTS idx_activity_created ON agent_activity_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_activity_status ON agent_activity_log(status);
CREATE INDEX IF NOT EXISTS idx_rules_enabled ON automation_rules(enabled);
CREATE INDEX IF NOT EXISTS idx_campaigns_status ON agent_campaigns(status);

-- 5. Row Level Security (RLS) — adjust per your needs
ALTER TABLE automation_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_activity_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_campaigns ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users full access (single-tenant setup)
CREATE POLICY "Allow all for authenticated" ON automation_rules FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Allow all for authenticated" ON agent_activity_log FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Allow all for authenticated" ON agent_campaigns FOR ALL USING (auth.role() = 'authenticated');

-- 6. Insert default rules
INSERT INTO automation_rules (name, metric, operator, threshold, min_spend, min_impressions, action, cooldown_hours, enabled) VALUES
  ('CPL Alto — Pausar', 'cpl', '>', 50, 30, 1000, 'pause', 24, true),
  ('ROAS Baixo — Pausar', 'roas', '<', 0.5, 50, 2000, 'pause', 24, true),
  ('CTR Muito Baixo — Pausar', 'ctr', '<', 0.5, 20, 2000, 'pause', 24, true),
  ('CPC Alto — Reduzir Budget 30%', 'cpc', '>', 5, 30, 1000, 'reduce_budget', 48, false),
  ('ROAS Excelente — Aumentar Budget 20%', 'roas', '>', 3, 100, 5000, 'increase_budget', 48, false)
ON CONFLICT DO NOTHING;