import pkgPg from 'pg';
import { PGDATABASE, PGHOST, PGPASSWORD, PGPORT, PGUSER, SESSION_TIMEOUT, VLAN_ID, MAX_DOWN, MAX_UP } from '../config/env.js';

const { Pool } = pkgPg;
export const pool = new Pool({
  host: PGHOST,
  port: PGPORT,
  database: PGDATABASE,
  user: PGUSER,
  password: PGPASSWORD,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
  keepAlive: true,
  keepAliveInitialDelayMillis: 10000,
});

// Manejo de errores del pool para evitar que el proceso se caiga
pool.on('error', (err, client) => {
  console.error('Error inesperado en el cliente de PostgreSQL:', err);
});

export async function ensureSchema() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // Minimal FreeRADIUS tables used by the app
    await client.query(`
      CREATE TABLE IF NOT EXISTS radcheck (
        id SERIAL PRIMARY KEY,
        username VARCHAR(64) NOT NULL DEFAULT '',
        attribute VARCHAR(64) NOT NULL DEFAULT '',
        op CHAR(2) NOT NULL DEFAULT ':=',
        value VARCHAR(253) NOT NULL DEFAULT ''
      );
    `);
    await client.query(`
      CREATE TABLE IF NOT EXISTS radreply (
        id SERIAL PRIMARY KEY,
        username VARCHAR(64) NOT NULL DEFAULT '',
        attribute VARCHAR(64) NOT NULL DEFAULT '',
        op CHAR(2) NOT NULL DEFAULT ':=',
        value VARCHAR(253) NOT NULL DEFAULT ''
      );
    `);
    await client.query(`CREATE INDEX IF NOT EXISTS radcheck_user ON radcheck (username);`);
    await client.query(`CREATE INDEX IF NOT EXISTS radreply_user ON radreply (username);`);
    await client.query(`
      CREATE TABLE IF NOT EXISTS vlans (
        id INTEGER PRIMARY KEY,
        nombre TEXT NOT NULL,
        descripcion TEXT
      );
    `);
    await client.query(`
      CREATE TABLE IF NOT EXISTS dispositivos (
        id SERIAL PRIMARY KEY,
        mac VARCHAR(32) UNIQUE NOT NULL,
        nombre TEXT,
        descripcion TEXT,
        vendor TEXT,
        first_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        last_seen TIMESTAMPTZ,
        notas TEXT
      );
    `);
    await client.query(`CREATE INDEX IF NOT EXISTS dispositivos_mac_idx ON dispositivos (mac);`);
    await client.query(`
      CREATE TABLE IF NOT EXISTS users (
        username VARCHAR(64) PRIMARY KEY,
        etiquetas TEXT[] NOT NULL DEFAULT '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        habilitado_since TIMESTAMPTZ,
        dispositivos_utilizados INTEGER[] NOT NULL DEFAULT '{}',
        dispositivos_conectados INTEGER[] NOT NULL DEFAULT '{}'
      );
    `);
    await client.query(`
      CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
      END; $$ LANGUAGE plpgsql;
    `);
    await client.query(`
      DO $$
      BEGIN
        IF NOT EXISTS (
          SELECT 1 FROM pg_trigger WHERE tgname = 'trg_users_updated'
        ) THEN
          CREATE TRIGGER trg_users_updated
          BEFORE UPDATE ON users
          FOR EACH ROW EXECUTE FUNCTION set_updated_at();
        END IF;
      END$$;
    `);
    // Sessions table for persistent connection tracking
    await client.query(`
      CREATE TABLE IF NOT EXISTS sesiones (
        id SERIAL PRIMARY KEY,
        session_id VARCHAR(64) UNIQUE NOT NULL,
        username VARCHAR(64) NOT NULL,
        dispositivo_id INTEGER REFERENCES dispositivos(id) ON DELETE SET NULL,
        nas_ip VARCHAR(45),
        nas_id VARCHAR(64),
        calling_station_id VARCHAR(32),
        called_station_id VARCHAR(64),
        started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        ended_at TIMESTAMPTZ,
        last_update TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        status VARCHAR(16) NOT NULL DEFAULT 'active',
        stop_reason VARCHAR(64),
        bytes_in BIGINT DEFAULT 0,
        bytes_out BIGINT DEFAULT 0,
        packets_in BIGINT DEFAULT 0,
        packets_out BIGINT DEFAULT 0,
        session_time INTEGER DEFAULT 0,
        interim_interval INTEGER
      );
    `);
    await client.query(`CREATE INDEX IF NOT EXISTS idx_sesiones_username ON sesiones(username);`);
    await client.query(`CREATE INDEX IF NOT EXISTS idx_sesiones_dispositivo ON sesiones(dispositivo_id);`);
    await client.query(`CREATE INDEX IF NOT EXISTS idx_sesiones_status ON sesiones(status);`);
    await client.query(`CREATE INDEX IF NOT EXISTS idx_sesiones_started ON sesiones(started_at DESC);`);
    await client.query(`CREATE INDEX IF NOT EXISTS idx_sesiones_last_update ON sesiones(last_update);`);
    await client.query(`CREATE INDEX IF NOT EXISTS idx_sesiones_calling_station ON sesiones(calling_station_id);`);
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('ensureSchema error:', e?.message || e);
    throw e;
  } finally {
    client.release();
  }
}

