/**
 * Team management queries.
 * Owns: teams, team_members, team_invites tables.
 * Does NOT own: user auth, session management, billing/subscriptions.
 */

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

// --- Teams ---

async function createTeam({ name, ownerId, planTier = 'team' }) {
  const ins = await pool.query(
    `INSERT INTO teams (name, owner_id, plan_tier) VALUES (:p1, :p2, :p3) RETURNING id INTO :out_id`,
    { p1: name, p2: ownerId, p3: planTier, out_id: pool.outNumber() }
  );
  const { rows } = await pool.query(`SELECT * FROM teams WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

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

async function getTeamByOwnerId(ownerId) {
  const { rows } = await pool.query(`SELECT * FROM teams WHERE owner_id = :p1 FETCH FIRST 1 ROWS ONLY`, { p1: ownerId });
  return rows[0] || null;
}

async function getTeamForUser(userId) {
  // Returns team + the member's role for this user
  const { rows } = await pool.query(
    `SELECT t.*, tm.role AS user_role
     FROM teams t
     JOIN team_members tm ON tm.team_id = t.id
     WHERE tm.user_id = :p1
     FETCH FIRST 1 ROWS ONLY`,
    { p1: userId }
  );
  return rows[0] || null;
}

async function updateTeamName(teamId, name) {
  const upd = await pool.query(
    `UPDATE teams SET name = :p2, updated_at = CURRENT_TIMESTAMP WHERE id = :p1`,
    { p1: teamId, p2: name }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM teams WHERE id = :p1`, { p1: teamId });
  return rows[0] || null;
}

// --- Team Members ---

async function addTeamMember({ teamId, userId, role = 'viewer' }) {
  await pool.query(
    `MERGE INTO team_members t
     USING (SELECT :p1 AS team_id, :p2 AS user_id, :p3 AS role FROM DUAL) s
        ON (t.team_id = s.team_id AND t.user_id = s.user_id)
     WHEN MATCHED THEN UPDATE SET role = s.role
     WHEN NOT MATCHED THEN INSERT (team_id, user_id, role) VALUES (s.team_id, s.user_id, s.role)`,
    { p1: teamId, p2: userId, p3: role }
  );
  const { rows } = await pool.query(
    `SELECT * FROM team_members WHERE team_id = :p1 AND user_id = :p2`, { p1: teamId, p2: userId });
  // Also update users.team_id for quick lookup
  await pool.query(`UPDATE users SET team_id = :p1 WHERE id = :p2`, { p1: teamId, p2: userId });
  return rows[0];
}

async function getTeamMembers(teamId) {
  const { rows } = await pool.query(
    `SELECT tm.id, tm.team_id, tm.user_id, tm.role, tm.joined_at,
            u.email, u.name
     FROM team_members tm
     JOIN users u ON u.id = tm.user_id
     WHERE tm.team_id = :p1
     ORDER BY tm.joined_at ASC`,
    { p1: teamId }
  );
  return rows;
}

async function getMemberRole(teamId, userId) {
  const { rows } = await pool.query(
    `SELECT role FROM team_members WHERE team_id = :p1 AND user_id = :p2`,
    { p1: teamId, p2: userId }
  );
  return rows[0]?.role || null;
}

async function updateMemberRole(teamId, userId, role) {
  const upd = await pool.query(
    `UPDATE team_members SET role = :p1 WHERE team_id = :p2 AND user_id = :p3`,
    { p1: role, 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 removeTeamMember(teamId, userId) {
  await pool.query(
    `DELETE FROM team_members WHERE team_id = :p1 AND user_id = :p2`,
    { p1: teamId, p2: userId }
  );
  // Clear team_id from users table
  await pool.query(
    `UPDATE users SET team_id = NULL WHERE id = :p1 AND team_id = :p2`,
    { p1: userId, p2: teamId }
  );
}

async function getTeamMemberCount(teamId) {
  const { rows } = await pool.query(
    `SELECT COUNT(*) AS "count" FROM team_members WHERE team_id = :p1`,
    { p1: teamId }
  );
  return parseInt(rows[0].count, 10);
}

// --- Team Invites ---

async function createInvite({ teamId, invitedBy, email, role, token }) {
  const ins = await pool.query(
    `INSERT INTO team_invites (team_id, invited_by, email, role, token)
     VALUES (:p1, :p2, :p3, :p4, :p5)
     RETURNING id INTO :out_id`,
    { p1: teamId, p2: invitedBy, p3: email.toLowerCase(), p4: role, p5: token, out_id: pool.outNumber() }
  );
  const { rows } = await pool.query(`SELECT * FROM team_invites WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

async function getInviteByToken(token) {
  const { rows } = await pool.query(
    `SELECT ti.*, t.name AS team_name, u.name AS inviter_name, u.email AS inviter_email
     FROM team_invites ti
     JOIN teams t ON t.id = ti.team_id
     JOIN users u ON u.id = ti.invited_by
     WHERE ti.token = :p1`,
    { p1: token }
  );
  return rows[0] || null;
}

async function getPendingInvitesForTeam(teamId) {
  const { rows } = await pool.query(
    `SELECT ti.*, u.name AS inviter_name
     FROM team_invites ti
     JOIN users u ON u.id = ti.invited_by
     WHERE ti.team_id = :p1 AND ti.status = 'pending' AND ti.expires_at > CURRENT_TIMESTAMP
     ORDER BY ti.created_at DESC`,
    { p1: teamId }
  );
  return rows;
}

async function getPendingInviteForEmail(teamId, email) {
  const { rows } = await pool.query(
    `SELECT * FROM team_invites
     WHERE team_id = :p1 AND email = :p2 AND status = 'pending' AND expires_at > CURRENT_TIMESTAMP`,
    { p1: teamId, p2: email.toLowerCase() }
  );
  return rows[0] || null;
}

async function updateInviteStatus(token, status) {
  const upd = await pool.query(
    `UPDATE team_invites SET status = :p1 WHERE token = :p2`,
    { p1: status, p2: token }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM team_invites WHERE token = :p1`, { p1: token });
  return rows[0] || null;
}

async function revokeInviteById(inviteId, teamId) {
  const upd = await pool.query(
    `UPDATE team_invites SET status = 'revoked'
     WHERE id = :p1 AND team_id = :p2`,
    { p1: inviteId, p2: teamId }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM team_invites WHERE id = :p1`, { p1: inviteId });
  return rows[0] || null;
}

async function resendInvite(inviteId, teamId, newToken) {
  // Extend expiry and issue fresh token
  const upd = await pool.query(
    `UPDATE team_invites
     SET token = :p1, status = 'pending', expires_at = CURRENT_TIMESTAMP + INTERVAL '7' DAY, created_at = CURRENT_TIMESTAMP
     WHERE id = :p2 AND team_id = :p3`,
    { p1: newToken, p2: inviteId, p3: teamId }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM team_invites WHERE id = :p1`, { p1: inviteId });
  return rows[0] || null;
}

module.exports = {
  createTeam,
  getTeamById,
  getTeamByOwnerId,
  getTeamForUser,
  updateTeamName,
  addTeamMember,
  getTeamMembers,
  getMemberRole,
  updateMemberRole,
  removeTeamMember,
  getTeamMemberCount,
  createInvite,
  getInviteByToken,
  getPendingInvitesForTeam,
  getPendingInviteForEmail,
  updateInviteStatus,
  revokeInviteById,
  resendInvite,
};
