import { Client, Databases, ID, Permission, Role } from "node-appwrite";

const requiredEnv = [
  "APPWRITE_ENDPOINT",
  "APPWRITE_PROJECT_ID",
  "APPWRITE_API_KEY",
  "DB_ID",
  "DB_NAME",
  "TABLE_PRODUCTS",
  "TABLE_QUESTIONS",
  "TABLE_SUBMISSIONS",
  "TABLE_ANSWERS",
  "TABLE_ORDERS",
  "PRODUCT_ID",
  "PRODUCT_SLUG",
  "PRODUCT_TITLE",
  "PRODUCT_PRICE_CENTS",
  "PRODUCT_CURRENCY"
];

for (const k of requiredEnv) {
  if (!process.env[k]) {
    console.error(`Missing env var: ${k}`);
    process.exit(1);
  }
}

const client = new Client()
  .setEndpoint(process.env.APPWRITE_ENDPOINT)
  .setProject(process.env.APPWRITE_PROJECT_ID)
  .setKey(process.env.APPWRITE_API_KEY);

const db = new Databases(client);

const DB_ID = process.env.DB_ID;
const DB_NAME = process.env.DB_NAME;
const T_PRODUCTS = process.env.TABLE_PRODUCTS;
const T_QUESTIONS = process.env.TABLE_QUESTIONS;
const T_SUBMISSIONS = process.env.TABLE_SUBMISSIONS;
const T_ANSWERS = process.env.TABLE_ANSWERS;
const T_ORDERS = process.env.TABLE_ORDERS;

async function ensureDatabase() {
  try {
    await db.get(DB_ID);
    console.log("DB exists:", DB_ID);
  } catch {
    await db.create(DB_ID, DB_NAME);
    console.log("DB created:", DB_ID);
  }
}

// Helper: create table if missing
async function ensureTable(tableId, name, permissions) {
  try {
    await db.getCollection(DB_ID, tableId);
    console.log("Collection exists:", tableId);
  } catch {
    await db.createCollection(DB_ID, tableId, name, permissions, true);
    console.log("Collection created:", tableId);
  }
}

// Helper: create column if missing
async function ensureColumn(tableId, key, fnCreate) {
  const columns = await db.listAttributes(DB_ID, tableId);
  const exists = columns.attributes?.some(c => c.key === key);
  if (exists) return;
  await fnCreate();
  console.log("Column created:", tableId, key);
}

// Basic permissions strategy
const PERM_READ_ANY = [Permission.read(Role.any())];
const PERM_SERVER_ONLY = [];
const PERM_SUBMISSION_TABLE = [Permission.create(Role.any())];

