import { DB, type QueryParameterSet } from "https://deno.land/x/sqlite/mod.ts";

const DB_PATH = "data/careers.db";

export let db: DB;

function queryValue(sql: string, params: QueryParameterSet = []): number | string | null {
  const rows = db.queryEntries(sql, params);
  if (!rows.length) return null;
  const vals = Object.values(rows[0] as Record<string, unknown>);
  return vals.length ? (vals[0] as number | string | null) : null;
}

export async function initDb(): Promise<void> {
  Deno.mkdirSync("data", { recursive: true });
  db = new DB(DB_PATH);
  db.execute("PRAGMA journal_mode=WAL");
  db.execute("PRAGMA foreign_keys=ON");
  createTables();
  await seedData();
}

function createTables(): void {
  db.execute(`
    CREATE TABLE IF NOT EXISTS jobs (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      slug TEXT UNIQUE NOT NULL,
      department TEXT NOT NULL,
      location TEXT NOT NULL DEFAULT 'Remote',
      type TEXT NOT NULL DEFAULT 'Full-time',
      description TEXT NOT NULL,
      responsibilities TEXT NOT NULL DEFAULT '[]',
      requirements TEXT NOT NULL DEFAULT '[]',
      salary_range TEXT,
      is_active INTEGER NOT NULL DEFAULT 1,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);

  db.execute(`
    CREATE TABLE IF NOT EXISTS application_questions (
      id TEXT PRIMARY KEY,
      job_id TEXT,
      question_text TEXT NOT NULL,
      question_type TEXT NOT NULL DEFAULT 'text',
      options TEXT,
      required INTEGER NOT NULL DEFAULT 1,
      sort_order INTEGER NOT NULL DEFAULT 0,
      section_label TEXT,
      FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
    )
  `);

  db.execute(`
    CREATE TABLE IF NOT EXISTS applications (
      id TEXT PRIMARY KEY,
      job_id TEXT NOT NULL,
      applicant_name TEXT NOT NULL,
      applicant_email TEXT NOT NULL,
      answers TEXT NOT NULL DEFAULT '{}',
      status TEXT NOT NULL DEFAULT 'new',
      admin_notes TEXT DEFAULT '',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
    )
  `);

  db.execute(`
    CREATE TABLE IF NOT EXISTS settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL
    )
  `);

  db.execute(`
    CREATE TABLE IF NOT EXISTS application_files (
      id TEXT PRIMARY KEY,
      application_id TEXT NOT NULL,
      question_id TEXT,
      original_name TEXT NOT NULL,
      stored_path TEXT NOT NULL,
      mime_type TEXT NOT NULL,
      file_size INTEGER NOT NULL,
      created_at TEXT NOT NULL
    )
  `);

  try {
    db.execute("ALTER TABLE application_questions ADD COLUMN file_types TEXT");
  } catch {
    // Column may already exist
  }
}

async function sha256(input: string): Promise<string> {
  const hash = await crypto.subtle.digest("SHA-256", new TextEncoder().encode(input));
  return Array.from(new Uint8Array(hash)).map(b => b.toString(16).padStart(2, "0")).join("");
}

async function seedData(): Promise<void> {
  const existing = queryValue("SELECT COUNT(*) FROM settings") as number;
  if (existing > 0) return;

  const adminHash = await sha256("admin");
  db.query("INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)", ["admin_password", adminHash]);
  db.query("INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)", ["session_secret", crypto.randomUUID()]);

  const questions = [
    { text: "What is your full name?", type: "text", opt: false, ord: 1, sect: "About You" },
    { text: "What is your email address?", type: "email", opt: false, ord: 2, sect: "About You" },
    { text: "What is your phone number?", type: "phone", opt: false, ord: 3, sect: "About You" },
    { text: "Where are you located (city, country)?", type: "text", opt: false, ord: 4, sect: "About You" },
    { text: "What is your LinkedIn profile URL?", type: "text", opt: true, ord: 5, sect: "About You" },
    { text: "Tell us about your professional background — what have you done and what are you best at?", type: "textarea", opt: false, ord: 6, sect: "Experience & Motivation" },
    { text: "Why do you want to work at MarqueLead?", type: "textarea", opt: false, ord: 7, sect: "Experience & Motivation" },
    { text: "What specific skills and results would you bring to this role?", type: "textarea", opt: false, ord: 8, sect: "Experience & Motivation" },
    { text: "Do you have experience in digital marketing, lead generation, or the industry relevant to this role?", type: "textarea", opt: false, ord: 9, sect: "Experience & Motivation" },
    { text: "Share a link to your portfolio, website, or work samples", type: "text", opt: true, ord: 10, sect: "Portfolio & Samples" },
    { text: "Describe a project or campaign you're most proud of and the impact it had", type: "textarea", opt: false, ord: 11, sect: "Portfolio & Samples" },
    { text: "What is your earliest start date / notice period?", type: "select", opt: false, ord: 12, sect: "Logistics", opts: ["Immediate", "2 weeks", "1 month", "Other"] },
    { text: "Are you able to work in the Malaysia Time (MYT, UTC+8) zone?", type: "select", opt: false, ord: 13, sect: "Logistics", opts: ["Yes", "No", "Flexible"] },
    { text: "What are your salary expectations?", type: "text", opt: true, ord: 14, sect: "Logistics" },
  ];

  for (const q of questions) {
    db.query(
      "INSERT INTO application_questions (id, job_id, question_text, question_type, options, required, sort_order, section_label) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
      [
        crypto.randomUUID(),
        null,
        q.text,
        q.type,
        (q as any).opts ? JSON.stringify((q as any).opts) : null,
        q.opt ? 0 : 1,
        q.ord,
        q.sect,
      ],
    );
  }

  const exampleJobs = [
    {
      title: "Senior Lead Generation Specialist",
      slug: "senior-lead-generation-specialist",
      department: "Real Estate",
      description: "At MarqueLead, Senior Lead Generation Specialists are the tip of the spear. You will architect and execute multi-channel outbound campaigns targeting high-net-worth real estate investors and property developers. This role demands a strategic mindset, exceptional research skills, and the ability to turn cold outreach into warm, qualified opportunities.",
      responsibilities: [
        "Design and implement outbound lead generation campaigns across email, LinkedIn, and cold calling",
        "Research and build targeted prospect lists of real estate investors, developers, and decision-makers",
        "Craft personalized messaging that resonates with high-value prospects",
        "Use CRM tools to track, nurture, and hand off qualified leads to the sales team",
        "Analyze campaign performance metrics and optimize based on data-driven insights",
      ],
      requirements: [
        "2+ years of experience in B2B lead generation, preferably in real estate or financial services",
        "Proven track record of hitting or exceeding lead generation quotas",
        "Expert-level proficiency with LinkedIn Sales Navigator, Apollo, or similar tools",
        "Exceptional written communication skills with an eye for persuasive copy",
        "Comfortable working in US/EU time zones with occasional flexibility",
      ],
      salary_range: "$60,000 - $85,000",
    },
    {
      title: "Client Success Manager",
      slug: "client-success-manager",
      department: "SaaS",
      description: "MarqueLead Client Success Managers own the post-sale relationship with our SaaS clients. You will ensure that every client achieves measurable ROI from our lead generation platform. You are part consultant, part account manager, and part product expert — driving retention, expansion, and advocacy.",
      responsibilities: [
        "Onboard new clients and guide them through platform setup and best practices",
        "Conduct regular business review calls to measure performance and identify growth opportunities",
        "Proactively monitor client health scores and intervene before churn risk escalates",
        "Collaborate with the product team to relay client feedback and influence the roadmap",
        "Drive upsells and expansions by demonstrating additional value streams within the platform",
      ],
      requirements: [
        "3+ years in client success, account management, or customer success at a SaaS company",
        "Experience with CRM and customer success platforms (HubSpot, Gainsight, or similar)",
        "Strong analytical skills — you can interpret data and tell a story with it",
        "Excellent presentation and executive communication skills",
        "Ability to manage 15-20 client relationships simultaneously",
      ],
      salary_range: "$70,000 - $95,000",
    },
    {
      title: "High-Ticket Sales Closer",
      slug: "high-ticket-sales-closer",
      department: "Real Estate",
      location: "Remote",
      type: "Contract",
      description: "MarqueLead is seeking elite High-Ticket Sales Closers who can take qualified leads and convert them into six-figure partnerships. You will handle the entire closing process — from discovery calls to contract negotiation — for our premier real estate lead generation packages. This is a performance-based role for closers who thrive on commission.",
      responsibilities: [
        "Conduct high-stakes discovery and closing calls with C-suite real estate executives",
        "Navigate complex buying processes involving multiple stakeholders and decision-makers",
        "Customize MarqueLead's service offerings to match each prospect's unique needs",
        "Overcome objections with confidence and strategic rebuttals",
        "Close deals ranging from $25,000 to $150,000 in annual recurring revenue",
      ],
      requirements: [
        "Proven track record closing high-ticket deals ($20k+ ACV) in B2B services or software",
        "Experience in real estate, marketing, or lead generation sales",
        "Exceptional emotional intelligence and active listening skills",
        "Resilient mindset — you handle rejection as data, not defeat",
        "Must be available for calls during US business hours (flexible schedule)",
      ],
      salary_range: "$40,000 base + uncapped commission (OTE $120k-$200k)",
    },
  ];

  for (const job of exampleJobs) {
    const id = crypto.randomUUID();
    const now = new Date().toISOString();
    db.query(
      `INSERT INTO jobs (id, title, slug, department, location, type, description, responsibilities, requirements, salary_range, is_active, sort_order, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id,
        job.title,
        job.slug,
        job.department,
        job.location || "Remote",
        job.type || "Full-time",
        job.description,
        JSON.stringify(job.responsibilities),
        JSON.stringify(job.requirements),
        job.salary_range || null,
        1,
        0,
        now,
        now,
      ],
    );
  }
}

