/**
 * Repository for settings
 * Handles all database operations for settings table
 */

// Allowlist of valid setting keys (security: prevent arbitrary key injection)
export const VALID_SETTING_KEYS = [
  // AI Providers
  'OPENAI_API_KEY', 'OPENAI_ORG_ID', 'ANTHROPIC_API_KEY', 'GROQ_API_KEY',
  'TOGETHER_API_KEY', 'REPLICATE_API_TOKEN', 'HUGGINGFACE_API_KEY',
  'PERPLEXITY_API_KEY', 'ELEVENLABS_API_KEY', 'ELEVENLABS_VOICE_ID',
  // Databases
  'DATABASE_URL', 'SUPABASE_URL', 'SUPABASE_ANON_KEY', 'SUPABASE_SERVICE_KEY',
  'PLANETSCALE_URL', 'MONGODB_URI', 'REDIS_URL', 'PINECONE_API_KEY', 'PINECONE_ENVIRONMENT',
  // Communication
  'TELEGRAM_BOT_TOKEN', 'TELEGRAM_CHAT_ID', 'DISCORD_BOT_TOKEN', 'DISCORD_CLIENT_ID',
  'DISCORD_GUILD_ID', 'SLACK_BOT_TOKEN', 'SLACK_SIGNING_SECRET', 'SLACK_APP_TOKEN',
  'TWILIO_ACCOUNT_SID', 'TWILIO_AUTH_TOKEN', 'TWILIO_PHONE_NUMBER',
  'RESEND_API_KEY', 'SENDGRID_API_KEY',
  // Productivity
  'GOOGLE_ACCOUNT', 'GOOGLE_CREDENTIALS_PATH', 'NOTION_API_KEY', 'NOTION_PARENT_PAGE_ID',
  'LINEAR_API_KEY', 'AIRTABLE_API_KEY', 'AIRTABLE_BASE_ID', 'CALENDLY_API_KEY',
  // Development
  'GITHUB_TOKEN', 'GITHUB_USERNAME', 'VERCEL_TOKEN', 'VERCEL_PROJECT_ID',
  'RAILWAY_TOKEN', 'CLOUDFLARE_API_TOKEN', 'CLOUDFLARE_ACCOUNT_ID',
  'SENTRY_DSN', 'SENTRY_AUTH_TOKEN',
  // Social
  'TWITTER_API_KEY', 'TWITTER_API_SECRET', 'TWITTER_ACCESS_TOKEN', 'TWITTER_ACCESS_SECRET',
  'BRAVE_API_KEY', 'MOLTBOOK_API_KEY',
  // Payments
  'STRIPE_SECRET_KEY', 'STRIPE_PUBLISHABLE_KEY', 'STRIPE_WEBHOOK_SECRET',
  'LEMONSQUEEZY_API_KEY',
  // Native governance alert settings
  'DASHCLAW_ALERTS_SLACK', 'DASHCLAW_ALERTS_DISCORD', 'DASHCLAW_ALERTS_LINEAR',
  'DASHCLAW_ALERTS_GITHUB', 'DASHCLAW_ALERTS_EMAIL',
  'DASHCLAW_ALERT_EMAIL', 'SLACK_CHANNEL_ID', 'SLACK_WEBHOOK_URL',
  'DISCORD_WEBHOOK_URL', 'GITHUB_REPO',
  'SENDGRID_DEFAULT_TO', 'SENDGRID_FROM_EMAIL',
  // Cost alerts — per-action cost cap in USD. When a PATCH resolves with
  // cost_estimate > threshold, the action.cost_exceeded event fires and
  // webhooks + native adapters deliver a signal. Empty/unset = disabled.
  'DASHCLAW_ACTION_COST_THRESHOLD',
  // System configuration
  'MODEL_PRICING',
  'ENFORCE_AGENT_SIGNATURES',
  // Predictive risk scoring
  'PREDICTIVE_RISK_ENABLED',
  'PREDICTIVE_RISK_THRESHOLD',
  // Durable execution finality — minutes a pending outcome may live before the
  // sweep marks it lost_confirmation. Default 15. See
  // docs/architecture/durable-execution-finality.md.
  'DASHCLAW_OUTCOME_TIMEOUT_MINUTES',
];

export const VALID_CATEGORIES = ['integration', 'general', 'system'];

/**
 * Ensure settings table exists
 */
export async function ensureSettingsTable(sql) {
  await sql`
    CREATE TABLE IF NOT EXISTS settings (
      id SERIAL PRIMARY KEY,
      org_id TEXT NOT NULL DEFAULT 'org_default',
      agent_id TEXT,
      key TEXT NOT NULL,
      value TEXT,
      category TEXT DEFAULT 'general',
      encrypted BOOLEAN DEFAULT false,
      updated_at TIMESTAMP DEFAULT NOW()
    )
  `;
  await sql`
    CREATE UNIQUE INDEX IF NOT EXISTS settings_org_agent_key_unique
    ON settings (org_id, COALESCE(agent_id, ''), key)
  `;
}

/**
 * Get settings with optional filters
 * When agentId is provided, returns merged settings (agent overrides org defaults)
 */
