/**
 * Role management queries.
 * Owns: roles table (per-team role definitions), approval_requests table.
 * Does NOT own: team membership, user auth, billing/subscriptions.
 */

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

// ─── Default role seed definitions (applied when a team is created) ───────────

const DEFAULT_ROLES = [
  // DBA branch
  {
    name: 'Lead DBA', slug: 'lead_dba', branch: 'dba', sort_order: 10,
    permissions: {
      can_diagnose: true, can_recommend: true, can_execute: true, can_approve: true,
      can_view_sql_console: true, can_view_cm_dashboard: true, can_view_perf_impact: true,
      can_view_patch_status: true, can_view_adop_internals: true,
      can_view_audit: 'team', can_export_compliance: false, report_visibility: 'team',
    },
  },
  {
    name: 'Senior DBA', slug: 'senior_dba', branch: 'dba', sort_order: 20,
    permissions: {
      can_diagnose: true, can_recommend: true, can_execute: true, can_approve: false,
      can_view_sql_console: true, can_view_cm_dashboard: true, can_view_perf_impact: true,
      can_view_patch_status: true, can_view_adop_internals: true,
      can_view_audit: 'own', can_export_compliance: false, report_visibility: 'own',
    },
  },
  {
    name: 'Junior DBA', slug: 'junior_dba', branch: 'dba', sort_order: 30,
    permissions: {
      can_diagnose: true, can_recommend: true, can_execute: false, can_approve: false,
      can_view_sql_console: true, can_view_cm_dashboard: true, can_view_perf_impact: true,
      can_view_patch_status: true, can_view_adop_internals: true,
      can_view_audit: 'own', can_export_compliance: false, report_visibility: 'own',
      requires_approval_to_execute: true,
    },
  },
  // Functional branch
  {
    name: 'Lead Functional', slug: 'lead_functional', branch: 'functional', sort_order: 40,
    permissions: {
      can_diagnose: false, can_recommend: false, can_execute: false, can_approve: false,
      can_view_sql_console: false, can_view_cm_dashboard: true, can_view_perf_impact: false,
      can_view_patch_status: true, can_view_adop_internals: false,
      can_view_audit: 'own', can_export_compliance: false, report_visibility: 'own',
      can_assign_tuneops: true,
    },
  },
  {
    name: 'Functional Analyst', slug: 'functional_analyst', branch: 'functional', sort_order: 50,
    permissions: {
      can_diagnose: false, can_recommend: false, can_execute: false, can_approve: false,
      can_view_sql_console: false, can_view_cm_dashboard: true, can_view_perf_impact: false,
      can_view_patch_status: true, can_view_adop_internals: false,
      can_view_audit: 'own', can_export_compliance: false, report_visibility: 'own',
    },
  },
  // Dev branch
  {
    name: 'Dev Lead', slug: 'dev_lead', branch: 'dev', sort_order: 60,
    permissions: {
      can_diagnose: false, can_recommend: false, can_execute: false, can_approve: false,
      can_view_sql_console: true, can_view_cm_dashboard: false, can_view_perf_impact: true,
      can_view_patch_status: true, can_view_adop_internals: false,
      can_view_audit: 'own', can_export_compliance: false, report_visibility: 'own',
      can_assign_tuneops: true, sql_console_readonly: false,
    },
  },
  {
    name: 'Developer', slug: 'developer', branch: 'dev', sort_order: 70,
    permissions: {
      can_diagnose: false, can_recommend: false, can_execute: false, can_approve: false,
      can_view_sql_console: true, can_view_cm_dashboard: false, can_view_perf_impact: true,
      can_view_patch_status: true, can_view_adop_internals: false,
      can_view_audit: 'own', can_export_compliance: false, report_visibility: 'own',
      sql_console_readonly: true,
    },
  },
  // Management branch
  {
    name: 'Manager / SDM', slug: 'manager', branch: 'management', sort_order: 80,
    permissions: {
      can_diagnose: false, can_recommend: false, can_execute: false, can_approve: true,
      can_view_sql_console: false, can_view_cm_dashboard: true, can_view_perf_impact: false,
      can_view_patch_status: true, can_view_adop_internals: false,
      can_view_audit: 'full', can_export_compliance: true, report_visibility: 'full',
      is_final_approver: true,
    },
  },
];