export interface Job {
  id: string;
  title: string;
  slug: string;
  department: string;
  location: string;
  type: string;
  description: string;
  responsibilities: string;
  requirements: string;
  salary_range: string | null;
  is_active: number;
  sort_order: number;
  created_at: string;
  updated_at: string;
}

export interface Question {
  id: string;
  job_id: string | null;
  question_text: string;
  question_type: string;
  options: string | null;
  required: number;
  sort_order: number;
  section_label: string | null;
  file_types: string | null;
}

export interface ApplicationFile {
  id: string;
  application_id: string;
  question_id: string | null;
  original_name: string;
  stored_path: string;
  mime_type: string;
  file_size: number;
  created_at: string;
}

export interface Application {
  id: string;
  job_id: string;
  applicant_name: string;
  applicant_email: string;
  answers: string;
  status: string;
  admin_notes: string;
  created_at: string;
  updated_at: string;
}

function rowToJob(row: Record<string, unknown>): Job {
  return row as unknown as Job;
}

function rowToQuestion(row: Record<string, unknown>): Question {
  return row as unknown as Question;
}

function rowToApplication(row: Record<string, unknown>): Application {
  return row as unknown as Application;
}

export function getJobs(activeOnly = false): Job[] {
  let sql = "SELECT * FROM jobs";
  if (activeOnly) sql += " WHERE is_active = 1";
  sql += " ORDER BY sort_order ASC, created_at DESC";
  return db.queryEntries(sql).map(rowToJob);
}

