import type { Database } from 'bun:sqlite'

/**
 * List all sessions visible to a given user.
 *
 * Visibility rules:
 * 1. System admin (users.role = 'admin') → sees everything including orphans
 * 2. Sessions owned by the user (owner_type='user', owner_id=userId)
 * 3. Sessions owned by a team the user belongs to, with visibility='team' or 'public'
 * 4. Sessions explicitly shared with the user via session_shares
 * 5. Sessions explicitly shared with a team the user belongs to via session_shares
 * 6. Sessions with visibility='public' (anyone can see)
 * 7. Orphan sessions (no owner) → only admin visible
 */
export function storeListSessionsVisibleToUser(
  userId: string,
  db: Database,
): Array<Record<string, unknown>> {
  // Check if user is a system admin
  const userRow = db
    .query('SELECT role FROM users WHERE id = $userId')
    .get({ $userId: userId }) as { role: string } | null

  const isSystemAdmin = userRow?.role === 'admin'

  if (isSystemAdmin) {
    // Admin sees all sessions
    return db.query('SELECT * FROM sessions').all() as Array<
      Record<string, unknown>
    >
  }

  // Build a query that unions all visibility rules
  // Note: expires_at is stored as ISO 8601 (e.g. "2026-07-21T12:00:00.000Z"),
  // so we use strftime to produce a comparable ISO string for "now".
  const sql = `
    SELECT DISTINCT s.* FROM sessions s
    WHERE
      -- Rule 2: owned by user
      EXISTS (
        SELECT 1 FROM session_owners so
        WHERE so.session_id = s.id
          AND so.owner_type = 'user'
          AND so.owner_id = $userId
      )
      -- Rule 3: owned by user's team + visibility team/public
      OR (
        s.visibility IN ('team', 'public')
        AND EXISTS (
          SELECT 1 FROM session_owners so
          JOIN team_members tm ON tm.team_id = so.owner_id
          WHERE so.session_id = s.id
            AND so.owner_type = 'team'
            AND tm.user_id = $userId
        )
      )
      -- Rule 4: explicitly shared with user (non-expired)
      OR EXISTS (
        SELECT 1 FROM session_shares ss
        WHERE ss.session_id = s.id
          AND ss.granted_to_user = $userId
          AND (ss.expires_at IS NULL OR ss.expires_at > strftime('%Y-%m-%dT%H:%M:%S.000Z', 'now'))
      )
      -- Rule 5: shared with a team the user belongs to (non-expired)
      OR EXISTS (
        SELECT 1 FROM session_shares ss
        JOIN team_members tm ON tm.team_id = ss.granted_to_team
        WHERE ss.session_id = s.id
          AND tm.user_id = $userId
          AND (ss.expires_at IS NULL OR ss.expires_at > strftime('%Y-%m-%dT%H:%M:%S.000Z', 'now'))
      )
      -- Rule 6: public visibility
      OR s.visibility = 'public'
  `

  return db.query(sql).all({ $userId: userId }) as Array<
    Record<string, unknown>
  >
}