import { readFileSync } from 'fs';
import { dirname, join } from 'path';
import { fileURLToPath } from 'url';
import { db } from '../../../lib/db.mjs';

const here = dirname(fileURLToPath(import.meta.url));
const migrationSql = readFileSync(join(here, 'migrations', '001_theme_profiles.sql'), 'utf8');

export function ensureThemeSchema() {
  db.exec(migrationSql);
}

function parseProfile(row) {
  if (!row) return null;
  let values = {};
  try { values = JSON.parse(row.values_json || '{}'); } catch {}
  return { ...row, values };
}

export function listThemeProfiles() {
  ensureThemeSchema();
  return db.prepare('SELECT * FROM theme_profiles ORDER BY name COLLATE NOCASE, id').all().map(parseProfile);
}

export function getThemeProfile(id) {
  ensureThemeSchema();
  return parseProfile(db.prepare('SELECT * FROM theme_profiles WHERE id = ?').get(id));
}

export function saveThemeProfile(profile, userId) {
  ensureThemeSchema();
  db.prepare(`INSERT INTO theme_profiles (id, name, base_theme, values_json, created_by)
    VALUES (?, ?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET name = excluded.name, base_theme = excluded.base_theme,
      values_json = excluded.values_json, updated_at = datetime('now')`).run(
    profile.id, profile.name, profile.base_theme, JSON.stringify(profile.values || {}), userId || null,
  );
  return getThemeProfile(profile.id);
}

export function deleteThemeProfile(id) {
  ensureThemeSchema();
  const transaction = db.transaction(() => {
    db.prepare("UPDATE user_theme_preferences SET theme_id = 'inherit', updated_at = datetime('now') WHERE theme_id = ?").run(id);
    const system = db.prepare("SELECT value FROM settings WHERE key = 'theme_system_default'").get();
    if (system?.value === id) db.prepare("INSERT OR REPLACE INTO settings (key, value) VALUES ('theme_system_default', 'system')").run();
    return db.prepare('DELETE FROM theme_profiles WHERE id = ?').run(id).changes;
  });
  return transaction();
}

export function getUserThemePreference(userId) {
  ensureThemeSchema();
  return db.prepare('SELECT theme_id, density, reduced_motion, updated_at FROM user_theme_preferences WHERE user_id = ?').get(userId) || {
    theme_id: 'inherit', density: 'comfortable', reduced_motion: 'system', updated_at: null,
  };
}

export function saveUserThemePreference(userId, preference) {
  ensureThemeSchema();
  db.prepare(`INSERT INTO user_theme_preferences (user_id, theme_id, density, reduced_motion)
    VALUES (?, ?, ?, ?)
    ON CONFLICT(user_id) DO UPDATE SET theme_id = excluded.theme_id, density = excluded.density,
      reduced_motion = excluded.reduced_motion, updated_at = datetime('now')`).run(
    userId, preference.theme_id, preference.density, preference.reduced_motion,
  );
  return getUserThemePreference(userId);
}

function getSetting(key, fallback) {
  const row = db.prepare('SELECT value FROM settings WHERE key = ?').get(key);
  return row?.value ?? fallback;
}

function setSetting(key, value) {
  db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)').run(key, String(value));
}

export function getThemeSystemDefault() {
  return {
    theme_id: getSetting('theme_system_default', 'system'),
    density: getSetting('theme_system_density', 'comfortable'),
    reduced_motion: getSetting('theme_system_reduced_motion', 'system'),
  };
}

export function saveThemeSystemDefault(preference) {
  const transaction = db.transaction(() => {
    setSetting('theme_system_default', preference.theme_id);
    setSetting('theme_system_density', preference.density);
    setSetting('theme_system_reduced_motion', preference.reduced_motion);
  });
  transaction();
  return getThemeSystemDefault();
}
