const fetch = (...args) =>
  import("node-fetch").then(({ default: fetch }) => fetch(...args));

// ============ CONFIG ============
const BASE_URL = "https://maxxcarl-sqllite.hf.space";
// For local testing, use: "http://localhost:7860"

// ============ COLORS ============
const colors = {
  reset: "\x1b[0m",
  bright: "\x1b[1m",
  green: "\x1b[32m",
  blue: "\x1b[34m",
  yellow: "\x1b[33m",
  red: "\x1b[31m",
  cyan: "\x1b[36m",
};

function log(msg, color = "reset") {
  console.log(`${colors[color]}${msg}${colors.reset}`);
}

function header(msg) {
  log(`\n${"=".repeat(60)}`, "cyan");
  log(`  ${msg}`, "bright");
  log(`${"=".repeat(60)}\n`, "cyan");
}

// ============ API FUNCTIONS ============
async function apiCall(endpoint, method = "GET", body = null) {
  const url = `${BASE_URL}${endpoint}`;
  const options = {
    method,
    headers: { "Content-Type": "application/json" },
  };

  if (body) options.body = JSON.stringify(body);

  const response = await fetch(url, options);
  const data = await response.json();

  if (!response.ok) {
    throw new Error(data.detail || `API Error: ${response.status}`);
  }

  return data;
}