export async function getOrCreateDevice({ mac, nombre = null, vendor = null, descripcion = null }) {
  if (!mac) throw new Error('mac required');
  const client = await pool.connect();
  try {
    const up = await client.query(
      `INSERT INTO dispositivos (mac, nombre, vendor, descripcion)
       VALUES ($1, $2, $3, $4)
       ON CONFLICT (mac) DO UPDATE SET
         last_seen = NOW(),
         nombre = COALESCE(dispositivos.nombre, EXCLUDED.nombre),
         vendor = COALESCE(dispositivos.vendor, EXCLUDED.vendor),
         descripcion = COALESCE(dispositivos.descripcion, EXCLUDED.descripcion)
       RETURNING id`,
      [String(mac), nombre, vendor, descripcion]
    );
    return up.rows[0].id;
  } finally {
    client.release();
  }
}

export async function ensureUserRow(username) {
  await pool.query('INSERT INTO users (username) VALUES ($1) ON CONFLICT (username) DO NOTHING', [username]);
}

export async function addDeviceToUser(username, deviceId) {
  await ensureUserRow(username);
  await pool.query(
    `UPDATE users SET dispositivos_utilizados = (
       SELECT ARRAY(
         SELECT DISTINCT x FROM (
           SELECT unnest(coalesce(dispositivos_utilizados, '{}'::int[])) AS x
           UNION ALL SELECT $2::int
         ) AS t
       )
     ) WHERE username = $1`,
    [username, deviceId]
  );
}

export async function connectDeviceForUser(username, deviceId) {
  await ensureUserRow(username);
  await pool.query(
    `UPDATE users SET dispositivos_conectados = (
       SELECT ARRAY(
         SELECT DISTINCT x FROM (
           SELECT unnest(coalesce(dispositivos_conectados, '{}'::int[])) AS x
           UNION ALL SELECT $2::int
         ) AS t
       )
     ) WHERE username = $1`,
    [username, deviceId]
  );
}

export async function disconnectDeviceForUser(username, deviceId) {
  await ensureUserRow(username);
  await pool.query(
    `UPDATE users SET dispositivos_conectados = array_remove(coalesce(dispositivos_conectados, '{}'::int[]), $2::int)
     WHERE username = $1`,
    [username, deviceId]
  );
}

