import "dotenv/config";
import mysql from "mysql2/promise";
import { parse } from "url";

const dbUrl = process.env.DATABASE_URL!;
const parsed = new URL(dbUrl);

async function main() {
  const conn = await mysql.createConnection({
    host: parsed.hostname,
    port: Number(parsed.port) || 4000,
    user: decodeURIComponent(parsed.username),
    password: decodeURIComponent(parsed.password),
    database: parsed.pathname.slice(1),
    ssl: { rejectUnauthorized: true },
  });

  const statements = [
    `CREATE TABLE IF NOT EXISTS wishlists (
      id INT AUTO_INCREMENT PRIMARY KEY,
      session_id VARCHAR(255) NOT NULL,
      user_id BIGINT UNSIGNED,
      product_id BIGINT UNSIGNED NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );`,
    `CREATE TABLE IF NOT EXISTS order_tracking (
      id INT AUTO_INCREMENT PRIMARY KEY,
      order_id BIGINT UNSIGNED NOT NULL,
      status ENUM('pending','confirmed','processing','delivered','cancelled') NOT NULL,
      note TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );`,
    `CREATE TABLE IF NOT EXISTS coupons (
      id INT AUTO_INCREMENT PRIMARY KEY,
      code VARCHAR(50) NOT NULL UNIQUE,
      type ENUM('percentage','fixed') DEFAULT 'percentage',
      value DECIMAL(10,2) NOT NULL,
      min_order_amount DECIMAL(10,2) DEFAULT 0,
      max_uses INT DEFAULT 999,
      used_count INT DEFAULT 0,
      start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      end_date TIMESTAMP NULL,
      is_active BOOLEAN DEFAULT TRUE,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );`,
    `CREATE TABLE IF NOT EXISTS blog_posts (
      id INT AUTO_INCREMENT PRIMARY KEY,
      slug VARCHAR(255) NOT NULL UNIQUE,
      title VARCHAR(255) NOT NULL,
      excerpt TEXT,
      content TEXT NOT NULL,
      cover_image VARCHAR(500),
      category VARCHAR(100),
      tags JSON,
      author VARCHAR(100) DEFAULT 'Subscription Hero',
      meta_title VARCHAR(255),
      meta_desc VARCHAR(500),
      is_published BOOLEAN DEFAULT TRUE,
      view_count INT DEFAULT 0,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );`,
    `CREATE TABLE IF NOT EXISTS site_settings (
      id INT AUTO_INCREMENT PRIMARY KEY,
      \`key\` VARCHAR(100) NOT NULL UNIQUE,
      value TEXT,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );`,
    `ALTER TABLE products ADD COLUMN IF NOT EXISTS is_featured BOOLEAN DEFAULT FALSE;`,
    `ALTER TABLE products ADD COLUMN IF NOT EXISTS stock INT DEFAULT 999;`,
    `ALTER TABLE orders ADD COLUMN IF NOT EXISTS coupon_code VARCHAR(50);`,
    `ALTER TABLE orders ADD COLUMN IF NOT EXISTS discount_amount DECIMAL(10,2) DEFAULT 0;`,
    `INSERT IGNORE INTO coupons (code, type, value, min_order_amount, max_uses) VALUES ('HERO20', 'percentage', 20.00, 10.00, 1000);`,
    `INSERT IGNORE INTO coupons (code, type, value, min_order_amount, max_uses) VALUES ('SAVE15', 'percentage', 15.00, 5.00, 500);`,
    `INSERT IGNORE INTO coupons (code, type, value, min_order_amount, max_uses) VALUES ('WELCOME10', 'percentage', 10.00, 0.00, 999);`,
    `INSERT IGNORE INTO blog_posts (slug, title, excerpt, content, category, tags, cover_image, meta_title, meta_desc) VALUES ('best-ai-tools-2025', 'Best AI Tools to Boost Your Productivity in 2025', 'Discover the top AI tools that can transform your workflow and save you hours every day.', '<h2>Why AI Tools Matter in 2025</h2><p>Artificial Intelligence has revolutionized how we work. From content creation to code generation, AI tools are becoming essential.</p><h2>Top Picks</h2><ul><li>ChatGPT Plus - Best for general AI assistance</li><li>Midjourney - Best for image generation</li><li>Claude - Best for long-form writing</li></ul>', 'AI Tools', '["ai","productivity","tools"]', '/hero-bg.jpg', 'Best AI Tools 2025 | Subscription Hero', 'Discover top AI tools for productivity in 2025. ChatGPT, Midjourney, Claude and more at discounted prices.');`,
    `INSERT IGNORE INTO blog_posts (slug, title, excerpt, content, category, tags, cover_image, meta_title, meta_desc) VALUES ('netflix-vs-disney', 'Netflix vs Disney Plus: Which Streaming Service is Better?', 'A comprehensive comparison of the two biggest streaming platforms.', '<h2>Content Library</h2><p>Netflix offers over 15,000 titles while Disney Plus focuses on premium franchises.</p><h2>Pricing</h2><p>Get both at discounted rates through Subscription Hero.</p>', 'Streaming', '["netflix","disney","streaming"]', '/cat-streaming.jpg', 'Netflix vs Disney Plus 2025 | Subscription Hero', 'Compare Netflix and Disney Plus. Find the best streaming service at up to 72% off.');`,
    `INSERT IGNORE INTO blog_posts (slug, title, excerpt, content, category, tags, cover_image, meta_title, meta_desc) VALUES ('canva-pro-guide', 'How to Get Canva Pro for Cheap: Complete Guide 2025', 'Learn how to unlock Canva Pro features without breaking the bank.', '<h2>What is Canva Pro?</h2><p>Canva Pro gives you access to millions of premium templates, images, and design tools.</p><h2>Why Buy from Us?</h2><p>Get genuine Canva Pro access for just $2 - that is 96% off the retail price!</p>', 'Design Tools', '["canva","design","tutorial"]', '/cat-design-tools.jpg', 'Canva Pro Cheap 2025 | Subscription Hero', 'Buy Canva Pro for just $2 - 96% off. Genuine license with instant delivery.');`,
    `INSERT IGNORE INTO site_settings (\`key\`, value) VALUES ('site_name', 'Subscription Hero');`,
    `INSERT IGNORE INTO site_settings (\`key\`, value) VALUES ('site_tagline', 'Premium Digital Subscriptions Marketplace');`,
    `INSERT IGNORE INTO site_settings (\`key\`, value) VALUES ('contact_whatsapp', '917038146526');`,
    `INSERT IGNORE INTO site_settings (\`key\`, value) VALUES ('contact_telegram', 'mfatool');`,
  ];

  for (const stmt of statements) {
    try {
      await conn.execute(stmt);
      console.log("OK:", stmt.slice(0, 50));
    } catch (err: any) {
      if (err.message?.includes("Duplicate entry")) {
        console.log("SKIP: Already exists");
      } else {
        console.log("SKIP:", err.message?.slice(0, 80));
      }
    }
  }

  console.log("\nMigration complete!");
  await conn.end();
}

main().catch(console.error);