export function getJobBySlug(slug: string): Job | null {
  const rows = db.queryEntries("SELECT * FROM jobs WHERE slug = ?", [slug]);
  return rows.length ? rowToJob(rows[0]) : null;
}

export function getJobById(id: string): Job | null {
  const rows = db.queryEntries("SELECT * FROM jobs WHERE id = ?", [id]);
  return rows.length ? rowToJob(rows[0]) : null;
}

export function createJob(data: {
  title: string;
  department: string;
  location?: string;
  type?: string;
  description: string;
  responsibilities?: string;
  requirements?: string;
  salary_range?: string;
  is_active?: number;
}): Job {
  const id = crypto.randomUUID();
  const now = new Date().toISOString();
  const maxOrder = (queryValue("SELECT COALESCE(MAX(sort_order), -1) + 1 FROM jobs") as number) || 0;

  db.query(
    `INSERT INTO jobs (id, title, slug, department, location, type, description, responsibilities, requirements, salary_range, is_active, sort_order, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      id,
      data.title,
      slugify(data.title, true),
      data.department,
      data.location || "Remote",
      data.type || "Full-time",
      data.description,
      data.responsibilities || "[]",
      data.requirements || "[]",
      data.salary_range || null,
      data.is_active !== undefined ? data.is_active : 1,
      maxOrder,
      now,
      now,
    ],
  );

  return getJobById(id)!;
}

export function updateJob(id: string, data: Partial<{
  title: string;
  department: string;
  location: string;
  type: string;
  description: string;
  responsibilities: string;
  requirements: string;
  salary_range: string;
  is_active: number;
  sort_order: number;
}>): Job | null {
  const fields: string[] = [];
  const values: QueryParameterSet = [];

  if (data.title !== undefined) {
    fields.push("title = ?");
    values.push(data.title);
    fields.push("slug = ?");
    values.push(slugify(data.title, true));
  }
  if (data.department !== undefined) { fields.push("department = ?"); values.push(data.department); }
  if (data.location !== undefined) { fields.push("location = ?"); values.push(data.location); }
  if (data.type !== undefined) { fields.push("type = ?"); values.push(data.type); }
  if (data.description !== undefined) { fields.push("description = ?"); values.push(data.description); }
  if (data.responsibilities !== undefined) { fields.push("responsibilities = ?"); values.push(data.responsibilities); }
  if (data.requirements !== undefined) { fields.push("requirements = ?"); values.push(data.requirements); }
  if (data.salary_range !== undefined) { fields.push("salary_range = ?"); values.push(data.salary_range!); }
  if (data.is_active !== undefined) { fields.push("is_active = ?"); values.push(data.is_active!); }
  if (data.sort_order !== undefined) { fields.push("sort_order = ?"); values.push(data.sort_order!); }

  if (!fields.length) return getJobById(id);

  fields.push("updated_at = ?");
  values.push(new Date().toISOString());
  values.push(id);

  db.query(`UPDATE jobs SET ${fields.join(", ")} WHERE id = ?`, values);
  return getJobById(id);
}

export function deleteJob(id: string): void {
  db.query("DELETE FROM jobs WHERE id = ?", [id]);
}

export function reorderJobs(orderedIds: string[]): void {
  orderedIds.forEach((id, index) => {
    db.query("UPDATE jobs SET sort_order = ?, updated_at = ? WHERE id = ?", [index, new Date().toISOString(), id]);
  });
}

export function getQuestions(jobId: string | null = null): Question[] {
  if (jobId) {
    const shared = db.queryEntries(
      "SELECT * FROM application_questions WHERE job_id IS NULL ORDER BY sort_order ASC",
    );
    const specific = db.queryEntries(
      "SELECT * FROM application_questions WHERE job_id = ? ORDER BY sort_order ASC",
      [jobId],
    );
    return [...shared.map(rowToQuestion), ...specific.map(rowToQuestion)];
  }
  return db.queryEntries("SELECT * FROM application_questions ORDER BY sort_order ASC").map(rowToQuestion);
}

export function getSharedQuestions(): Question[] {
  return db.queryEntries(
    "SELECT * FROM application_questions WHERE job_id IS NULL ORDER BY sort_order ASC",
  ).map(rowToQuestion);
}

export function getQuestionById(id: string): Question | null {
  const rows = db.queryEntries("SELECT * FROM application_questions WHERE id = ?", [id]);
  return rows.length ? rowToQuestion(rows[0]) : null;
}

export function createQuestion(data: {
  job_id?: string;
  question_text: string;
  question_type: string;
  options?: string;
  required?: number;
  section_label?: string;
}): Question {
  const id = crypto.randomUUID();
  const maxOrder = (queryValue(
    "SELECT COALESCE(MAX(sort_order), -1) + 1 FROM application_questions",
  ) as number) || 0;

  db.query(
    `INSERT INTO application_questions (id, job_id, question_text, question_type, options, required, sort_order, section_label, file_types)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      id,
      data.job_id || null,
      data.question_text,
      data.question_type,
      data.options || null,
      data.required !== undefined ? data.required : 1,
      maxOrder,
      data.section_label || null,
      (data as any).file_types || null,
    ],
  );

  return getQuestionById(id)!;
}

