/**
 * db/sso.js — SSO configuration and login audit persistence.
 * Owns: sso_configs CRUD, sso_login_log writes, domain-to-config lookup.
 * Does NOT own: SAML validation, session creation, user upsert — those live in services/saml.js and server.js.
 */

'use strict';

const pool = require('./index');

/**
 * Get the active SSO config for a company domain.
 * Returns null if not found or not active.
 * @param {string} domain
 * @returns {Promise<object|null>}
 */
async function getSsoConfigByDomain(domain) {
  const res = await pool.query(
    `SELECT * FROM sso_configs WHERE company_domain = :p1 AND is_active = TRUE FETCH FIRST 1 ROWS ONLY`,
    { p1: domain.toLowerCase().trim() }
  );
  return res.rows[0] || null;
}

/**
 * Get SSO config by ID (for settings management).
 * @param {number} id
 * @returns {Promise<object|null>}
 */
async function getSsoConfigById(id) {
  const res = await pool.query(`SELECT * FROM sso_configs WHERE id = :p1`, { p1: id });
  return res.rows[0] || null;
}

/**
 * Get SSO config for the authenticated user's company domain.
 * @param {string} userEmail
 * @returns {Promise<object|null>}
 */
async function getSsoConfigForUser(userEmail) {
  const domain = userEmail.split('@')[1]?.toLowerCase();
  if (!domain) return null;
  return getSsoConfigByDomain(domain);
}

/**
 * Upsert SSO config for a company domain. Creates or fully replaces.
 * @param {object} cfg
 * @returns {Promise<object>}
 */
async function upsertSsoConfig({
  company_domain,
  provider_type,
  sso_url,
  entity_id,
  certificate,
  attribute_mapping,
  group_role_mapping,
  default_role,
  is_active,
  require_sso,
}) {
  const dom = company_domain.toLowerCase().trim();
  await pool.query(
    `MERGE INTO sso_configs t
     USING (SELECT :p1 AS company_domain FROM DUAL) s
        ON (t.company_domain = s.company_domain)
     WHEN MATCHED THEN UPDATE SET
       provider_type      = :p2,
       sso_url            = :p3,
       entity_id          = :p4,
       certificate        = :p5,
       attribute_mapping  = :p6,
       group_role_mapping = :p7,
       default_role       = :p8,
       is_active          = :p9,
       require_sso        = :p10,
       updated_at         = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (company_domain, provider_type, sso_url, entity_id, certificate,
               attribute_mapping, group_role_mapping, default_role, is_active, require_sso,
               created_at, updated_at)
       VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)`,
    {
      p1: dom,
      p2: provider_type || 'custom',
      p3: sso_url,
      p4: entity_id,
      p5: certificate,
      p6: JSON.stringify(attribute_mapping || { email: 'nameID', name: 'displayName' }),
      p7: JSON.stringify(group_role_mapping || {}),
      p8: default_role || 'junior_dba',
      p9: pool.bool(is_active !== false),
      p10: pool.bool(require_sso === true),
    }
  );
  const res = await pool.query(`SELECT * FROM sso_configs WHERE company_domain = :p1`, { p1: dom });
  return res.rows[0];
}

/**
 * Delete SSO config for a domain.
 * @param {string} domain
 */
async function deleteSsoConfig(domain) {
  await pool.query(
    `DELETE FROM sso_configs WHERE company_domain = :p1`,
    { p1: domain.toLowerCase().trim() }
  );
}

/**
 * Toggle require_sso for a domain.
 * @param {string} domain
 * @param {boolean} required
 */
async function setRequireSso(domain, required) {
  await pool.query(
    `UPDATE sso_configs SET require_sso = :p2, updated_at = CURRENT_TIMESTAMP WHERE company_domain = :p1`,
    { p1: domain.toLowerCase().trim(), p2: pool.bool(required) }
  );
}

/**
 * Log an SSO login attempt (audit trail).
 * @param {object} entry
 */
async function logSsoAttempt({ company_domain, email, succeeded, failure_reason, ip, user_agent }) {
  await pool.query(
    `INSERT INTO sso_login_log (company_domain, email, succeeded, failure_reason, ip, user_agent, attempted_at)
     VALUES (:p1,:p2,:p3,:p4,:p5,:p6,CURRENT_TIMESTAMP)`,
    { p1: company_domain, p2: email || null, p3: pool.bool(succeeded), p4: failure_reason || null,
      p5: ip || null, p6: user_agent || null }
  ).catch(() => {}); // audit failures are non-fatal
}

/**
 * Check if any company has SSO configured (for login page button visibility).
 * @returns {Promise<boolean>}
 */
async function hasSsoConfigured() {
  const res = await pool.query(
    `SELECT 1 FROM sso_configs WHERE is_active = TRUE FETCH FIRST 1 ROWS ONLY`
  );
  return res.rows.length > 0;
}

module.exports = {
  getSsoConfigByDomain,
  getSsoConfigById,
  getSsoConfigForUser,
  upsertSsoConfig,
  deleteSsoConfig,
  setRequireSso,
  logSsoAttempt,
  hasSsoConfigured,
};
