schema.sql

CREATE TABLE IF NOT EXISTS meta_account (
  account_id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  currency TEXT DEFAULT 'BRL',
  timezone TEXT DEFAULT 'America/Sao_Paulo',
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS meta_ad_metrics (
  ad_id TEXT NOT NULL,
  ad_name TEXT,
  adset_id TEXT,
  adset_name TEXT,
  campaign_id TEXT NOT NULL,
  campaign_name TEXT,
  account_id TEXT,
  spend REAL NOT NULL DEFAULT 0,
  impressions INTEGER DEFAULT 0,
  cpm REAL DEFAULT 0,
  clicks INTEGER DEFAULT 0,
  ctr REAL DEFAULT 0,
  reach INTEGER DEFAULT 0,
  frequency REAL DEFAULT 0,
  actions_results INTEGER DEFAULT 0,
  cost_per_result REAL DEFAULT 0,
  inline_link_clicks INTEGER DEFAULT 0,
  inline_link_click_ctr REAL DEFAULT 0,
  date_ref TEXT NOT NULL,
  synced_at TEXT DEFAULT (datetime('now')),
  PRIMARY KEY (ad_id, date_ref)
);

CREATE INDEX IF NOT EXISTS idx_metrics_date ON meta_ad_metrics(date_ref);
CREATE INDEX IF NOT EXISTS idx_metrics_campaign ON meta_ad_metrics(campaign_id);
CREATE INDEX IF NOT EXISTS idx_metrics_campaign_date ON meta_ad_metrics(campaign_id, date_ref);

CREATE TABLE IF NOT EXISTS meta_financeiro (
  account_id TEXT NOT NULL,
  month_start TEXT NOT NULL,
  month_end TEXT NOT NULL,
  spend REAL NOT NULL DEFAULT 0,
  tax REAL NOT NULL DEFAULT 0,
  total REAL NOT NULL DEFAULT 0,
  updated_at TEXT DEFAULT (datetime('now')),
  PRIMARY KEY (account_id, month_start)
);