import Database from 'better-sqlite3';
import { join } from 'path';

// ── Types ────────────────────────────────────────────────────────

export type Role = 'pilot' | 'cab' | 'fenja';
export type ContributionType = 'idea' | 'inspiration' | 'question';
export type AttendanceStatus = 'yes' | 'no';

export interface User {
  id: number;
  email: string;
  password_hash: string;
  name: string;
  organisation: string;
  role: Role;
  bio: string;
  created_at: string;
  last_seen_at: string | null;
  active: number;
}

export type UserPublic = Omit<User, 'password_hash'>;

export interface Invite {
  id: number;
  token_hash: string;
  email: string;
  name: string;
  organisation: string;
  role: Role;
  expires_at: string;
  used_at: string | null;
  created_at: string;
  created_by_user_id: number | null;
}

export interface Contribution {
  id: number;
  user_id: number;
  type: ContributionType;
  body_md: string;
  created_at: string;
  edited_at: string | null;
  hidden_at: string | null;
}

export interface ContributionRow extends Contribution {
  author_name: string;
  author_organisation: string;
  author_role: Role;
  reaction_count: number;
}

export interface Reply {
  id: number;
  contribution_id: number;
  user_id: number;
  body_md: string;
  created_at: string;
  author_name: string;
  author_role: Role;
}

export interface AttendanceSummary {
  yes: number;
  no: number;
}

// ── Connection ───────────────────────────────────────────────────

// Persist across Vite HMR reloads in dev
const g = globalThis as typeof globalThis & { __bifrost_db?: Database.Database };
if (!g.__bifrost_db) {
  g.__bifrost_db = new Database(join(process.cwd(), 'bifrost.db'));
  g.__bifrost_db.pragma('journal_mode = WAL');
  g.__bifrost_db.pragma('foreign_keys = ON');
}
const db = g.__bifrost_db;

// ── Users ────────────────────────────────────────────────────────

export function getUserByEmail(email: string): User | null {
  return db.prepare(
    'SELECT * FROM users WHERE email = ? AND active = 1'
  ).get(email) as User | null;
}

export function getUserById(id: number): User | null {
  return db.prepare(
    'SELECT * FROM users WHERE id = ? AND active = 1'
  ).get(id) as User | null;
}

export function getUserPublicById(id: number): UserPublic | null {
  return db.prepare(
    'SELECT id,email,name,organisation,role,bio,created_at,last_seen_at,active FROM users WHERE id = ? AND active = 1'
  ).get(id) as UserPublic | null;
}

export function createUser(data: {
  email: string;
  password_hash: string;
  name: string;
  organisation: string;
  role: Role;
}): number {
  const r = db.prepare(
    'INSERT INTO users (email,password_hash,name,organisation,role) VALUES (?,?,?,?,?)'
  ).run(data.email, data.password_hash, data.name, data.organisation, data.role);
  return Number(r.lastInsertRowid);
}

export function updateUserLastSeen(id: number): void {
  db.prepare("UPDATE users SET last_seen_at = datetime('now') WHERE id = ?").run(id);
}

export function updateUserProfile(id: number, name: string, bio: string): void {
  db.prepare('UPDATE users SET name = ?, bio = ? WHERE id = ?').run(name, bio, id);
}

export function updateUserRole(id: number, role: Role): void {
  db.prepare('UPDATE users SET role = ? WHERE id = ?').run(role, id);
}

export function deactivateUser(id: number): void {
  db.prepare('UPDATE users SET active = 0 WHERE id = ?').run(id);
}

export function getAllUsersPublic(): UserPublic[] {
  return db.prepare(
    'SELECT id,email,name,organisation,role,bio,created_at,last_seen_at,active FROM users ORDER BY organisation,name'
  ).all() as UserPublic[];
}

// ── Sessions ─────────────────────────────────────────────────────

export function createDbSession(id: string, userId: number, expiresAt: string): void {
  db.prepare(
    'INSERT INTO sessions (id,user_id,expires_at) VALUES (?,?,?)'
  ).run(id, userId, expiresAt);
}

export function getDbSession(id: string): { user_id: number; expires_at: string } | null {
  return db.prepare(
    "SELECT user_id,expires_at FROM sessions WHERE id = ? AND expires_at > datetime('now')"
  ).get(id) as { user_id: number; expires_at: string } | null;
}

export function deleteDbSession(id: string): void {
  db.prepare('DELETE FROM sessions WHERE id = ?').run(id);
}

// ── Invites ──────────────────────────────────────────────────────

export function createInvite(data: {
  token_hash: string;
  email: string;
  name: string;
  organisation: string;
  role: Role;
  expires_at: string;
  created_by_user_id: number;
}): number {
  const r = db.prepare(`
    INSERT INTO invites (token_hash,email,name,organisation,role,expires_at,created_by_user_id)
    VALUES (?,?,?,?,?,?,?)
  `).run(data.token_hash, data.email, data.name, data.organisation, data.role, data.expires_at, data.created_by_user_id);
  return Number(r.lastInsertRowid);
}

export function getInviteByTokenHash(tokenHash: string): Invite | null {
  return db.prepare(
    'SELECT * FROM invites WHERE token_hash = ?'
  ).get(tokenHash) as Invite | null;
}

export function redeemInvite(inviteId: number): void {
  db.prepare("UPDATE invites SET used_at = datetime('now') WHERE id = ?").run(inviteId);
}

export function createUserFromInvite(invite: Invite, passwordHash: string): UserPublic {
  const id = createUser({
    email: invite.email,
    password_hash: passwordHash,
    name: invite.name,
    organisation: invite.organisation,
    role: invite.role,
  });
  return getUserPublicById(id) as UserPublic;
}