export function updateQuestion(id: string, data: Partial<{
  job_id: string;
  question_text: string;
  question_type: string;
  options: string;
  required: number;
  sort_order: number;
  section_label: string;
  file_types: string;
}>): Question | null {
  const fields: string[] = [];
  const values: QueryParameterSet = [];

  if (data.job_id !== undefined) { fields.push("job_id = ?"); values.push(data.job_id); }
  if (data.question_text !== undefined) { fields.push("question_text = ?"); values.push(data.question_text); }
  if (data.question_type !== undefined) { fields.push("question_type = ?"); values.push(data.question_type); }
  if (data.options !== undefined) { fields.push("options = ?"); values.push(data.options); }
  if (data.required !== undefined) { fields.push("required = ?"); values.push(data.required!); }
  if (data.sort_order !== undefined) { fields.push("sort_order = ?"); values.push(data.sort_order!); }
  if (data.section_label !== undefined) { fields.push("section_label = ?"); values.push(data.section_label); }
  if (data.file_types !== undefined) { fields.push("file_types = ?"); values.push(data.file_types); }

  if (!fields.length) return getQuestionById(id);

  values.push(id);
  db.query(`UPDATE application_questions SET ${fields.join(", ")} WHERE id = ?`, values);
  return getQuestionById(id);
}

