const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

const defaultSettings = {
  visionModel: '',
  taskModel: '',
  sex: '',
  age: '',
  heightCm: '',
  weightKg: '',
  targetWeightKg: '',
  goal: 'Lose weight',
  calorieTarget: '',
  activityLevel: 'Moderate',
  pace: 'Steady',
  motivation: '',
  challenge: '',
  trackingStyle: '',
  calorieCountingExperience: '',
  fastingInterest: '',
};

function readJsonFile(file, fallback) {
  try { return JSON.parse(fs.readFileSync(file, 'utf8')); } catch { return fallback; }
}

function nowIso() {
  return new Date().toISOString();
}

function delay(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

function maskDatabaseUrl(databaseUrl) {
  try {
    const url = new URL(databaseUrl);
    if (url.password) url.password = '***';
    return url.toString();
  } catch {
    return 'PostgreSQL';
  }
}

function normalizePlan(plan) {
  return {
    id: String(plan.id || ''),
    title: String(plan.title || 'Weight-loss plan'),
    version: Number(plan.version || 1),
    content: String(plan.content || ''),
    previousPlanId: plan.previousPlanId || '',
    createdAt: plan.createdAt || nowIso(),
  };
}

function normalizeEntry(entry, trashedAt = '') {
  const payload = { ...entry };
  if (trashedAt) payload.trashedAt = trashedAt;
  else delete payload.trashedAt;
  return payload;
}

function readSqliteLegacy(sqliteFile) {
  if (!fs.existsSync(sqliteFile)) return {};
  try {
    const { DatabaseSync } = require('node:sqlite');
    const db = new DatabaseSync(sqliteFile, { readOnly: true });
    const readKey = key => {
      try {
        const row = db.prepare('SELECT value FROM app_kv WHERE key = ?').get(key);
        return row ? JSON.parse(row.value) : undefined;
      } catch { return undefined; }
    };
    const plans = (() => {
      try {
        return db.prepare('SELECT id, title, version, content, previous_plan_id AS previousPlanId, created_at AS createdAt FROM plans ORDER BY version DESC, created_at DESC').all();
      } catch { return []; }
    })();
    const entries = [];
    const trash = [];
    try {
      for (const row of db.prepare('SELECT payload, trashed_at AS trashedAt FROM meal_entries ORDER BY updated_at DESC').all()) {
        const entry = JSON.parse(row.payload);
        if (row.trashedAt) trash.push(normalizeEntry(entry, row.trashedAt));
        else entries.push(normalizeEntry(entry, ''));
      }
    } catch {}
    db.close();
    return {
      authConfig: readKey('auth_config'),
      modelConfig: readKey('model_config'),
      settings: readKey('settings'),
      selectedPlanId: readKey('selected_plan_id'),
      plans,
      entries,
      trash,
    };
  } catch {
    return {};
  }
}

async function connectWithRetry(pool, attempts = 40) {
  let lastError;
  for (let attempt = 1; attempt <= attempts; attempt += 1) {
    try {
      await pool.query('SELECT 1');
      return;
    } catch (error) {
      lastError = error;
      await delay(Math.min(3000, attempt * 250));
    }
  }
  throw lastError;
}

async function createStore({
  dataDir,
  databaseUrl = process.env.CALORIE_AI_DATABASE_URL || process.env.DATABASE_URL || 'postgresql://calorie_ai@postgres:5432/calorie_ai',
  legacyFiles = {},
  sqliteFile = path.join(dataDir, 'calorie-ai.sqlite'),
}) {
  fs.mkdirSync(dataDir, { recursive: true });
  const pool = new Pool({ connectionString: databaseUrl });
  await connectWithRetry(pool);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS app_kv (
      key TEXT PRIMARY KEY,
      value JSONB NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );
    CREATE TABLE IF NOT EXISTS plans (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      version INTEGER NOT NULL,
      content TEXT NOT NULL,
      previous_plan_id TEXT,
      created_at TEXT NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );
    CREATE TABLE IF NOT EXISTS meal_entries (
      id TEXT PRIMARY KEY,
      payload JSONB NOT NULL,
      trashed_at TEXT,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );
    CREATE TABLE IF NOT EXISTS activities (
      id TEXT PRIMARY KEY,
      source TEXT NOT NULL,
      source_name TEXT NOT NULL DEFAULT '',
      type TEXT NOT NULL DEFAULT '',
      title TEXT NOT NULL DEFAULT '',
      start_at TEXT NOT NULL,
      end_at TEXT NOT NULL DEFAULT '',
      calories DOUBLE PRECISION NOT NULL DEFAULT 0,
      steps BIGINT NOT NULL DEFAULT 0,
      distance_meters DOUBLE PRECISION NOT NULL DEFAULT 0,
      metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
      synced_at TEXT NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );
  `);

  async function getKey(key, fallback) {
    const result = await pool.query('SELECT value FROM app_kv WHERE key = $1', [key]);
    return result.rows[0]?.value ?? fallback;
  }

  async function setKey(key, value) {
    await pool.query(`
      INSERT INTO app_kv (key, value, updated_at) VALUES ($1, $2::jsonb, now())
      ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at
    `, [key, JSON.stringify(value)]);
  }

  async function hasKey(key) {
    const result = await pool.query('SELECT 1 FROM app_kv WHERE key = $1', [key]);
    return result.rowCount > 0;
  }

  async function savePlanRow(plan) {
    const normalized = normalizePlan(plan);
    await pool.query(`
      INSERT INTO plans (id, title, version, content, previous_plan_id, created_at, updated_at)
      VALUES ($1, $2, $3, $4, $5, $6, now())
      ON CONFLICT(id) DO UPDATE SET
        title = excluded.title,
        version = excluded.version,
        content = excluded.content,
        previous_plan_id = excluded.previous_plan_id,
        created_at = excluded.created_at,
        updated_at = excluded.updated_at
    `, [
      normalized.id,
      normalized.title,
      normalized.version,
      normalized.content,
      normalized.previousPlanId || null,
      normalized.createdAt,
    ]);
    return normalized.id;
  }

  async function saveEntryRow(entry, trashedAt = '') {
    const payload = normalizeEntry(entry, trashedAt);
    await pool.query(`
      INSERT INTO meal_entries (id, payload, trashed_at, updated_at) VALUES ($1, $2::jsonb, $3, now())
      ON CONFLICT(id) DO UPDATE SET payload = excluded.payload, trashed_at = excluded.trashed_at, updated_at = excluded.updated_at
    `, [String(payload.id), JSON.stringify(payload), trashedAt || null]);
  }

  async function migrateLegacy() {
    if (await getKey('legacy_migration_done', false)) return;

    const authMissing = !await hasKey('auth_config');
    const modelsMissing = !await hasKey('model_config');
    const settingsMissing = !await hasKey('settings');
    const planCount = await pool.query('SELECT COUNT(*)::int AS count FROM plans');
    const entryCount = await pool.query('SELECT COUNT(*)::int AS count FROM meal_entries');
    if (!authMissing && !modelsMissing && !settingsMissing && planCount.rows[0].count > 0 && entryCount.rows[0].count > 0) {
      await setKey('legacy_migration_done', true);
      return;
    }

    const sqlite = readSqliteLegacy(sqliteFile);

    if (authMissing) await setKey('auth_config', sqlite.authConfig || readJsonFile(legacyFiles.authFile, {}));
    if (modelsMissing) await setKey('model_config', sqlite.modelConfig || readJsonFile(legacyFiles.modelsFile, {}));
    if (settingsMissing) await setKey('settings', sqlite.settings || readJsonFile(legacyFiles.settingsFile, defaultSettings));

    if (planCount.rows[0].count === 0) {
      const jsonPlans = readJsonFile(legacyFiles.plansFile, {});
      const plans = sqlite.plans?.length ? sqlite.plans : Array.isArray(jsonPlans.plans) ? jsonPlans.plans : [];
      for (const plan of plans) if (plan && plan.id && plan.content) await savePlanRow(plan);
      const selectedPlanId = sqlite.selectedPlanId || jsonPlans.selectedPlanId || '';
      if (selectedPlanId) await setKey('selected_plan_id', selectedPlanId);
    }

    if (entryCount.rows[0].count === 0) {
      const jsonEntries = readJsonFile(legacyFiles.entriesFile, {});
      const entries = sqlite.entries?.length ? sqlite.entries : Array.isArray(jsonEntries.entries) ? jsonEntries.entries : [];
      const trash = sqlite.trash?.length ? sqlite.trash : Array.isArray(jsonEntries.trash) ? jsonEntries.trash : [];
      for (const entry of entries) if (entry && entry.id) await saveEntryRow(entry, '');
      for (const entry of trash) if (entry && entry.id) await saveEntryRow(entry, entry.trashedAt || nowIso());
    }

    await setKey('legacy_migration_done', true);
  }

  async function getPlans() {
    const result = await pool.query(`
      SELECT id, title, version, content, previous_plan_id, created_at
      FROM plans
      ORDER BY version DESC, created_at DESC
    `);
    const plans = result.rows.map(row => {
      const plan = {
        id: row.id,
        title: row.title,
        version: row.version,
        content: row.content,
        createdAt: row.created_at,
      };
      if (row.previous_plan_id) plan.previousPlanId = row.previous_plan_id;
      return plan;
    });
    const selectedPlanId = await getKey('selected_plan_id', '') || plans[0]?.id || '';
    return { plans, selectedPlanId };
  }

  async function savePlan(plan) {
    const id = await savePlanRow(plan);
    await setKey('selected_plan_id', id);
    return getPlans();
  }

  async function selectPlan(id) {
    await setKey('selected_plan_id', id || '');
    return getPlans();
  }

  async function planExists(id) {
    const result = await pool.query('SELECT 1 FROM plans WHERE id = $1', [id]);
    return result.rowCount > 0;
  }

  async function deletePlan(id) {
    await pool.query('DELETE FROM plans WHERE id = $1', [id]);
    const state = await getPlans();
    if (state.selectedPlanId === id) await setKey('selected_plan_id', state.plans[0]?.id || '');
    return getPlans();
  }

  async function getEntries() {
    const result = await pool.query('SELECT payload, trashed_at FROM meal_entries ORDER BY updated_at DESC');
    const entries = [];
    const trash = [];
    for (const row of result.rows) {
      if (row.trashed_at) trash.push(normalizeEntry(row.payload, row.trashed_at));
      else entries.push(normalizeEntry(row.payload, ''));
    }
    return { entries, trash };
  }

  async function upsertMealEntry(entry) {
    await saveEntryRow(entry, '');
    return getEntries();
  }

  async function moveEntriesToTrash(ids) {
    const result = await pool.query('SELECT payload FROM meal_entries WHERE trashed_at IS NULL AND id = ANY($1::text[])', [ids]);
    const trashedAt = nowIso();
    for (const row of result.rows) await saveEntryRow(row.payload, trashedAt);
    return getEntries();
  }

  async function restoreEntry(id) {
    const result = await pool.query('SELECT payload FROM meal_entries WHERE id = $1 AND trashed_at IS NOT NULL', [id]);
    if (!result.rows[0]) return null;
    await saveEntryRow(result.rows[0].payload, '');
    return getEntries();
  }

  async function clearTrash() {
    await pool.query('DELETE FROM meal_entries WHERE trashed_at IS NOT NULL');
    return getEntries();
  }

  function normalizeActivity(activity, fallbackSource = '') {
    const startAt = activity.startAt || activity.date || nowIso();
    const source = String(activity.source || fallbackSource || 'external');
    return {
      id: String(activity.id || `${source}-${startAt}`),
      source,
      sourceName: String(activity.sourceName || activity.source_name || source),
      type: String(activity.type || 'Activity'),
      title: String(activity.title || activity.type || 'Activity'),
      startAt: String(startAt),
      endAt: String(activity.endAt || activity.end_at || startAt),
      calories: Number(activity.calories || 0),
      steps: Number(activity.steps || 0),
      distanceMeters: Number(activity.distanceMeters || activity.distance_meters || 0),
      metadata: activity.metadata && typeof activity.metadata === 'object' ? activity.metadata : {},
      syncedAt: String(activity.syncedAt || nowIso()),
    };
  }

  async function getActivities(limit = 500) {
    const result = await pool.query(`
      SELECT id, source, source_name, type, title, start_at, end_at, calories, steps, distance_meters, metadata, synced_at
      FROM activities
      ORDER BY start_at DESC
      LIMIT $1
    `, [limit]);
    return {
      activities: result.rows.map(row => ({
        id: row.id,
        source: row.source,
        sourceName: row.source_name,
        type: row.type,
        title: row.title,
        startAt: row.start_at,
        endAt: row.end_at,
        calories: Number(row.calories || 0),
        steps: Number(row.steps || 0),
        distanceMeters: Number(row.distance_meters || 0),
        metadata: row.metadata || {},
        syncedAt: row.synced_at,
      })),
    };
  }

  async function upsertActivities(activities, source = '') {
    for (const item of activities) {
      const activity = normalizeActivity(item, source);
      await pool.query(`
        INSERT INTO activities (id, source, source_name, type, title, start_at, end_at, calories, steps, distance_meters, metadata, synced_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11::jsonb, $12, now())
        ON CONFLICT(id) DO UPDATE SET
          source = excluded.source,
          source_name = excluded.source_name,
          type = excluded.type,
          title = excluded.title,
          start_at = excluded.start_at,
          end_at = excluded.end_at,
          calories = excluded.calories,
          steps = excluded.steps,
          distance_meters = excluded.distance_meters,
          metadata = excluded.metadata,
          synced_at = excluded.synced_at,
          updated_at = excluded.updated_at
      `, [
        activity.id,
        activity.source,
        activity.sourceName,
        activity.type,
        activity.title,
        activity.startAt,
        activity.endAt,
        activity.calories,
        activity.steps,
        activity.distanceMeters,
        JSON.stringify(activity.metadata),
        activity.syncedAt,
      ]);
    }
    return getActivities();
  }

  await migrateLegacy();

  return {
    databaseLabel: maskDatabaseUrl(databaseUrl),
    getAuthConfig: () => getKey('auth_config', {}),
    saveAuthConfig: config => setKey('auth_config', config),
    getModelConfig: () => getKey('model_config', {}),
    saveModelConfig: config => setKey('model_config', config),
    getSettings: async () => ({ ...defaultSettings, ...await getKey('settings', defaultSettings) }),
    saveSettings: settings => setKey('settings', settings),
    getPlans,
    savePlan,
    selectPlan,
    planExists,
    deletePlan,
    getEntries,
    upsertMealEntry,
    moveEntriesToTrash,
    restoreEntry,
    clearTrash,
    getActivities,
    upsertActivities,
  };
}

module.exports = { createStore, defaultSettings };