export function markInviteUsed(id: number): void {
  db.prepare("UPDATE invites SET used_at = datetime('now') WHERE id = ?").run(id);
}

export function revokeInvite(id: number): void {
  db.prepare("UPDATE invites SET expires_at = datetime('now') WHERE id = ? AND used_at IS NULL").run(id);
}

export function getAllInvites(): (Invite & { creator_name: string | null })[] {
  return db.prepare(`
    SELECT i.*, u.name AS creator_name
    FROM invites i
    LEFT JOIN users u ON u.id = i.created_by_user_id
    ORDER BY i.created_at DESC
  `).all() as (Invite & { creator_name: string | null })[];
}

// ── Contributions ────────────────────────────────────────────────

export function createContribution(data: {
  user_id: number;
  type: ContributionType;
  body_md: string;
}): number {
  const r = db.prepare(
    'INSERT INTO contributions (user_id,type,body_md) VALUES (?,?,?)'
  ).run(data.user_id, data.type, data.body_md);
  return Number(r.lastInsertRowid);
}

export function getContributions(opts: {
  type?: ContributionType;
  sort?: 'newest' | 'top';
} = {}): ContributionRow[] {
  const typeClause = opts.type ? `AND c.type = '${opts.type}'` : '';
  const orderClause = opts.sort === 'top'
    ? 'ORDER BY reaction_count DESC, c.created_at DESC'
    : 'ORDER BY c.created_at DESC';
  return db.prepare(`
    SELECT
      c.*,
      u.name  AS author_name,
      u.organisation AS author_organisation,
      u.role  AS author_role,
      (SELECT COUNT(*) FROM reactions r WHERE r.contribution_id = c.id) AS reaction_count
    FROM contributions c
    JOIN users u ON u.id = c.user_id
    WHERE c.hidden_at IS NULL ${typeClause}
    ${orderClause}
  `).all() as ContributionRow[];
}

export function getContributionById(id: number): Contribution | null {
  return db.prepare('SELECT * FROM contributions WHERE id = ?').get(id) as Contribution | null;
}

export function updateContribution(id: number, body_md: string): void {
  db.prepare("UPDATE contributions SET body_md = ?, edited_at = datetime('now') WHERE id = ?")
    .run(body_md, id);
}

export function hideContribution(id: number): void {
  db.prepare("UPDATE contributions SET hidden_at = datetime('now') WHERE id = ?").run(id);
}

// ── Reactions ────────────────────────────────────────────────────

export function addReaction(userId: number, contributionId: number): void {
  db.prepare('INSERT OR IGNORE INTO reactions (user_id,contribution_id) VALUES (?,?)').run(userId, contributionId);
}

export function removeReaction(userId: number, contributionId: number): void {
  db.prepare('DELETE FROM reactions WHERE user_id = ? AND contribution_id = ?').run(userId, contributionId);
}

export function hasReaction(userId: number, contributionId: number): boolean {
  const r = db.prepare('SELECT id FROM reactions WHERE user_id = ? AND contribution_id = ?').get(userId, contributionId);
  return r !== undefined;
}

export function getReactedIds(userId: number): Set<number> {
  const rows = db.prepare('SELECT contribution_id FROM reactions WHERE user_id = ?').all(userId) as { contribution_id: number }[];
  return new Set(rows.map(r => r.contribution_id));
}

// ── Replies ──────────────────────────────────────────────────────

export function createReply(data: {
  contribution_id: number;
  user_id: number;
  body_md: string;
}): void {
  db.prepare('INSERT INTO replies (contribution_id,user_id,body_md) VALUES (?,?,?)')
    .run(data.contribution_id, data.user_id, data.body_md);
}

export function getReplies(contributionId: number): Reply[] {
  return db.prepare(`
    SELECT re.*,u.name AS author_name,u.role AS author_role
    FROM replies re
    JOIN users u ON u.id = re.user_id
    WHERE re.contribution_id = ?
    ORDER BY re.created_at ASC
  `).all(contributionId) as Reply[];
}

// ── Attendance ───────────────────────────────────────────────────

export function setAttendance(userId: number, meetingSlug: string, status: AttendanceStatus): void {
  db.prepare(`
    INSERT INTO attendance (user_id,meeting_slug,status,updated_at)
    VALUES (?,?,?,datetime('now'))
    ON CONFLICT(user_id,meeting_slug) DO UPDATE SET status=excluded.status, updated_at=excluded.updated_at
  `).run(userId, meetingSlug, status);
}

export function getAttendanceSummary(meetingSlug: string): AttendanceSummary {
  const rows = db.prepare(
    "SELECT status, COUNT(*) AS n FROM attendance WHERE meeting_slug = ? GROUP BY status"
  ).all(meetingSlug) as { status: string; n: number }[];
  const yes = rows.find(r => r.status === 'yes')?.n ?? 0;
  const no  = rows.find(r => r.status === 'no')?.n ?? 0;
  return { yes, no };
}

export function getUserAttendance(userId: number, meetingSlug: string): AttendanceStatus | null {
  const r = db.prepare(
    'SELECT status FROM attendance WHERE user_id = ? AND meeting_slug = ?'
  ).get(userId, meetingSlug) as { status: AttendanceStatus } | undefined;
  return r?.status ?? null;
}

export function getAllAttendance(meetingSlug: string): { user_id: number; status: AttendanceStatus; name: string }[] {
  return db.prepare(`
    SELECT a.user_id, a.status, u.name
    FROM attendance a JOIN users u ON u.id = a.user_id
    WHERE a.meeting_slug = ?
  `).all(meetingSlug) as { user_id: number; status: AttendanceStatus; name: string }[];
}