// ─── Role CRUD ─────────────────────────────────────────────────────────────────

async function seedDefaultRoles(teamId) {
  // Insert default roles for a new team, setting approval_required_from afterward
  const inserted = {};
  for (const r of DEFAULT_ROLES) {
    try {
      const ins = await pool.query(
        `INSERT INTO roles (team_id, name, slug, branch, permissions, is_default, sort_order)
         VALUES (:p1, :p2, :p3, :p4, :p5, TRUE, :p6)
         RETURNING id INTO :out_id`,
        { p1: teamId, p2: r.name, p3: r.slug, p4: r.branch, p5: JSON.stringify(r.permissions),
          p6: r.sort_order, out_id: pool.outNumber() }
      );
      inserted[r.slug] = ins.outBinds.out_id[0];
    } catch (err) {
      if (err.errorNum !== 1) throw err;   // ORA-00001: role already seeded (DO NOTHING)
    }
  }
  // junior_dba requires approval from lead_dba
  if (inserted['junior_dba'] && inserted['lead_dba']) {
    await pool.query(
      `UPDATE roles SET approval_required_from = :p2 WHERE id = :p1`,
      { p1: inserted['junior_dba'], p2: inserted['lead_dba'] }
    );
  }
  return inserted;
}

async function getRolesForTeam(teamId) {
  const { rows } = await pool.query(
    `SELECT r.*, ar.name AS approver_role_name
     FROM roles r
     LEFT JOIN roles ar ON ar.id = r.approval_required_from
     WHERE r.team_id = :p1
     ORDER BY r.sort_order ASC, r.name ASC`,
    { p1: teamId }
  );
  return rows;
}

async function getRoleById(roleId) {
  const { rows } = await pool.query(`SELECT * FROM roles WHERE id = :p1`, { p1: roleId });
  return rows[0] || null;
}

async function getRoleByTeamAndSlug(teamId, slug) {
  const { rows } = await pool.query(
    `SELECT * FROM roles WHERE team_id = :p1 AND slug = :p2`,
    { p1: teamId, p2: slug }
  );
  return rows[0] || null;
}

async function createRole({ teamId, name, slug, branch, permissions, approvalRequiredFrom, sortOrder }) {
  const ins = await pool.query(
    `INSERT INTO roles (team_id, name, slug, branch, permissions, approval_required_from, sort_order)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7)
     RETURNING id INTO :out_id`,
    { p1: teamId, p2: name, p3: slug, p4: branch, p5: JSON.stringify(permissions || {}),
      p6: approvalRequiredFrom || null, p7: sortOrder || 100, out_id: pool.outNumber() }
  );
  const { rows } = await pool.query(`SELECT * FROM roles WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

async function updateRole(roleId, teamId, { name, permissions, approvalRequiredFrom, sortOrder }) {
  // p4 feeds COALESCE(:p4, sort_order) onto a NUMBER column; a null bind is CHAR
  // there (ORA-00932), so it is typed. p1 -> COALESCE(:p1, name) is CHAR-vs-VARCHAR2
  // and needs no typing.
  const binds = { p1: name || null, p3: approvalRequiredFrom ?? null, p4: pool.num(sortOrder || null), p5: roleId, p6: teamId };
  const setParts = ['name = COALESCE(:p1, name)'];
  if (permissions) { setParts.push('permissions = :p2'); binds.p2 = JSON.stringify(permissions); }
  setParts.push('approval_required_from = :p3');
  setParts.push('sort_order = COALESCE(:p4, sort_order)');
  setParts.push('updated_at = CURRENT_TIMESTAMP');
  const upd = await pool.query(
    `UPDATE roles SET ${setParts.join(', ')} WHERE id = :p5 AND team_id = :p6`,
    binds
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM roles WHERE id = :p5 AND team_id = :p6`, { p5: roleId, p6: teamId });
  return rows[0] || null;
}