export async function readUsersFromDb() {
  const client = await pool.connect();
  try {
    const q = `
      SELECT rc.username,
             rc.value AS password,
             EXISTS (
                SELECT 1 FROM radcheck r2
                WHERE r2.username = rc.username AND r2.attribute = 'Auth-Type' AND r2.value = 'Reject'
             ) AS disabled,
             COALESCE((
                SELECT rr.value FROM radreply rr
                WHERE rr.username = rc.username AND rr.attribute = 'Tunnel-Private-Group-Id'
                ORDER BY rr.id DESC LIMIT 1
             ), $1) AS vlan,
             u.etiquetas,
             u.created_at,
             u.updated_at,
             u.habilitado_since,
             u.dispositivos_utilizados,
             u.dispositivos_conectados
      FROM radcheck rc
      LEFT JOIN users u ON u.username = rc.username
      WHERE rc.attribute = 'Cleartext-Password'
      ORDER BY rc.username ASC`;
    const { rows } = await client.query(q, [String(VLAN_ID)]);
    return rows.map(r => ({
      username: r.username,
      password: r.password,
      vlan: String(r.vlan),
      disabled: !!r.disabled,
      etiquetas: r.etiquetas || [],
      created_at: r.created_at || null,
      updated_at: r.updated_at || null,
      habilitado_since: r.habilitado_since || null,
      dispositivos_utilizados: r.dispositivos_utilizados || [],
      dispositivos_conectados: r.dispositivos_conectados || [],
    }));
  } finally {
    client.release();
  }
}

export async function upsertUserToDb(user) {
  const { username, password, vlan, disabled, etiquetas } = user;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // Ensure metadata row exists
    await client.query(
      'INSERT INTO users (username) VALUES ($1) ON CONFLICT (username) DO NOTHING',
      [username]
    );
    if (Array.isArray(etiquetas)) {
      await client.query('UPDATE users SET etiquetas = $2, updated_at = NOW() WHERE username = $1', [username, etiquetas.map(String).slice(0, 100)]);
    }
    await client.query("DELETE FROM radcheck WHERE username = $1 AND attribute = 'Cleartext-Password'", [username]);
    await client.query(
      "INSERT INTO radcheck (username, attribute, op, value) VALUES ($1,'Cleartext-Password',':=',$2)",
      [username, password]
    );
    await client.query("DELETE FROM radcheck WHERE username = $1 AND attribute = 'Auth-Type'", [username]);
    if (disabled) {
      await client.query(
        "INSERT INTO radcheck (username, attribute, op, value) VALUES ($1,'Auth-Type',':=','Reject')",
        [username]
      );
    }
    const attrs = [
      ['Tunnel-Type', 'VLAN'],
      ['Tunnel-Medium-Type', 'IEEE-802'],
      ['Tunnel-Private-Group-Id', String(vlan || VLAN_ID)],
      ['WISPr-Bandwidth-Max-Down', String(MAX_DOWN)],
      ['WISPr-Bandwidth-Max-Up', String(MAX_UP)],
    ];
    if (SESSION_TIMEOUT > 0) {
      attrs.push(['Session-Timeout', String(SESSION_TIMEOUT)]);
    }
    await client.query(
      "DELETE FROM radreply WHERE username = $1 AND attribute IN ('Tunnel-Type','Tunnel-Medium-Type','Tunnel-Private-Group-Id','WISPr-Bandwidth-Max-Down','WISPr-Bandwidth-Max-Up','Session-Timeout')",
      [username]
    );
    for (const [attr, val] of attrs) {
      await client.query(
        "INSERT INTO radreply (username, attribute, op, value) VALUES ($1,$2,':=',$3)",
        [username, attr, String(val)]
      );
    }
    // Update metadata timestamps; set habilitado_since when enabled
    await client.query(
      `UPDATE users
         SET updated_at = NOW(),
             habilitado_since = CASE WHEN $2::boolean = FALSE THEN NOW() ELSE habilitado_since END
       WHERE username = $1`,
      [username, !!disabled]
    );
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

export async function disableGuestsFromYesterday() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(`
      WITH tz AS (
        SELECT (NOW() AT TIME ZONE 'America/Tegucigalpa')::date AS today
      )
      SELECT u.username
      FROM users u, tz
      WHERE 'invitado' = ANY(u.etiquetas)
        AND u.habilitado_since IS NOT NULL
        AND (u.habilitado_since AT TIME ZONE 'America/Tegucigalpa')::date = (tz.today - INTERVAL '1 day')::date
    `);
    const usernames = rows.map(r => r.username);
    if (usernames.length === 0) { await client.query('COMMIT'); return { count: 0 }; }
    await client.query("DELETE FROM radcheck WHERE attribute = 'Auth-Type' AND username = ANY($1)", [usernames]);
    const values = usernames.map(u => `('${u}','Auth-Type',':=','Reject')`).join(',');
    await client.query(`INSERT INTO radcheck (username, attribute, op, value) VALUES ${values}`);
    await client.query('UPDATE users SET updated_at = NOW() WHERE username = ANY($1)', [usernames]);
    await client.query('COMMIT');
    return { count: usernames.length };
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('disableGuestsFromYesterday error:', e?.message || e);
    throw e;
  } finally {
    client.release();
  }
}

