<?php
// socialmediamanager/app/db.php
function smm_base_dir(): string {
  // project root = one level above /app
  return realpath(__DIR__ . "/..") ?: (dirname(__DIR__));
}
function smm_config(): array {
  static $cfg = null;
  if ($cfg === null) {
    $cfg = require __DIR__ . "/config.php";
    $tz = $cfg["timezone"] ?? "America/New_York";
    date_default_timezone_set($tz);
  }
  return $cfg;
}

function smm_storage_path(string $relative): string {
  $cfg = smm_config();
  $dir = rtrim($cfg["storage_dir"], "/");
  return $dir . "/" . ltrim($relative, "/");
}

function smm_db(): PDO {
  static $pdo = null;
  if ($pdo) return $pdo;

  $dbPath = smm_storage_path("app.sqlite");
  if (!is_dir(dirname($dbPath))) @mkdir(dirname($dbPath), 0755, true);

  $pdo = new PDO("sqlite:" . $dbPath);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  smm_init_schema($pdo);
  smm_migrate_schema($pdo);
  return $pdo;
}

function smm_init_schema(PDO $pdo): void {
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS articles (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      slug TEXT NOT NULL,
      body_html TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'draft', -- draft|queued|published|archived
      site_profile_id INTEGER NULL,
      publish_at TEXT NULL,
      tags_json TEXT NULL,
      meta_json TEXT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
  ");

  $pdo->exec("
    CREATE TABLE IF NOT EXISTS sites (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      base_url TEXT NOT NULL,
      publish_dir TEXT NOT NULL,
      posts_index_path TEXT NOT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
  ");

  $pdo->exec("
    CREATE TABLE IF NOT EXISTS tools (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      key TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      type TEXT NOT NULL, -- builtin|openai
      config_json TEXT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
  ");

  $pdo->exec("
    CREATE TABLE IF NOT EXISTS agents (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      key TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      description TEXT NULL,
      preferred_tool_key TEXT NULL,
      config_json TEXT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
  ");

  $pdo->exec("
    CREATE TABLE IF NOT EXISTS runs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      route TEXT NOT NULL,
      input_json TEXT NOT NULL,
      output_json TEXT NOT NULL,
      created_at TEXT NOT NULL
    );
  ");


  $pdo->exec("
    CREATE TABLE IF NOT EXISTS share_packs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      article_id INTEGER NOT NULL,
      site_profile_id INTEGER NULL,
      platforms_json TEXT NOT NULL,
      url TEXT NOT NULL,
      caption TEXT NOT NULL,
      hashtags TEXT NOT NULL,
      full_post TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'due', -- due|done|archived
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
  ");
  $pdo->exec("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT NOT NULL);");
}

function smm_migrate_schema(PDO $pdo): void {
  // Seed tools/agents if empty
  $count = intval($pdo->query("SELECT COUNT(*) AS c FROM tools")->fetch(PDO::FETCH_ASSOC)["c"] ?? 0);
  if ($count === 0) {
    $now = smm_now_iso();
    $stmt = $pdo->prepare("INSERT INTO tools (key,name,type,config_json,created_at,updated_at) VALUES (:key,:name,:type,:cfg,:c,:u)");
    $stmt->execute([":key"=>"tagx",":name"=>"TagX (SEO Extractor)",":type"=>"builtin",":cfg"=>json_encode(["mode"=>"basic"]),":c"=>$now,":u"=>$now]);
    $stmt->execute([":key"=>"openai_draft",":name"=>"OpenAI Draft Writer",":type"=>"openai",":cfg"=>json_encode(["style"=>"blog_html"]),":c"=>$now,":u"=>$now]);
  }
  try {
  $now = smm_now_iso();
  $stmt = $pdo->prepare("
    INSERT OR IGNORE INTO tools (key,name,type,config_json,created_at,updated_at)
    VALUES (:key,:name,:type,:cfg,:c,:u)
  ");
  $stmt->execute([
    ":key"  => "zapier_webhook",
    ":name" => "Zapier Webhook (Published)",
    ":type" => "http",
    ":cfg"  => json_encode([
      "url" => "",
      "timeout_sec" => 25,
      "headers" => ["Content-Type" => "application/json"]
    ], JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE),
    ":c" => $now,
    ":u" => $now
  ]);
} catch (Throwable $e) {
  // don't block app
}
  $acount = intval($pdo->query("SELECT COUNT(*) AS c FROM agents")->fetch(PDO::FETCH_ASSOC)["c"] ?? 0);
  if ($acount === 0) {
    $now = smm_now_iso();
    $stmt = $pdo->prepare("INSERT INTO agents (key,name,description,preferred_tool_key,config_json,created_at,updated_at)
      VALUES (:key,:name,:d,:pt,:cfg,:c,:u)");
    $stmt->execute([":key"=>"writer",":name"=>"Writer Agent",":d"=>"Creates blog drafts from prompts.",":pt"=>"openai_draft",":cfg"=>json_encode(["tone"=>"helpful"]),":c"=>$now,":u"=>$now]);
    $stmt->execute([":key"=>"seo",":name"=>"SEO Agent",":d"=>"Generates meta + tags using TagX.",":pt"=>"tagx",":cfg"=>json_encode([]),":c"=>$now,":u"=>$now]);
    $stmt->execute([":key"=>"publisher",":name"=>"Publisher Agent",":d"=>"Schedules and publishes to sites.",":pt"=>"tagx",":cfg"=>json_encode([]),":c"=>$now,":u"=>$now]);

  // Seed settings defaults
  $stmt = $pdo->prepare("INSERT OR IGNORE INTO settings (key,value) VALUES (:k,:v)");
  $stmt->execute([":k"=>"auto_sharepack_after_publish",":v"=>"1"]);
  $stmt->execute([":k"=>"default_share_platforms",":v"=>json_encode(["facebook","instagram","tiktok","pinterest"])]);

  $stmt->execute([":k"=>"default_image_tool",":v"=>"openai_image"]);
  $stmt->execute([":k"=>"default_video_tool",":v"=>"openai_video"]);
  $stmt->execute([":k"=>"media_public_base",":v"=>"../storage/media"]); // relative from public/
  }

  // ---- Phase 9 additions: agents, accounts, oauth providers, cron queue ----
  $pdo->exec("CREATE TABLE IF NOT EXISTS agents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT '',
    config_json TEXT NOT NULL DEFAULT '{}',
    enabled INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS accounts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    platform TEXT NOT NULL,
    account_label TEXT NOT NULL DEFAULT '',
    access_token TEXT NOT NULL DEFAULT '',
    refresh_token TEXT NOT NULL DEFAULT '',
    expires_at TEXT NOT NULL DEFAULT '',
    scope TEXT NOT NULL DEFAULT '',
    extra_json TEXT NOT NULL DEFAULT '{}',
    enabled INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS oauth_providers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    platform TEXT NOT NULL,
    client_id TEXT NOT NULL DEFAULT '',
    client_secret TEXT NOT NULL DEFAULT '',
    auth_url TEXT NOT NULL DEFAULT '',
    token_url TEXT NOT NULL DEFAULT '',
    scopes TEXT NOT NULL DEFAULT '',
    extra_json TEXT NOT NULL DEFAULT '{}',
    enabled INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS cron_queue (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    job_type TEXT NOT NULL,
    payload_json TEXT NOT NULL DEFAULT '{}',
    run_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued',
    attempts INTEGER NOT NULL DEFAULT 0,
    last_error TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
  )");

}

function smm_now_iso(): string { return date("c"); }

function smm_log(string $msg, string $level="INFO"): void {
  $line = sprintf("[%s] [%s] %s\n", date("c"), $level, $msg);
  @file_put_contents(smm_storage_path("logs/app.log"), $line, FILE_APPEND);
}

function smm_json_response($data, int $code=200): void {
  http_response_code($code);
  header("Content-Type: application/json; charset=utf-8");
  echo json_encode($data, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE);
  exit;
}

function smm_read_json_body(): array {
  $raw = file_get_contents("php://input");
  if (!$raw) return [];
  $d = json_decode($raw, true);
  return is_array($d) ? $d : [];
}

function smm_slugify(string $text): string {
  $text = strtolower(trim($text));
  $text = preg_replace('/[^a-z0-9\s-]/', '', $text);
  $text = preg_replace('/\s+/', '-', $text);
  $text = preg_replace('/-+/', '-', $text);
  return trim($text, '-') ?: "post";
}

function smm_is_absolute_path(string $p): bool {
  if ($p === "") return false;
  if ($p[0] === "/" ) return true;
  // Windows drive (unlikely on cPanel)
  return preg_match('/^[A-Za-z]:\\/', $p) === 1;
}
