-- migrations/001_initial.sql
-- Full Content Machine SaaS schema


-- ─── PLANS ────────────────────────────────────────────────────────────────
CREATE TABLE plans (
  id            TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  name_ro       TEXT NOT NULL,
  price_usd     INTEGER NOT NULL, -- cents
  stripe_price_id TEXT,
  scripts_per_day INTEGER NOT NULL DEFAULT 3,
  posts_per_day   INTEGER NOT NULL DEFAULT 3,
  byok            BOOLEAN NOT NULL DEFAULT false, -- bring your own key
  white_label     BOOLEAN NOT NULL DEFAULT false,
  max_sub_clients INTEGER NOT NULL DEFAULT 0,
  features        JSONB NOT NULL DEFAULT '[]',
  features_ro     JSONB NOT NULL DEFAULT '[]',
  active          BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO plans VALUES
  ('starter', 'Starter', 'Starter', 2900, NULL, 3, 3, false, false, 0,
   '["3 video scripts/day","3 X posts/day","Blog + Newsletter","All 10 agents","14-day trial"]',
   '["3 scripturi video/zi","3 postări X/zi","Blog + Newsletter","Toți 10 agenții","Trial 14 zile"]',
   true, NOW()),
  ('pro', 'Pro', 'Pro', 7900, NULL, 5, 5, true, false, 0,
   '["5 video scripts/day","5 X posts/day","Blog + Newsletter","All 10 agents","Your own API key","Unlimited runs","14-day trial"]',
   '["5 scripturi video/zi","5 postări X/zi","Blog + Newsletter","Toți 10 agenții","Cheia ta API","Rulări nelimitate","Trial 14 zile"]',
   true, NOW()),
  ('agency', 'Agency', 'Agency', 19900, NULL, 10, 10, true, true, 10,
   '["10 video scripts/day","10 X posts/day","Blog + Newsletter","All 10 agents","Your own API key","White label","Up to 10 sub-clients","14-day trial"]',
   '["10 scripturi video/zi","10 postări X/zi","Blog + Newsletter","Toți 10 agenții","Cheia ta API","White label","Până la 10 sub-clienți","Trial 14 zile"]',
   true, NOW());

-- ─── USER PROFILES ────────────────────────────────────────────────────────
CREATE TABLE user_profiles (
  user_id         UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  niche           TEXT,
  brand_name      TEXT,
  target_audience TEXT,
  tone            TEXT,
  writing_style   TEXT,
  personality     TEXT,
  avoid_words     TEXT[] DEFAULT '{}',
  cta             TEXT,
  example_phrases TEXT[] DEFAULT '{}',
  scripts_per_day INTEGER,
  posts_per_day   INTEGER,
  newsletter_freq TEXT DEFAULT 'daily',
  schedule_intel  TEXT DEFAULT '0 5 * * *',
  schedule_run    TEXT DEFAULT '0 6 * * *',
  output_timezone TEXT DEFAULT 'UTC',
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── PIPELINE JOBS ────────────────────────────────────────────────────────
CREATE TABLE pipeline_runs (
  id              UUID PRIMARY KEY,
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status          TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','running','completed','failed')),
  triggered_by    TEXT NOT NULL DEFAULT 'schedule' CHECK (triggered_by IN ('schedule','manual')),
  provider_used   TEXT,
  agents_run      TEXT[] DEFAULT '{}',
  scripts_count   INTEGER DEFAULT 0,
  error_message   TEXT,
  started_at      TIMESTAMPTZ,
  completed_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── GENERATED CONTENT ────────────────────────────────────────────────────
CREATE TABLE generated_content (
  id              UUID PRIMARY KEY,
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  run_id          UUID REFERENCES pipeline_runs(id) ON DELETE SET NULL,
  content_type    TEXT NOT NULL CHECK (content_type IN ('script','post','blog','newsletter','thumbnail','clip','intel','outreach','performance')),
  title           TEXT,
  body            TEXT NOT NULL,
  metadata        JSONB DEFAULT '{}',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── USAGE TRACKING ───────────────────────────────────────────────────────
CREATE TABLE usage_logs (
  id              UUID PRIMARY KEY,
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  action          TEXT NOT NULL,
  tokens_used     INTEGER DEFAULT 0,
  provider        TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── PERFORMANCE DATA ─────────────────────────────────────────────────────
CREATE TABLE performance_data (
  id              UUID PRIMARY KEY,
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content_type    TEXT NOT NULL,
  title           TEXT,
  metric_name     TEXT NOT NULL,
  metric_value    NUMERIC,
  recorded_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── AUDIT LOG ────────────────────────────────────────────────────────────
CREATE TABLE audit_logs (
  id              UUID PRIMARY KEY,
  actor_id        UUID REFERENCES users(id),
  target_user_id  UUID REFERENCES users(id),
  action          TEXT NOT NULL,
  details         JSONB DEFAULT '{}',
  ip_address      TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_pipeline_runs_user ON pipeline_runs(user_id, created_at DESC);
CREATE INDEX idx_generated_content_user ON generated_content(user_id, created_at DESC);
CREATE INDEX idx_generated_content_type ON generated_content(user_id, content_type, created_at DESC);
CREATE INDEX idx_usage_logs_user ON usage_logs(user_id, created_at DESC);

-- ─── UPDATED_AT TRIGGER ──────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER profiles_updated_at BEFORE UPDATE ON user_profiles FOR EACH ROW EXECUTE PROCEDURE update_updated_at();