export async function deleteUserFromDb(username) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('DELETE FROM radcheck WHERE username = $1', [username]);
    await client.query('DELETE FROM radreply WHERE username = $1', [username]);
    await client.query('DELETE FROM users WHERE username = $1', [username]);
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

// ==================== SESSION FUNCTIONS ====================

// Create or update session on START/INTERIM-UPDATE
export async function upsertSession({
  sessionId,
  username,
  nasIp,
  nasId,
  callingStationId,
  calledStationId,
  bytesIn,
  bytesOut,
  packetsIn,
  packetsOut,
  sessionTime,
  interimInterval
}) {
  const client = await pool.connect();
  try {
    // Get dispositivo_id if device exists
    let dispositivoId = null;
    if (callingStationId) {
      const devRes = await client.query(
        'SELECT id FROM dispositivos WHERE mac = $1',
        [callingStationId]
      );
      if (devRes.rows.length > 0) {
        dispositivoId = devRes.rows[0].id;
      }
    }

    await client.query(`
      INSERT INTO sesiones (
        session_id, username, dispositivo_id, nas_ip, nas_id,
        calling_station_id, called_station_id, status, last_update,
        bytes_in, bytes_out, packets_in, packets_out, session_time,
        interim_interval
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, 'active', NOW(), $8, $9, $10, $11, $12, $13)
      ON CONFLICT (session_id) DO UPDATE SET
        last_update = NOW(),
        bytes_in = COALESCE($8, sesiones.bytes_in),
        bytes_out = COALESCE($9, sesiones.bytes_out),
        packets_in = COALESCE($10, sesiones.packets_in),
        packets_out = COALESCE($11, sesiones.packets_out),
        session_time = COALESCE($12, sesiones.session_time),
        interim_interval = COALESCE($13, sesiones.interim_interval)
    `, [
      sessionId, username, dispositivoId, nasIp, nasId,
      callingStationId, calledStationId,
      bytesIn || 0, bytesOut || 0, packetsIn || 0, packetsOut || 0,
      sessionTime || 0, interimInterval
    ]);
  } finally {
    client.release();
  }
}

// End session on STOP
export async function endSession(sessionId, stopReason, stats = {}) {
  await pool.query(`
    UPDATE sesiones SET
      status = 'stopped',
      ended_at = NOW(),
      last_update = NOW(),
      stop_reason = $2,
      bytes_in = COALESCE($3, bytes_in),
      bytes_out = COALESCE($4, bytes_out),
      session_time = COALESCE($5, session_time)
    WHERE session_id = $1 AND status = 'active'
  `, [sessionId, stopReason, stats.bytesIn, stats.bytesOut, stats.sessionTime]);
}

// Get all active sessions
export async function getActiveSessions() {
  const { rows } = await pool.query(`
    SELECT s.*, d.mac, d.nombre as device_name, d.vendor as device_vendor
    FROM sesiones s
    LEFT JOIN dispositivos d ON d.id = s.dispositivo_id
    WHERE s.status = 'active'
    ORDER BY s.started_at DESC
  `);
  return rows;
}