async function setupSchema() {
  await ensureDatabase();

  // Tables
  await ensureTable(T_PRODUCTS, "Products", PERM_SERVER_ONLY);
  await ensureTable(T_QUESTIONS, "Questions", PERM_SERVER_ONLY);
  await ensureTable(T_SUBMISSIONS, "Submissions", PERM_SUBMISSION_TABLE);
  await ensureTable(T_ANSWERS, "Answers", PERM_SUBMISSION_TABLE);
  await ensureTable(T_ORDERS, "Orders", PERM_SERVER_ONLY);

  // PRODUCTS columns
  await ensureColumn(T_PRODUCTS, "slug", () => 
    db.createStringAttribute(DB_ID, T_PRODUCTS, "slug", 128, true));
  await ensureColumn(T_PRODUCTS, "title", () => 
    db.createStringAttribute(DB_ID, T_PRODUCTS, "title", 256, true));
  await ensureColumn(T_PRODUCTS, "description", () => 
    db.createStringAttribute(DB_ID, T_PRODUCTS, "description", 4096, false));
  await ensureColumn(T_PRODUCTS, "priceCents", () => 
    db.createIntegerAttribute(DB_ID, T_PRODUCTS, "priceCents", true, 0, 999999999));
  await ensureColumn(T_PRODUCTS, "currency", () => 
    db.createStringAttribute(DB_ID, T_PRODUCTS, "currency", 8, true));
  await ensureColumn(T_PRODUCTS, "isActive", () => 
    db.createBooleanAttribute(DB_ID, T_PRODUCTS, "isActive", true));

  // QUESTIONS columns
  await ensureColumn(T_QUESTIONS, "productId", () => 
    db.createStringAttribute(DB_ID, T_QUESTIONS, "productId", 64, true));
  await ensureColumn(T_QUESTIONS, "key", () => 
    db.createStringAttribute(DB_ID, T_QUESTIONS, "key", 64, true));
  await ensureColumn(T_QUESTIONS, "label", () => 
    db.createStringAttribute(DB_ID, T_QUESTIONS, "label", 256, true));
  await ensureColumn(T_QUESTIONS, "helpText", () => 
    db.createStringAttribute(DB_ID, T_QUESTIONS, "helpText", 1024, false));
  await ensureColumn(T_QUESTIONS, "type", () => 
    db.createStringAttribute(DB_ID, T_QUESTIONS, "type", 32, true));
  await ensureColumn(T_QUESTIONS, "required", () => 
    db.createBooleanAttribute(DB_ID, T_QUESTIONS, "required", true));
  await ensureColumn(T_QUESTIONS, "step", () => 
    db.createIntegerAttribute(DB_ID, T_QUESTIONS, "step", true, 1, 9999));
  await ensureColumn(T_QUESTIONS, "order", () => 
    db.createIntegerAttribute(DB_ID, T_QUESTIONS, "order", true, 1, 999999));
  await ensureColumn(T_QUESTIONS, "optionsJson", () => 
    db.createStringAttribute(DB_ID, T_QUESTIONS, "optionsJson", 8192, false));
  await ensureColumn(T_QUESTIONS, "isActive", () => 
    db.createBooleanAttribute(DB_ID, T_QUESTIONS, "isActive", true));

  // SUBMISSIONS columns
  await ensureColumn(T_SUBMISSIONS, "productId", () => 
    db.createStringAttribute(DB_ID, T_SUBMISSIONS, "productId", 64, true));
  await ensureColumn(T_SUBMISSIONS, "status", () => 
    db.createStringAttribute(DB_ID, T_SUBMISSIONS, "status", 32, true));
  await ensureColumn(T_SUBMISSIONS, "customerEmail", () => 
    db.createEmailAttribute(DB_ID, T_SUBMISSIONS, "customerEmail", false));
  await ensureColumn(T_SUBMISSIONS, "customerName", () => 
    db.createStringAttribute(DB_ID, T_SUBMISSIONS, "customerName", 256, false));
  await ensureColumn(T_SUBMISSIONS, "utmJson", () => 
    db.createStringAttribute(DB_ID, T_SUBMISSIONS, "utmJson", 2048, false));
  await ensureColumn(T_SUBMISSIONS, "finalSummaryJson", () => 
    db.createStringAttribute(DB_ID, T_SUBMISSIONS, "finalSummaryJson", 8192, false));
  await ensureColumn(T_SUBMISSIONS, "priceCents", () => 
    db.createIntegerAttribute(DB_ID, T_SUBMISSIONS, "priceCents", true, 0, 999999999));
  await ensureColumn(T_SUBMISSIONS, "currency", () => 
    db.createStringAttribute(DB_ID, T_SUBMISSIONS, "currency", 8, true));

  // ANSWERS columns - simplified
  await ensureColumn(T_ANSWERS, "submissionId", () => 
    db.createStringAttribute(DB_ID, T_ANSWERS, "submissionId", 64, true));
  await ensureColumn(T_ANSWERS, "answersJson", () => 
    db.createStringAttribute(DB_ID, T_ANSWERS, "answersJson", 16384, true));

  // ORDERS columns - simplified
  await ensureColumn(T_ORDERS, "submissionId", () => 
    db.createStringAttribute(DB_ID, T_ORDERS, "submissionId", 64, true));
  await ensureColumn(T_ORDERS, "orderDataJson", () => 
    db.createStringAttribute(DB_ID, T_ORDERS, "orderDataJson", 8192, true));
}

