// ADR-0079 — Personal Space Starter Pack v1
// Idempotent post-signup provisioning: 6 starter tables + Welcome Dashboard widget
// + Tor first-message conversation. Wired from /auth/register via AuthService.registerUser().

import { dbGet, dbAll, dbRun, withTransactionAsync, toBool, safeJsonParse } from '../../database/connection.js';
import { authLogger } from '../../utils/logger.js';
import { generateBaseId } from '../../utils/baseId.js';
import {
  STARTER_PROJECT_NAME,
  STARTER_PROJECT_ICON,
  STARTER_TABLES,
  TIER_A_AGENT_SLUGS,
  TIER_B_AGENT_SLUGS,
  TIER_B_UNLOCK_PROMOS,
  WELCOME_WIDGET_PRESET,
  WELCOME_WIDGET_TITLE,
  WELCOME_WIDGET_POSITION,
  FEATURE_FLAG_KEY,
  buildStarterTableSeeds
} from './starterPackCatalog.js';

const AGENTS_TABLE_ID = 1784;

/**
 * Read the kill-switch flag from _app_settings.
 * Default: enabled (fail-open — provisioning is best-effort).
 */
async function isFeatureEnabled() {
  try {
    const row = await dbGet('SELECT value FROM _app_settings WHERE key = ?', [FEATURE_FLAG_KEY]);
    if (!row) return true;
    const v = row.value;
    if (typeof v === 'boolean') return v;
    if (typeof v === 'string') return v === 'true' || v === '"true"';
    return safeJsonParse(v, true) === true;
  } catch (err) {
    authLogger.warn({ err }, '[StarterPack] feature_flag read failed — defaulting to enabled');
    return true;
  }
}

// ADR-183-A D6 — owner kill-switch for the promo mechanism. Same `_app_settings`
// read pattern as isFeatureEnabled; key `promo_enabled`. Default: ENABLED (fail-open,
// preserves pre-amendment behaviour — an absent row means promo is on). The owner flips
// it off post-campaign via the owner-only settings surface (settingsController).
export const PROMO_FLAG_KEY = 'promo_enabled';

export async function isPromoEnabled() {
  try {
    const row = await dbGet('SELECT value FROM _app_settings WHERE key = ?', [PROMO_FLAG_KEY]);
    if (!row) return true;
    const v = row.value;
    if (typeof v === 'boolean') return v;
    if (typeof v === 'string') return !(v === 'false' || v === '"false"');
    return safeJsonParse(v, true) !== false;
  } catch (err) {
    authLogger.warn({ err }, '[StarterPack] promo_enabled read failed — defaulting to enabled');
    return true;
  }
}

/**
 * Locate the user's personal Space + its default "Home" project.
 * Returns null if the personal space is missing (autoCreateDefaultProjects must run first).
 */
async function findPersonalHome(userId) {
  const space = await dbGet(
    `SELECT id, name FROM spaces WHERE owner_id = ? AND type = ? ORDER BY id LIMIT 1`,
    [userId, 'personal']
  );
  if (!space) return null;

  // Pick the lowest-id project — autoCreateDefaultProjects creates exactly one ("My Tasks").
  const project = await dbGet(
    `SELECT id, name FROM projects WHERE space_id = ? ORDER BY id LIMIT 1`,
    [space.id]
  );
  if (!project) return null;
  return { spaceId: space.id, projectId: project.id, projectName: project.name };
}

/**
 * Idempotency probe: if any of the named starter tables already exists in the project,
 * we treat the pack as provisioned and return true.
 */
async function isAlreadyProvisioned(projectId) {
  const names = STARTER_TABLES.map(t => t.name);
  const rows = await dbAll(
    `SELECT name FROM universal_tables
      WHERE project_id = ? AND deleted_at IS NULL AND name = ANY(?::text[])`,
    [projectId, names]
  );
  return rows.length > 0;
}

/**
 * Create one starter table inside the transaction.
 */