export function deleteQuestion(id: string): void {
  db.query("DELETE FROM application_questions WHERE id = ?", [id]);
}

export function reorderQuestions(orderedIds: string[]): void {
  orderedIds.forEach((id, index) => {
    db.query("UPDATE application_questions SET sort_order = ? WHERE id = ?", [index, id]);
  });
}

export function getApplications(filters?: { jobId?: string; status?: string }): Application[] {
  let sql = "SELECT * FROM applications WHERE 1=1";
  const params: QueryParameterSet = [];

  if (filters?.jobId) {
    sql += " AND job_id = ?";
    params.push(filters.jobId);
  }
  if (filters?.status && filters.status !== "all") {
    sql += " AND status = ?";
    params.push(filters.status);
  }

  sql += " ORDER BY created_at DESC";
  return db.queryEntries(sql, params).map(rowToApplication);
}

export function getApplicationById(id: string): Application | null {
  const rows = db.queryEntries("SELECT * FROM applications WHERE id = ?", [id]);
  return rows.length ? rowToApplication(rows[0]) : null;
}

export function getApplicationCountByJob(jobId: string): number {
  return (queryValue("SELECT COUNT(*) FROM applications WHERE job_id = ?", [jobId]) as number) || 0;
}

export function createApplication(data: {
  job_id: string;
  applicant_name: string;
  applicant_email: string;
  answers: string;
}): Application {
  const id = crypto.randomUUID();
  const now = new Date().toISOString();

  db.query(
    `INSERT INTO applications (id, job_id, applicant_name, applicant_email, answers, status, admin_notes, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [id, data.job_id, data.applicant_name, data.applicant_email, data.answers, "new", "", now, now],
  );

  return getApplicationById(id)!;
}

export function updateApplicationStatus(id: string, status: string): Application | null {
  const now = new Date().toISOString();
  db.query("UPDATE applications SET status = ?, updated_at = ? WHERE id = ?", [status, now, id]);
  return getApplicationById(id);
}

export function updateApplicationNotes(id: string, notes: string): Application | null {
  const now = new Date().toISOString();
  db.query("UPDATE applications SET admin_notes = ?, updated_at = ? WHERE id = ?", [notes, now, id]);
  return getApplicationById(id);
}

export function getDashboardStats(): Record<string, number> {
  const totalJobs = (queryValue("SELECT COUNT(*) FROM jobs") as number) || 0;
  const totalApplications = (queryValue("SELECT COUNT(*) FROM applications") as number) || 0;

  const weekAgo = new Date();
  weekAgo.setDate(weekAgo.getDate() - 7);
  const weekStart = weekAgo.toISOString();

  const appsThisWeek = (queryValue(
    "SELECT COUNT(*) FROM applications WHERE created_at >= ?",
    [weekStart],
  ) as number) || 0;

  const newApps = (queryValue(
    "SELECT COUNT(*) FROM applications WHERE status = 'new'",
  ) as number) || 0;

  return { totalJobs, totalApplications, appsThisWeek, newApps };
}

export function getSetting(key: string): string | null {
  const row = db.queryEntries("SELECT value FROM settings WHERE key = ?", [key]);
  return row.length ? (row[0] as Record<string, unknown>).value as string : null;
}

export function setSetting(key: string, value: string): void {
  db.query(
    "INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",
    [key, value],
  );
}

export function createApplicationFile(data: {
  application_id: string;
  question_id: string | null;
  original_name: string;
  stored_path: string;
  mime_type: string;
  file_size: number;
}): ApplicationFile {
  const id = crypto.randomUUID();
  const now = new Date().toISOString();
  db.query(
    `INSERT INTO application_files (id, application_id, question_id, original_name, stored_path, mime_type, file_size, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    [id, data.application_id, data.question_id, data.original_name, data.stored_path, data.mime_type, data.file_size, now],
  );
  const rows = db.queryEntries("SELECT * FROM application_files WHERE id = ?", [id]);
  return rows[0] as unknown as ApplicationFile;
}

export function getApplicationFiles(applicationId: string): ApplicationFile[] {
  return db.queryEntries(
    "SELECT * FROM application_files WHERE application_id = ? ORDER BY created_at ASC",
    [applicationId],
  ) as unknown as ApplicationFile[];
}

export function getFileQuestions(jobId: string): Question[] {
  const shared = db.queryEntries(
    "SELECT * FROM application_questions WHERE (job_id IS NULL OR job_id = ?) AND question_type = 'file' ORDER BY sort_order ASC",
    [jobId],
  );
  const specific = db.queryEntries(
    "SELECT * FROM application_questions WHERE job_id = ? AND question_type = 'file' ORDER BY sort_order ASC",
    [jobId],
  );
  return [...shared.map(rowToQuestion), ...specific.map(rowToQuestion)];
}

export function getQuestionByIdFull(id: string): Question | null {
  const rows = db.queryEntries("SELECT * FROM application_questions WHERE id = ?", [id]);
  return rows.length ? rowToQuestion(rows[0]) : null;
}

let slugCounter = 0;
function slugify(text: string, ensureUnique = false): string {
  let slug = text.toLowerCase()
    .replace(/[^\w\s-]/g, "")
    .replace(/\s+/g, "-")
    .replace(/-+/g, "-")
    .trim();
  if (!ensureUnique) return slug;
  const existing = db.queryEntries("SELECT slug FROM jobs WHERE slug = ?", [slug]);
  if (existing.length === 0) return slug;
  slugCounter++;
  return `${slug}-${slugCounter}`;
}