export async function getSettings(sql, orgId, { key, category, agentId } = {}) {
  let settings;

  if (agentId) {
    // Merged query: agent-specific rows override org-level defaults
    // DISTINCT ON (key) with ORDER BY agent_id NULLS LAST means agent-specific wins
    if (key) {
      settings = await sql`
        SELECT DISTINCT ON (key) *,
          CASE WHEN agent_id IS NULL THEN true ELSE false END AS is_inherited
        FROM settings
        WHERE org_id = ${orgId} AND (agent_id = ${agentId} OR agent_id IS NULL) AND key = ${key}
        ORDER BY key, agent_id NULLS LAST
      `;
    } else if (category) {
      settings = await sql`
        SELECT DISTINCT ON (key) *,
          CASE WHEN agent_id IS NULL THEN true ELSE false END AS is_inherited
        FROM settings
        WHERE org_id = ${orgId} AND (agent_id = ${agentId} OR agent_id IS NULL) AND category = ${category}
        ORDER BY key, agent_id NULLS LAST
      `;
    } else {
      settings = await sql`
        SELECT DISTINCT ON (key) *,
          CASE WHEN agent_id IS NULL THEN true ELSE false END AS is_inherited
        FROM settings
        WHERE org_id = ${orgId} AND (agent_id = ${agentId} OR agent_id IS NULL)
        ORDER BY key, agent_id NULLS LAST
      `;
    }
  } else {
    // Org-level only (no agent filter)
    if (key) {
      settings = await sql`SELECT *, false AS is_inherited FROM settings WHERE key = ${key} AND org_id = ${orgId} AND agent_id IS NULL`;
    } else if (category) {
      settings = await sql`SELECT *, false AS is_inherited FROM settings WHERE category = ${category} AND org_id = ${orgId} AND agent_id IS NULL ORDER BY key`;
    } else {
      settings = await sql`SELECT *, false AS is_inherited FROM settings WHERE org_id = ${orgId} AND agent_id IS NULL ORDER BY category, key`;
    }
  }

  return settings;
}

/**
 * Upsert a setting
 */
export async function upsertSetting(sql, orgId, { key, value, category = 'general', encrypted = false, agent_id = null }) {
  // Validation
  if (!key) {
    throw new Error('Key is required');
  }
  if (!VALID_SETTING_KEYS.includes(key)) {
    throw new Error(`Invalid setting key: ${key}`);
  }
  if (!VALID_CATEGORIES.includes(category)) {
    throw new Error(`Invalid category: ${category}`);
  }
  if (value && value.length > 10000) {
    throw new Error('Value too long (max 10000 chars)');
  }
  if (agent_id && agent_id.length > 255) {
    throw new Error('agent_id too long (max 255 chars)');
  }

  // Use COALESCE-based conflict target matching the functional unique index
  await sql`
    INSERT INTO settings (org_id, agent_id, key, value, category, encrypted, updated_at)
    VALUES (${orgId}, ${agent_id}, ${key}, ${value}, ${category}, ${encrypted}, NOW())
    ON CONFLICT (org_id, COALESCE(agent_id, ''), key) DO UPDATE SET
      value = ${value},
      category = ${category},
      encrypted = ${encrypted},
      updated_at = NOW()
  `;
}

/**
 * Delete a setting
 * When agentId is provided, deletes agent-specific row only
 */
export async function deleteSetting(sql, orgId, key, agentId = null) {
  if (!key) {
    throw new Error('Key is required');
  }

  if (agentId) {
    await sql`DELETE FROM settings WHERE key = ${key} AND org_id = ${orgId} AND agent_id = ${agentId}`;
  } else {
    await sql`DELETE FROM settings WHERE key = ${key} AND org_id = ${orgId} AND agent_id IS NULL`;
  }
}

/**
 * Load custom model pricing for an org.
 * Returns a parsed pricing array or null if none is configured.
 */
export async function getModelPricing(sql, orgId) {
  try {
    const rows = await sql`SELECT value FROM settings WHERE org_id = ${orgId} AND key = 'MODEL_PRICING' AND agent_id IS NULL LIMIT 1`;
    if (rows.length > 0 && rows[0].value) {
      const parsed = JSON.parse(rows[0].value);
      if (Array.isArray(parsed) && parsed.length > 0) return parsed;
    }
  } catch {
    // Gracefully fall back to default pricing on any error
  }
  return null;
}

/**
 * Helper to mask sensitive values
 */
export function maskValue(value) {
  if (!value) return '';
  if (value.length <= 8) return '••••••••';
  return value.substring(0, 4) + '••••••••' + value.substring(value.length - 4);
}

/**
 * Check if a key should be auto-encrypted
 */
export function shouldAutoEncrypt(key, encrypted) {
  if (encrypted) return true; // Already marked for encryption

  const SENSITIVE_SUFFIXES = ['_KEY', '_TOKEN', '_SECRET', '_URL', '_URI', '_DSN', '_PASSWORD', '_ID', '_CREDENTIALS_PATH'];
  const EXCEPTIONS = ['TELEGRAM_CHAT_ID', 'DISCORD_CLIENT_ID', 'DISCORD_GUILD_ID', 'VERCEL_PROJECT_ID', 'CLOUDFLARE_ACCOUNT_ID', 'AIRTABLE_BASE_ID', 'ELEVENLABS_VOICE_ID', 'OPENAI_ORG_ID'];

  return SENSITIVE_SUFFIXES.some(s => key.endsWith(s)) && !EXCEPTIONS.includes(key);
}