async function createStarterTable(trx, projectId, spec) {
  const tblResult = await trx.run(
    `INSERT INTO universal_tables (project_id, name, description, icon, is_system, show_in_nav, table_type)
     VALUES (?, ?, ?, ?, ?, 1, 'starter_pack')`,
    [projectId, spec.name, spec.description ?? null, spec.icon ?? null, toBool(false)]
  );
  const tableId = tblResult.lastInsertRowid || tblResult.lastID;

  for (const col of spec.columns) {
    await trx.run(
      `INSERT INTO table_columns (table_id, column_name, display_name, type, config,
                                  order_index, is_required, is_system)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        tableId,
        col.name,
        col.display ?? col.name,
        col.type,
        col.config ? JSON.stringify(col.config) : null,
        col.order ?? 0,
        toBool(!!col.is_required),
        toBool(!!col.is_system)
      ]
    );
  }
  return tableId;
}

/**
 * Seed sample rows into a freshly-created starter table. Dates are computed
 * per call (in buildStarterTableSeeds) so they stay anchored to "today" at
 * registration time. Skips silently if the catalog has no seed for the slug.
 *
 * Rows are inserted with `created_by = userId` so the user owns them and can
 * delete freely; an autogenerated 8-char `base_id` matches the canonical
 * shape used by tableRowCreateController.
 */
async function seedStarterTableRows(trx, slug, tableId, userId, seedsBySlug) {
  if (!slug) return 0;
  const rows = seedsBySlug[slug];
  if (!Array.isArray(rows) || rows.length === 0) return 0;

  let inserted = 0;
  for (const row of rows) {
    await trx.run(
      `INSERT INTO table_rows (table_id, base_id, data, created_by, created_at, updated_at)
       VALUES (?, ?, ?, ?, NOW(), NOW())`,
      [tableId, generateBaseId(), JSON.stringify(row), userId]
    );
    inserted += 1;
  }
  return inserted;
}

/**
 * Pin one `table_view` preset widget per starter table on the HOME project's
 * default dashboard, stacked vertically. ProjectService.createProject already
 * created the project dashboard ("My Tasks Dashboard" → renamed implicitly via
 * project rename) — we just look it up and INSERT widgets.
 *
 * Why this is separate from pinWelcomeWidget: the Welcome banner lives on the
 * SPACE dashboard (where SpaceDashboardPage lands), but the per-table nav
 * entries the user expects under "Home" in the sidebar are driven by widget
 * rows attached to the PROJECT dashboard. Without these, the project page
 * renders empty even though the tables exist in _databases.
 *
 * Idempotent: skips any (dashboard_id, table_id) pair already wired up.
 *
 * @param {object} trx
 * @param {number} projectId - Home project id
 * @param {Array<{name:string,id:number}>} tableIds - output of createStarterTable loop
 * @param {number} userId
 */
async function pinStarterTableWidgets(trx, projectId, tableIds, userId) {
  const dashboard = await trx.get(
    `SELECT id FROM dashboards
      WHERE project_id = ? AND is_default = ?
      ORDER BY id ASC LIMIT 1`,
    [projectId, toBool(true)]
  );
  if (!dashboard?.id) {
    authLogger.warn({ projectId }, '[StarterPack] project dashboard missing — table widgets skipped');
    return { dashboardId: null, widgetIds: [] };
  }
  const dashboardId = dashboard.id;

  const widgetIds = [];
  let order = 0;
  for (const t of tableIds) {
    const existing = await trx.get(
      `SELECT id FROM widgets
        WHERE dashboard_id = ? AND widget_type = 'preset' AND preset_name = 'table_view'
              AND (config::jsonb->>'table_id')::int = ?
        LIMIT 1`,
      [dashboardId, t.id]
    );
    if (existing) {
      widgetIds.push(existing.id);
      order += 1;
      continue;
    }

    const position = { x: 0, y: order * 4, w: 12, h: 4 };
    const res = await trx.run(
      `INSERT INTO widgets (dashboard_id, widget_type, preset_name, title, icon,
                            config, position, is_visible, order_index, owner_kind, owner_id, created_by)
       VALUES (?, 'preset', 'table_view', ?, '📋', ?, ?, 1, ?, 'dashboard', ?, ?)`,
      [
        dashboardId,
        t.name,
        JSON.stringify({ table_id: t.id }),
        JSON.stringify(position),
        order,
        dashboardId,
        userId
      ]
    );
    widgetIds.push(res.lastInsertRowid || res.lastID);
    order += 1;
  }
  return { dashboardId, widgetIds };
}

/**
 * Pin the Welcome widget on the SPACE's default dashboard.
 *
 * Why the space dashboard, not the project's: SpaceDashboardPage hits
 * `/api/v3/spaces/:id/dashboard` → `getSpaceDashboard()`, which returns the
 * dashboard row where `space_id = :id AND is_default = true` (lazily created
 * by createSpace as "<Space> Overview"). When the user clicks the Personal
 * Space tile after register/login they land on that dashboard — so the
 * welcome experience must live there. The project's own dashboard ("My Tasks
 * Dashboard") is only reachable one level deeper and was rendering empty.
 *
 * @param {object} trx - active transaction handle
 * @param {number} spaceId
 * @param {number} userId
 * @param {Record<string, number>} starterTablesMap - slug→tableId map consumed by
 *   the frontend WelcomeDashboardWidget to enable the per-table CTA cards. Required.
 */
async function pinWelcomeWidget(trx, spaceId, userId, starterTablesMap) {
  // Reuse the space-level default dashboard created by createSpace.
  let dashboard = await trx.get(
    `SELECT id FROM dashboards
      WHERE space_id = ? AND project_id IS NULL
      ORDER BY id ASC LIMIT 1`,
    [spaceId]
  );
  let dashboardId = dashboard?.id;

  if (!dashboardId) {
    const dRes = await trx.run(
      `INSERT INTO dashboards (user_id, space_id, project_id, name, description, icon, is_default, order_index)
       VALUES (?, ?, ?, ?, ?, ?, ?, 0)`,
      [null, spaceId, null, 'Home', null, '🏠', toBool(true)]
    );
    dashboardId = dRes.lastInsertRowid || dRes.lastID;
  }

  // Skip pinning if a welcome widget is already there (idempotent).
  const existing = await trx.get(
    `SELECT id FROM widgets WHERE dashboard_id = ? AND preset_name = ? LIMIT 1`,
    [dashboardId, WELCOME_WIDGET_PRESET]
  );
  if (existing) return { dashboardId, widgetId: existing.id };

  // Shift any existing widgets down so the welcome banner sits at the top.
  await trx.run(
    `UPDATE widgets
        SET position = jsonb_set(position::jsonb, '{y}', to_jsonb(COALESCE((position::jsonb->>'y')::int, 0) + ?))::text,
            order_index = order_index + 1
      WHERE dashboard_id = ?`,
    [WELCOME_WIDGET_POSITION.h, dashboardId]
  );

  const wRes = await trx.run(
    `INSERT INTO widgets (dashboard_id, widget_type, preset_name, title, icon,
                          config, position, is_visible, order_index, owner_kind, owner_id, created_by)
     VALUES (?, 'preset', ?, ?, '🏠', ?, ?, 1, 0, 'dashboard', ?, ?)`,
    [
      dashboardId,
      WELCOME_WIDGET_PRESET,
      WELCOME_WIDGET_TITLE,
      JSON.stringify({
        adr: 'ADR-0079',
        copy_source: 'shared/starter-pack-copy.json',
        starter_tables_map: starterTablesMap
      }),
      JSON.stringify(WELCOME_WIDGET_POSITION),
      dashboardId,
      userId
    ]
  );
  return { dashboardId, widgetId: wRes.lastInsertRowid || wRes.lastID };
}

/**
 * Spawn a Tor conversation and post the welcome message.
 * The 2s display delay is a client-side UX detail — backend writes once, frontend animates on load.
 */
async function scheduleTorFirstMessage(trx, userId, spaceId) {
  const tor = await trx.get(
    `SELECT id, data->>'name' AS name FROM table_rows
      WHERE table_id = ? AND data->>'agent_slug' = ? LIMIT 1`,
    [AGENTS_TABLE_ID, 'tor']
  );
  if (!tor) {
    authLogger.warn({ userId }, '[StarterPack] Tor agent row missing — first-message skipped');
    return null;
  }

  const convRes = await trx.run(
    `INSERT INTO conversations (type, title, created_by, space_id, agent_id, agent_table_id, settings)
     VALUES ('ai_chat', ?, ?, ?, ?, ?, ?)`,
    ['Tor', userId, spaceId, tor.id, AGENTS_TABLE_ID, JSON.stringify({ adr: 'ADR-0079', starter_pack: true })]
  );
  const conversationId = convRes.lastInsertRowid || convRes.lastID;

  // Inline the copy so the message is self-contained even if the JSON moves.
  const torContent = [
    'Hi 👋 I\'m Tor.',
    '',
    'I\'ll show you around in 5 minutes. You\'ve got six shelves here — daily log, goals, habits, people, ideas, wishlist. Each one is seeded with a few sample rows so you can see how they work; delete them any time.',
    '',
    'Where do you want to start — the journal, or a goal? If you\'re not even sure what a CRM is for you, just say so and I\'ll explain honestly.'
  ].join('\n');

  const msgRes = await trx.run(
    `INSERT INTO messages (conversation_id, sender_id, sender_type, role, content, content_type, agent_id)
     VALUES (?, ?, 'agent', 'assistant', ?, 'text', ?)`,
    [conversationId, null, torContent, tor.id]
  );
  const messageId = msgRes.lastInsertRowid || msgRes.lastID;

  await trx.run(
    `UPDATE conversations
        SET last_message_id = ?, last_message_at = NOW(),
            last_message_preview = ?, messages_count = 1
      WHERE id = ?`,
    [messageId, torContent.slice(0, 120), conversationId]
  );

  return { conversationId, messageId };
}

/**
 * Rename the auto-created project to "Home" if it still has its default name,
 * and flip `settings.is_starter_home = true` so the frontend can tell this
 * project apart from user-created ones (Welcome widget discovery / future
 * marketplace gating). Convention only — no schema change.
 */
async function renameToHome(trx, projectId, currentName) {
  // Always flip the starter flag — idempotent JSON merge.
  await trx.run(
    `UPDATE projects
        SET settings = jsonb_set(
              COALESCE(NULLIF(settings, '')::jsonb, '{}'::jsonb),
              '{is_starter_home}',
              'true'::jsonb,
              true
            )::text,
            updated_at = CURRENT_TIMESTAMP
      WHERE id = ?`,
    [projectId]
  );

  // Only rename the canonical auto-create name; don't clobber anything else.
  if (currentName === STARTER_PROJECT_NAME) return;
  if (currentName !== 'My Tasks') return;
  await trx.run(
    `UPDATE projects SET name = ?, icon = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
    [STARTER_PROJECT_NAME, STARTER_PROJECT_ICON, projectId]
  );
}

/**
 * Apply a Tier-B unlock to the user (called from /auth/register when promo_code matches).
 * Stores unlocked slugs inside users.agent_config JSONB.
 *
 * @param {number} userId
 * @param {string|null} promoCode
 * @returns {Promise<string[]>} List of unlocked slugs (empty if promo doesn't match).
 */
export async function applyPromoUnlock(userId, promoCode) {
  if (!promoCode) return [];
  const normalized = String(promoCode).trim().toUpperCase();
  if (!TIER_B_UNLOCK_PROMOS.includes(normalized)) return [];

  try {
    await dbRun(
      `UPDATE users
          SET agent_config = jsonb_set(
                COALESCE(agent_config, '{}'::jsonb),
                '{unlocked_agent_slugs}',
                to_jsonb(?::text[]),
                true
              ),
              updated_at = CURRENT_TIMESTAMP
        WHERE id = ?`,
      [TIER_B_AGENT_SLUGS, userId]
    );
    authLogger.info({ userId, promoCode: normalized, unlocked: TIER_B_AGENT_SLUGS }, '[StarterPack] Tier-B coding pack unlocked');
    return [...TIER_B_AGENT_SLUGS];
  } catch (err) {
    authLogger.error({ err, userId, promoCode: normalized }, '[StarterPack] promo unlock failed');
    return [];
  }
}

/**
 * Main entry — provision the starter pack for a freshly-registered user.
 * Idempotent: safe to re-run; no-ops when tables already exist.
 *
 * Failures are logged but DO NOT throw — registration must never fail because of cosmetics.
 */
export async function applyStarterPack(userId, _userName = null) {
  if (!(await isFeatureEnabled())) {
    authLogger.info({ userId }, '[StarterPack] feature flag off — skipping');
    return { skipped: true, reason: 'feature_disabled' };
  }

  try {
    const home = await findPersonalHome(userId);
    if (!home) {
      authLogger.warn({ userId }, '[StarterPack] personal space not found — autoCreateDefaultProjects must run first');
      return { skipped: true, reason: 'no_personal_space' };
    }

    if (await isAlreadyProvisioned(home.projectId)) {
      authLogger.info({ userId, projectId: home.projectId }, '[StarterPack] already provisioned — skipping');
      return { skipped: true, reason: 'already_provisioned' };
    }

    const result = await withTransactionAsync(async (trx) => {
      await renameToHome(trx, home.projectId, home.projectName);

      const seedsBySlug = buildStarterTableSeeds();
      const tableIds = [];
      const starterTablesMap = {};
      let seededRows = 0;
      for (const spec of STARTER_TABLES) {
        const tid = await createStarterTable(trx, home.projectId, spec);
        tableIds.push({ name: spec.name, id: tid });
        if (spec.slug) {
          starterTablesMap[spec.slug] = tid;
          seededRows += await seedStarterTableRows(trx, spec.slug, tid, userId, seedsBySlug);
        }
      }

      const tableWidgets = await pinStarterTableWidgets(trx, home.projectId, tableIds, userId);
      const widget = await pinWelcomeWidget(trx, home.spaceId, userId, starterTablesMap);
      const tor = await scheduleTorFirstMessage(trx, userId, home.spaceId);

      return { tableIds, tableWidgets, widget, tor, seededRows };
    });

    authLogger.info(
      {
        userId,
        spaceId: home.spaceId,
        projectId: home.projectId,
        tables: result.tableIds.length,
        seededRows: result.seededRows || 0,
        tableWidgets: result.tableWidgets?.widgetIds?.length || 0,
        widgetId: result.widget?.widgetId,
        torConversationId: result.tor?.conversationId
      },
      '[StarterPack] provisioned (ADR-0079)'
    );

    return { success: true, ...result, spaceId: home.spaceId, projectId: home.projectId };
  } catch (err) {
    authLogger.error({ err, userId }, '[StarterPack] provisioning failed (non-fatal)');
    return { skipped: true, reason: 'error', error: err.message };
  }
}

// Internal exports for tests.
export const __test = {
  isFeatureEnabled,
  findPersonalHome,
  isAlreadyProvisioned
};