import Database from 'better-sqlite3';
import path from 'path';

const dbPath = path.join(process.cwd(), '..', 'data', 'bot.db');
const db = new Database(dbPath);

export const guildSettings = {
  get: (guildId: string) => db.prepare('SELECT * FROM guild_settings WHERE guild_id = ?').get(guildId) as any,
  getOrCreate: (guildId: string) => {
    let s = db.prepare('SELECT * FROM guild_settings WHERE guild_id = ?').get(guildId) as any;
    if (!s) {
      db.prepare('INSERT OR IGNORE INTO guild_settings (guild_id) VALUES (?)').run(guildId);
      s = db.prepare('SELECT * FROM guild_settings WHERE guild_id = ?').get(guildId);
    }
    return s;
  },
  update: (guildId: string, data: Record<string, any>) => {
    const fields = Object.keys(data).map(k => `${k} = ?`).join(', ');
    const values = [...Object.values(data), guildId];
    db.prepare(`UPDATE guild_settings SET ${fields} WHERE guild_id = ?`).run(...values);
  }
};

export const ticketCategories = {
  getAll: (guildId: string) => db.prepare('SELECT * FROM ticket_categories WHERE guild_id = ?').all(guildId) as any[],
  get: (id: number) => db.prepare('SELECT * FROM ticket_categories WHERE id = ?').get(id) as any,
  create: (guildId: string, name: string, description: string, emoji: string, roleIds: string[], formFields: any[]) => {
    const result = db.prepare(
      'INSERT INTO ticket_categories (guild_id, name, description, emoji, role_ids, form_fields) VALUES (?, ?, ?, ?, ?, ?)'
    ).run(guildId, name, description, emoji, JSON.stringify(roleIds), JSON.stringify(formFields));
    return db.prepare('SELECT * FROM ticket_categories WHERE id = ?').get(result.lastInsertRowid);
  },
  update: (id: number, data: Record<string, any>) => {
    if (data.role_ids && Array.isArray(data.role_ids)) data.role_ids = JSON.stringify(data.role_ids);
    if (data.form_fields && Array.isArray(data.form_fields)) data.form_fields = JSON.stringify(data.form_fields);
    const fields = Object.keys(data).map(k => `${k} = ?`).join(', ');
    const values = [...Object.values(data), id];
    db.prepare(`UPDATE ticket_categories SET ${fields} WHERE id = ?`).run(...values);
  },
  delete: (id: number) => {
    db.prepare('UPDATE tickets SET category_id = NULL WHERE category_id = ?').run(id);
    db.prepare('DELETE FROM ticket_categories WHERE id = ?').run(id);
  }
};

export const tickets = {
  getAll: (guildId: string, status?: string) => {
    if (status) return db.prepare('SELECT * FROM tickets WHERE guild_id = ? AND status = ?').all(guildId, status) as any[];
    return db.prepare('SELECT * FROM tickets WHERE guild_id = ?').all(guildId) as any[];
  },
  stats: (guildId: string) => {
    const total = (db.prepare('SELECT COUNT(*) as c FROM tickets WHERE guild_id = ?').get(guildId) as any).c;
    const open = (db.prepare('SELECT COUNT(*) as c FROM tickets WHERE guild_id = ? AND status = ?').get(guildId, 'open') as any).c;
    const closed = (db.prepare('SELECT COUNT(*) as c FROM tickets WHERE guild_id = ? AND status = ?').get(guildId, 'closed') as any).c;
    const avgResponse = (db.prepare(`SELECT AVG((julianday(first_response_at) - julianday(created_at)) * 24 * 60) as avg_minutes FROM tickets WHERE guild_id = ? AND first_response_at IS NOT NULL`).get(guildId) as any);
    const avgResolution = (db.prepare(`SELECT AVG((julianday(closed_at) - julianday(created_at)) * 24 * 60) as avg_minutes FROM tickets WHERE guild_id = ? AND closed_at IS NOT NULL`).get(guildId) as any);
    const today = (db.prepare(`SELECT COUNT(*) as c FROM tickets WHERE guild_id = ? AND date(created_at) = date('now')`).get(guildId) as any).c;
    const thisWeek = (db.prepare(`SELECT COUNT(*) as c FROM tickets WHERE guild_id = ? AND created_at >= date('now', '-7 days')`).get(guildId) as any).c;
    const byPriority = {
      urgent: (db.prepare('SELECT COUNT(*) as c FROM tickets WHERE guild_id = ? AND priority = ?').get(guildId, 'urgent') as any).c,
      high: (db.prepare('SELECT COUNT(*) as c FROM tickets WHERE guild_id = ? AND priority = ?').get(guildId, 'high') as any).c,
      normal: (db.prepare('SELECT COUNT(*) as c FROM tickets WHERE guild_id = ? AND priority = ?').get(guildId, 'normal') as any).c,
      low: (db.prepare('SELECT COUNT(*) as c FROM tickets WHERE guild_id = ? AND priority = ?').get(guildId, 'low') as any).c,
    };
    return { total, open, closed, today, thisWeek, avgResponseMinutes: avgResponse?.avg_minutes || 0, avgResolutionMinutes: avgResolution?.avg_minutes || 0, byPriority };
  }
};

export const ticketLogs = {
  getRecent: (guildId: string, limit = 20) => db.prepare('SELECT * FROM ticket_logs WHERE guild_id = ? ORDER BY created_at DESC LIMIT ?').all(guildId, limit) as any[]
};

export const intimations = {
  stats: (guildId: string) => {
    const total = (db.prepare('SELECT COUNT(*) as c FROM intimations WHERE guild_id = ?').get(guildId) as any).c;
    const pending = (db.prepare('SELECT COUNT(*) as c FROM intimations WHERE guild_id = ? AND status = ?').get(guildId, 'pending') as any).c;
    const confirmed = (db.prepare('SELECT COUNT(*) as c FROM intimations WHERE guild_id = ? AND status = ?').get(guildId, 'confirmed') as any).c;
    return { total, pending, confirmed };
  }
};

export const panelAdmins = {
  isAdmin: (guildId: string, userId: string) => !!db.prepare('SELECT * FROM panel_admins WHERE guild_id = ? AND user_id = ?').get(guildId, userId)
};

export default db;