// Get session history with filters and pagination
export async function getSessionHistory({
  username,
  dispositivoId,
  mac,
  status,
  limit = 50,
  offset = 0,
  desde,
  hasta
} = {}) {
  let query = `
    SELECT s.*, d.mac, d.nombre as device_name, d.vendor as device_vendor
    FROM sesiones s
    LEFT JOIN dispositivos d ON d.id = s.dispositivo_id
    WHERE 1=1
  `;
  const params = [];
  let paramIdx = 1;

  if (username) {
    query += ` AND s.username = $${paramIdx++}`;
    params.push(username);
  }
  if (dispositivoId) {
    query += ` AND s.dispositivo_id = $${paramIdx++}`;
    params.push(dispositivoId);
  }
  if (mac) {
    query += ` AND s.calling_station_id = $${paramIdx++}`;
    params.push(mac);
  }
  if (status) {
    query += ` AND s.status = $${paramIdx++}`;
    params.push(status);
  }
  if (desde) {
    query += ` AND s.started_at >= $${paramIdx++}`;
    params.push(desde);
  }
  if (hasta) {
    query += ` AND s.started_at <= $${paramIdx++}`;
    params.push(hasta);
  }

  query += ` ORDER BY s.started_at DESC LIMIT $${paramIdx++} OFFSET $${paramIdx}`;
  params.push(limit, offset);

  const { rows } = await pool.query(query, params);
  return rows;
}

// Mark stale sessions (called by cleanup job)
export async function markStaleSessions(maxIdleMinutes = 10) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Get sessions that will be marked as stale (for disconnect logic)
    const { rows: staleSessions } = await client.query(`
      SELECT session_id, username, dispositivo_id
      FROM sesiones
      WHERE status = 'active'
        AND last_update < NOW() - INTERVAL '1 minute' * $1
    `, [maxIdleMinutes]);

    // Mark them as stale
    const { rowCount } = await client.query(`
      UPDATE sesiones SET
        status = 'stale',
        ended_at = last_update,
        stop_reason = 'Stale-Detected'
      WHERE status = 'active'
        AND last_update < NOW() - INTERVAL '1 minute' * $1
    `, [maxIdleMinutes]);

    // Disconnect devices for users with stale sessions
    for (const sess of staleSessions) {
      if (sess.dispositivo_id) {
        await client.query(
          `UPDATE users SET dispositivos_conectados = array_remove(coalesce(dispositivos_conectados, '{}'::int[]), $2::int)
           WHERE username = $1`,
          [sess.username, sess.dispositivo_id]
        );
      }
    }

    await client.query('COMMIT');
    return { count: rowCount, sessions: staleSessions };
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

// Rebuild dispositivos_conectados from active sessions
export async function syncConnectedDevicesFromSessions() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Clear all dispositivos_conectados
    await client.query(`UPDATE users SET dispositivos_conectados = '{}'`);

    // Get active sessions grouped by username
    const { rows } = await client.query(`
      SELECT username, array_agg(DISTINCT dispositivo_id) as device_ids
      FROM sesiones
      WHERE status = 'active' AND dispositivo_id IS NOT NULL
      GROUP BY username
    `);

    // Update each user with their connected devices
    for (const row of rows) {
      const deviceIds = row.device_ids.filter(id => id != null);
      if (deviceIds.length > 0) {
        await client.query(
          `UPDATE users SET dispositivos_conectados = $2 WHERE username = $1`,
          [row.username, deviceIds]
        );
      }
    }

    await client.query('COMMIT');
    return rows.length;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

// Clean old sessions (data retention)
export async function cleanOldSessions(retentionDays = 90) {
  const { rowCount } = await pool.query(`
    DELETE FROM sesiones
    WHERE status != 'active'
      AND started_at < NOW() - INTERVAL '1 day' * $1
  `, [retentionDays]);
  return rowCount;
}

// Get session counts by status
export async function getSessionStats() {
  const { rows } = await pool.query(`
    SELECT
      COUNT(*) FILTER (WHERE status = 'active') as active,
      COUNT(*) FILTER (WHERE status = 'stopped') as stopped,
      COUNT(*) FILTER (WHERE status = 'stale') as stale,
      COUNT(*) as total
    FROM sesiones
  `);
  return rows[0];
}