// ============ MAIN TEST FLOW ============
async function main() {
  try {
    log(`\n🚀 SQLite Multi-Project Tester`, "bright");
    log(`Base URL: ${BASE_URL}\n`, "yellow");

    // 1. Health Check
    header("1️⃣  HEALTH CHECK");
    try {
      const health = await apiCall("/api/v1/heartbeat");
      log(`✅ Server is healthy!`, "green");
      log(`Response: ${JSON.stringify(health, null, 2)}\n`, "cyan");
    } catch (e) {
      log(`❌ Server health check failed: ${e.message}`, "red");
      return;
    }

    // 2. Create Projects
    header("2️⃣  CREATE PROJECTS");
    const projects = ["ecommerce", "blog", "analytics"];

    for (const projectName of projects) {
      try {
        await apiCall("/api/v1/projects", "POST", {
          name: projectName,
          description: `${projectName} project database`,
        });
        log(`✅ Created project: ${projectName}`, "green");
      } catch (e) {
        log(`⚠️  Project ${projectName}: ${e.message}`, "yellow");
      }
    }

    // 3. Setup E-Commerce Project
    header("3️⃣  ECOMMERCE PROJECT - CREATE TABLES");
    const ecommerceSQL = [
      `CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        price REAL NOT NULL,
        stock INTEGER
      )`,
      `CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY,
        customer_name TEXT NOT NULL,
        total REAL,
        status TEXT DEFAULT 'pending'
      )`,
    ];

    for (const sql of ecommerceSQL) {
      try {
        await apiCall("/api/v1/execute", "POST", { sql, project: "ecommerce" });
        log(`  ✅ Table created`, "green");
      } catch (e) {
        log(`  ⚠️  ${e.message}`, "yellow");
      }
    }

    // 4. Insert E-Commerce Data
    header("4️⃣  ECOMMERCE PROJECT - INSERT DATA");
    const ecommerceInserts = [
      `INSERT INTO products (name, price, stock) VALUES ('iPhone 15', 999.99, 50)`,
      `INSERT INTO products (name, price, stock) VALUES ('MacBook Pro', 2499.99, 20)`,
      `INSERT INTO orders (customer_name, total, status) VALUES ('Alice', 999.99, 'shipped')`,
      `INSERT INTO orders (customer_name, total, status) VALUES ('Bob', 3499.98, 'pending')`,
    ];

    for (const sql of ecommerceInserts) {
      try {
        await apiCall("/api/v1/execute", "POST", { sql, project: "ecommerce" });
        log(`  ✅ Data inserted`, "green");
      } catch (e) {
        log(`  ⚠️  ${e.message}`, "yellow");
      }
    }

    // 5. Setup Blog Project
    header("5️⃣  BLOG PROJECT - CREATE TABLES");
    const blogSQL = [
      `CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT,
        author TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      `CREATE TABLE IF NOT EXISTS comments (
        id INTEGER PRIMARY KEY,
        post_id INTEGER,
        text TEXT,
        author TEXT
      )`,
    ];

    for (const sql of blogSQL) {
      try {
        await apiCall("/api/v1/execute", "POST", { sql, project: "blog" });
        log(`  ✅ Table created`, "green");
      } catch (e) {
        log(`  ⚠️  ${e.message}`, "yellow");
      }
    }

    // 6. Insert Blog Data
    header("6️⃣  BLOG PROJECT - INSERT DATA");
    const blogInserts = [
      `INSERT INTO posts (title, author, content) VALUES ('Getting Started with SQLite', 'Tech Writer', 'SQLite is awesome...')`,
      `INSERT INTO comments (post_id, author, text) VALUES (1, 'Reader1', 'Great article!')`,
    ];

    for (const sql of blogInserts) {
      try {
        await apiCall("/api/v1/execute", "POST", { sql, project: "blog" });
        log(`  ✅ Data inserted`, "green");
      } catch (e) {
        log(`  ⚠️  ${e.message}`, "yellow");
      }
    }

    // 7. Setup Analytics Project
    header("7️⃣  ANALYTICS PROJECT - CREATE TABLE");
    const analyticsSQL = `CREATE TABLE IF NOT EXISTS events (
      id INTEGER PRIMARY KEY,
      event_type TEXT,
      user_id INTEGER,
      timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )`;

    try {
      await apiCall("/api/v1/execute", "POST", {
        sql: analyticsSQL,
        project: "analytics",
      });
      log(`✅ Analytics table created`, "green");
    } catch (e) {
      log(`❌ ${e.message}`, "red");
    }

    // 8. List All Projects
    header("8️⃣  LIST ALL PROJECTS");
    try {
      const projects = await apiCall("/api/v1/projects");
      log(JSON.stringify(projects, null, 2), "cyan");
    } catch (e) {
      log(`❌ Error: ${e.message}`, "red");
    }

    // 9. Query E-Commerce Data
    header("9️⃣  QUERY ECOMMERCE - PRODUCTS");
    try {
      const result = await apiCall("/api/v1/query", "POST", {
        sql: "SELECT * FROM products WHERE price > 1000",
        project: "ecommerce",
      });
      log(JSON.stringify(result.data, null, 2), "cyan");
    } catch (e) {
      log(`❌ Error: ${e.message}`, "red");
    }

    // 10. Query Blog Data
    header("🔟 QUERY BLOG - POSTS WITH COMMENTS");
    try {
      const result = await apiCall("/api/v1/query", "POST", {
        sql: `SELECT p.title, p.author, c.text as comment
            FROM posts p LEFT JOIN comments c ON p.id = c.post_id`,
        project: "blog",
      });
      log(JSON.stringify(result.data, null, 2), "cyan");
    } catch (e) {
      log(`❌ Error: ${e.message}`, "red");
    }

    // 11. Schema Overview
    header("1️⃣1️⃣  DATABASE ORGANIZATION");
    try {
      log(
        `
📁 /data/ (Persistent HF Spaces Storage)
├── ecommerce.db
│   ├── products (id, name, price, stock)
│   └── orders (id, customer_name, total, status)
├── blog.db
│   ├── posts (id, title, content, author, created_at)
│   └── comments (id, post_id, text, author)
└── analytics.db
    └── events (id, event_type, user_id, timestamp)

✅ Each project is completely isolated
✅ Fast queries - data organized by concern
✅ Easy backups - one db file per project
      `,
        "cyan"
      );
    } catch (e) {
      log(`❌ Error: ${e.message}`, "red");
    }

    // 12. Summary
    header("✅ MULTI-PROJECT SETUP COMPLETE");
    log(
      `
    🎉 Three Isolated Projects Created!

    🛒 ECOMMERCE: products, orders
    ✍️  BLOG: posts, comments
    📊 ANALYTICS: events

    🚀 Fast & Organized Queries
    💾 Persistent Storage: /data/
    🔄 Easy to Scale & Manage
    `,
      "green"
    );
  } catch (error) {
    log(`\n❌ Fatal Error: ${error.message}`, "red");
    process.exit(1);
  }
}

// Run the tests
main().catch(console.error);