async function seedProductAndQuestions() {
  const productId = process.env.PRODUCT_ID;

  // Upsert product
  try {
    await db.getDocument(DB_ID, T_PRODUCTS, productId);
    console.log("Product exists:", productId);
  } catch {
    await db.createDocument(
      DB_ID, 
      T_PRODUCTS, 
      productId, 
      {
        slug: process.env.PRODUCT_SLUG,
        title: process.env.PRODUCT_TITLE,
        description: "Personalisiere dein Postfach und bekomme ein klares Regel-Setup (Labels/Ordner/Filter) fuer Inbox Zero.",
        priceCents: Number(process.env.PRODUCT_PRICE_CENTS),
        currency: process.env.PRODUCT_CURRENCY,
        isActive: true
      },
      [Permission.read(Role.any())]
    );
    console.log("Product created:", productId);
  }

  // 13 Kernfragen
  const QUESTIONS = [
    // Step 1: Kontakt
    { 
      key:"customer_name", 
      label:"Wie soll ich dich nennen?", 
      type:"text", 
      required:false, 
      step:1, 
      order:1, 
      helpText:"Optional, fuer persoenliche Auslieferung." 
    },
    { 
      key:"customer_email", 
      label:"Wohin sollen wir dein Setup schicken?", 
      type:"email", 
      required:true, 
      step:1, 
      order:2, 
      helpText:"Wir schicken dir das Ergebnis + Anleitung." 
    },
    
    // Step 2: Provider + Volumen
    { 
      key:"provider", 
      label:"Welchen E-Mail Provider nutzt du?", 
      type:"select", 
      required:true, 
      step:2, 
      order:1,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"gmail", label:"Gmail"},
          {value:"outlook", label:"Outlook / Microsoft 365"},
          {value:"icloud", label:"iCloud Mail"},
          {value:"imap", label:"IMAP (anderer Anbieter)"}
        ]
      })
    },
    { 
      key:"daily_volume", 
      label:"Wie viele E-Mails bekommst du pro Tag?", 
      type:"select", 
      required:true, 
      step:2, 
      order:2,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"0-10", label:"0-10"},
          {value:"10-30", label:"10-30"},
          {value:"30-100", label:"30-100"},
          {value:"100+", label:"100+"}
        ]
      })
    },
    
    // Step 3: Ziel + Striktheit
    { 
      key:"primary_goal", 
      label:"Was ist dein Hauptziel?", 
      type:"select", 
      required:true, 
      step:3, 
      order:1,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"inbox_zero", label:"Inbox Zero (Posteingang leer)"},
          {value:"priority_focus", label:"Wichtiges sofort sehen"},
          {value:"newsletter_cleanup", label:"Newsletter/Promo aufraeumen"},
          {value:"client_speed", label:"Kundenmails schneller bearbeiten"},
          {value:"finance_clean", label:"Rechnungen/Belege sauber sammeln"}
        ]
      })
    },
    { 
      key:"strictness", 
      label:"Wie strikt soll sortiert werden?", 
      type:"select", 
      required:true, 
      step:3, 
      order:2,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"light", label:"Leicht (nur Stoerer)"},
          {value:"medium", label:"Mittel (balanced)"},
          {value:"hard", label:"Hart (Inbox wird fast leer)"}
        ]
      })
    },
    
    // Step 4: Kategorien + Limits
    { 
      key:"categories", 
      label:"Welche Kategorien willst du aktiv nutzen?", 
      type:"multiselect", 
      required:true, 
      step:4, 
      order:1,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"vip", label:"VIP / Wichtig"},
          {value:"clients", label:"Kunden / Projekte"},
          {value:"leads", label:"Leads / Anfragen"},
          {value:"billing", label:"Rechnungen / Belege"},
          {value:"banking", label:"Banking / Payments"},
          {value:"shipping", label:"Bestellungen / Versand"},
          {value:"newsletters", label:"Newsletter"},
          {value:"promos", label:"Promotions / Werbung"},
          {value:"social", label:"Social / Plattformen"},
          {value:"security", label:"Security / 2FA Codes"},
          {value:"calendar", label:"Kalender / Einladungen"},
          {value:"review", label:"Review / Unklar"}
        ]
      })
    },
    { 
      key:"max_labels", 
      label:"Wie viele Labels/Ordner maximal (damit es clean bleibt)?", 
      type:"select", 
      required:true, 
      step:4, 
      order:2,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"5", label:"max 5"},
          {value:"10", label:"max 10"},
          {value:"20", label:"max 20"},
          {value:"no_limit", label:"egal"}
        ]
      })
    },
    
    // Step 5: Ausnahmen
    { 
      key:"vip_senders", 
      label:"VIP Absender (nie aussortieren). Eine pro Zeile.", 
      type:"textarea", 
      required:false, 
      step:5, 
      order:1,
      helpText:"E-Mail oder Domain, z.B. boss@firma.de oder firma.de"
    },
    { 
      key:"block_senders", 
      label:"Absender die immer weg duerfen. Eine pro Zeile.", 
      type:"textarea", 
      required:false, 
      step:5, 
      order:2 
    },
    
    // Step 6: Newsletter/Invoices
    { 
      key:"newsletter_policy", 
      label:"Wie sollen Newsletter behandelt werden?", 
      type:"select", 
      required:true, 
      step:6, 
      order:1,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"label_only", label:"Nur labeln"},
          {value:"move", label:"In Newsletter Ordner verschieben"},
          {value:"archive", label:"Automatisch archivieren"},
          {value:"aggressive", label:"Aggressiv (fast alles weg, Review fuer Ausnahmen)"}
        ]
      })
    },
    { 
      key:"invoice_policy", 
      label:"Wie sollen Rechnungen/Belege behandelt werden?", 
      type:"select", 
      required:true, 
      step:6, 
      order:2,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"label_only", label:"Nur labeln"},
          {value:"move", label:"In Rechnungen Ordner verschieben"},
          {value:"forward", label:"Weiterleiten an Buchhaltung Adresse (spaeter in n8n)"},
          {value:"move_and_forward", label:"Verschieben + Weiterleiten (spaeter in n8n)"}
        ]
      })
    },
    
    // Step 7: Sprache
    { 
      key:"email_language", 
      label:"Welche Sprache ist in deinen E-Mails meist?", 
      type:"select", 
      required:true, 
      step:7, 
      order:1,
      optionsJson: JSON.stringify({ 
        options:[
          {value:"de", label:"Deutsch"},
          {value:"en", label:"Englisch"},
          {value:"mixed", label:"Gemischt"}
        ]
      })
    }
  ];

  // Seed questions - create with unique IDs
  for (const q of QUESTIONS) {
    await db.createDocument(
      DB_ID, 
      T_QUESTIONS, 
      ID.unique(), 
      {
        productId,
        key: q.key,
        label: q.label,
        helpText: q.helpText || null,
        type: q.type,
        required: q.required,
        step: q.step,
        order: q.order,
        optionsJson: q.optionsJson || null,
        isActive: true
      },
      [Permission.read(Role.any())]
    );
  }
  
  console.log("Seeded product + 13 questions.");
}

(async () => {
  await setupSchema();
  await seedProductAndQuestions();
  console.log("DONE");
})().catch((e) => {
  console.error(e);
  process.exit(1);
});