async function deleteRole(roleId, teamId) {
  // Only non-default roles can be deleted
  const del = await pool.query(
    `DELETE FROM roles WHERE id = :p1 AND team_id = :p2 AND is_default = FALSE RETURNING id INTO :out_id`,
    { p1: roleId, p2: teamId, out_id: pool.outNumber() }
  );
  const arr = (del.outBinds && del.outBinds.out_id) || [];
  return arr.length ? { id: arr[0] } : null;
}

// ─── Member role assignment ────────────────────────────────────────────────────

async function assignRoleToMember(teamId, userId, roleId) {
  // Sets both the new role_id and updates legacy role string to branch-based value
  const role = await getRoleById(roleId);
  if (!role || role.team_id !== teamId) throw new Error('Role not found or wrong team');

  // team_members has no updated_at column (pre-existing bug #8a) — dropped.
  const upd = await pool.query(
    `UPDATE team_members SET role_id = :p1
     WHERE team_id = :p2 AND user_id = :p3`,
    { p1: roleId, p2: teamId, p3: userId }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(
    `SELECT * FROM team_members WHERE team_id = :p1 AND user_id = :p2`, { p1: teamId, p2: userId });
  return rows[0] || null;
}

async function getMemberRoleDetails(teamId, userId) {
  const { rows } = await pool.query(
    `SELECT tm.role, tm.role_id, r.name AS role_name, r.branch, r.permissions,
            r.approval_required_from, ar.name AS approver_role_name, ar.id AS approver_role_id
     FROM team_members tm
     LEFT JOIN roles r ON r.id = tm.role_id
     LEFT JOIN roles ar ON ar.id = r.approval_required_from
     WHERE tm.team_id = :p1 AND tm.user_id = :p2`,
    { p1: teamId, p2: userId }
  );
  return rows[0] || null;
}

// ─── Approval requests ─────────────────────────────────────────────────────────

async function createApprovalRequest({ ticketId, requesterId, approverRoleId, context }) {
  const ins = await pool.query(
    `INSERT INTO approval_requests (ticket_id, requester_id, approver_role_id, context)
     VALUES (:p1, :p2, :p3, :p4)
     RETURNING id INTO :out_id`,
    { p1: ticketId || null, p2: requesterId, p3: approverRoleId, p4: JSON.stringify(context || {}),
      out_id: pool.outNumber() }
  );
  const { rows } = await pool.query(`SELECT * FROM approval_requests WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

async function getPendingApprovalRequestsForRole(approverRoleId) {
  const { rows } = await pool.query(
    `SELECT ar.*, u.email AS requester_email, u.name AS requester_name
     FROM approval_requests ar
     JOIN users u ON u.id = ar.requester_id
     WHERE ar.approver_role_id = :p1 AND ar.status = 'pending'
     ORDER BY ar.created_at ASC`,
    { p1: approverRoleId }
  );
  return rows;
}

async function getPendingApprovalRequestsForTeam(teamId) {
  // Returns pending approvals where the approver_role belongs to this team
  const { rows } = await pool.query(
    `SELECT ar.*, u.email AS requester_email, u.name AS requester_name,
            r.name AS approver_role_name, r.branch
     FROM approval_requests ar
     JOIN users u ON u.id = ar.requester_id
     JOIN roles r ON r.id = ar.approver_role_id
     WHERE r.team_id = :p1 AND ar.status = 'pending'
     ORDER BY ar.created_at ASC`,
    { p1: teamId }
  );
  return rows;
}

async function getApprovalRequestById(id) {
  const { rows } = await pool.query(
    `SELECT ar.*, u.email AS requester_email, u.name AS requester_name,
            r.name AS approver_role_name, r.branch, r.team_id
     FROM approval_requests ar
     JOIN users u ON u.id = ar.requester_id
     JOIN roles r ON r.id = ar.approver_role_id
     WHERE ar.id = :p1`,
    { p1: id }
  );
  return rows[0] || null;
}

async function approveRequest(id, approverId) {
  const upd = await pool.query(
    `UPDATE approval_requests
     SET status = 'approved', approved_by = :p1, approved_at = CURRENT_TIMESTAMP
     WHERE id = :p2 AND status = 'pending'`,
    { p1: approverId, p2: id }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM approval_requests WHERE id = :p1`, { p1: id });
  return rows[0] || null;
}

async function rejectRequest(id, approverId, reason) {
  const upd = await pool.query(
    `UPDATE approval_requests
     SET status = 'rejected', approved_by = :p1, rejected_at = CURRENT_TIMESTAMP, rejection_reason = :p2
     WHERE id = :p3 AND status = 'pending'`,
    { p1: approverId, p2: reason || null, p3: id }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM approval_requests WHERE id = :p1`, { p1: id });
  return rows[0] || null;
}

async function getApprovalRequestsForRequester(requesterId) {
  const { rows } = await pool.query(
    `SELECT ar.*, r.name AS approver_role_name, au.email AS approver_email
     FROM approval_requests ar
     JOIN roles r ON r.id = ar.approver_role_id
     LEFT JOIN users au ON au.id = ar.approved_by
     WHERE ar.requester_id = :p1
     ORDER BY ar.created_at DESC
     FETCH FIRST 50 ROWS ONLY`,
    { p1: requesterId }
  );
  return rows;
}

// ─── Permission check helper ───────────────────────────────────────────────────

/**
 * Check if a user has a specific permission key.
 * Falls back gracefully to legacy role string if role_id not yet assigned.
 */
async function userHasPermission(userId, teamId, permissionKey) {
  const memberRole = await getMemberRoleDetails(teamId, userId);
  if (!memberRole) return true; // Not on team → individual account → full access

  if (memberRole.permissions) {
    const perm = memberRole.permissions[permissionKey];
    if (perm === undefined) return false;
    return Boolean(perm);
  }
  return false;
}

// ─── Patch status query (role-filtered) ───────────────────────────────────────

/**
 * Returns patch-related check results from the latest completed health check run.
 * Route filters the result set based on branch permissions.
 */
async function getPatchStatusForConnection(connectionId) {
  // pre-existing bug #8b: this query named columns that never shipped on
  // check_results (result/severity/category/details/health_check_id) and joined
  // health_checks on a non-existent FK. The real shape is: status (holds the
  // severity-like verdict), check_category, recommendation, executed_at, and a
  // UUID run_id that is INDEPENDENT of health_checks.id (same fact db/fleet.js
  // relies on). So the latest run is derived from check_results alone — the most
  // recent run_id for this connection — rather than by joining health_checks.
  // Aliased back to the keys the /api/patches/status widget reads.
  const { rows } = await pool.query(
    `SELECT cr.check_id,
            cr.status         AS result,
            cr.status         AS severity,
            cr.check_category AS category,
            cr.recommendation AS details,
            cr.executed_at    AS run_at
     FROM check_results cr
     WHERE cr.connection_id = :p1
       AND cr.run_id = (
         SELECT run_id FROM check_results
         WHERE connection_id = :p1
         GROUP BY run_id
         ORDER BY MAX(executed_at) DESC
         FETCH FIRST 1 ROW ONLY
       )
       AND (cr.check_category = 'patching'
            OR LOWER(cr.check_id) LIKE '%patch%'
            OR LOWER(cr.check_id) LIKE '%adop%')
     ORDER BY cr.executed_at DESC`,
    { p1: connectionId }
  );
  return rows;
}

module.exports = {
  DEFAULT_ROLES,
  seedDefaultRoles,
  getRolesForTeam,
  getRoleById,
  getRoleByTeamAndSlug,
  createRole,
  updateRole,
  deleteRole,
  assignRoleToMember,
  getMemberRoleDetails,
  createApprovalRequest,
  getPendingApprovalRequestsForRole,
  getPendingApprovalRequestsForTeam,
  getApprovalRequestById,
  approveRequest,
  rejectRequest,
  getApprovalRequestsForRequester,
  userHasPermission,
  getPatchStatusForConnection,
};
