'use strict';

/**
 * db/licenses.js — CRUD helpers for self_hosted_licenses.
 * Owns: query logic for the self-hosted license table.
 */

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

// Find a license record by its key (exact match). Returns row or null.
async function findByKey(key) {
  const result = await pool.query(
    `SELECT * FROM self_hosted_licenses WHERE license_key = :p1`,
    { p1: key }
  );
  return result.rows[0] || null;
}

// Bump last_validated_at; set instance_id on first validation only (locked thereafter).
async function updateValidation(id, instanceId) {
  const upd = await pool.query(
    `UPDATE self_hosted_licenses
        SET last_validated_at = CURRENT_TIMESTAMP,
            instance_id = COALESCE(instance_id, :p2)
      WHERE id = :p1`,
    { p1: id, p2: instanceId || null }
  );
  if (!upd.rowCount) return null;
  const result = await pool.query(
    `SELECT * FROM self_hosted_licenses WHERE id = :p1`,
    { p1: id }
  );
  return result.rows[0] || null;
}

// Insert a new license. maxConnections / maxHealthChecks may be -1 (unlimited),
// so callers must pass explicit values rather than relying on `||` coalescing.
async function createLicense({ userId, planTier, maxConnections, maxHealthChecks, expiresAt, licenseKey }) {
  const ins = await pool.query(
    `INSERT INTO self_hosted_licenses
       (license_key, user_id, plan_tier, max_connections, max_health_checks_per_month, expires_at)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6)
     RETURNING id INTO :out_id`,
    { p1: licenseKey, p2: userId || null, p3: planTier, p4: maxConnections,
      p5: maxHealthChecks, p6: expiresAt || null, out_id: pool.outNumber() }
  );
  const result = await pool.query(
    `SELECT * FROM self_hosted_licenses WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return result.rows[0];
}

// Find the newest active license for a user on a given tier. Used before
// auto-minting on payment so webhook retries / double-payments don't mint
// duplicate keys. Returns row or null.
async function findActiveByUserId(userId, planTier) {
  const result = await pool.query(
    `SELECT * FROM self_hosted_licenses
      WHERE user_id = :p1 AND plan_tier = :p2 AND status = 'active'
      ORDER BY created_at DESC
      FETCH FIRST 1 ROWS ONLY`,
    { p1: userId, p2: planTier }
  );
  return result.rows[0] || null;
}

// All active licenses for a user, any tier, newest first. Used by POST
// /api/license/free, which must distinguish "already has a free key" (return it,
// idempotent) from "already has a PAID key" (refuse — they don't need a trial).
// findActiveByUserId() can't answer that: it requires a specific plan_tier.
async function listActiveByUserId(userId) {
  const result = await pool.query(
    `SELECT * FROM self_hosted_licenses
      WHERE user_id = :p1 AND status = 'active'
      ORDER BY created_at DESC`,
    { p1: userId }
  );
  return result.rows;
}

// List all licenses with the owning user's email, newest first (admin view).
async function listLicenses() {
  const result = await pool.query(
    `SELECT l.id, l.license_key, l.user_id, u.email AS user_email,
            l.plan_tier, l.max_connections, l.max_health_checks_per_month,
            l.status, l.issued_at, l.expires_at, l.last_validated_at,
            l.instance_id, l.created_at
       FROM self_hosted_licenses l
       LEFT JOIN users u ON u.id = l.user_id
      ORDER BY l.created_at DESC`
  );
  return result.rows;
}

// Mark a license expired.
async function expireLicense(id) {
  const result = await pool.query(
    `UPDATE self_hosted_licenses SET status = 'expired' WHERE id = :p1`,
    { p1: id }
  );
  if (!result.rowCount) return null;
  const read = await pool.query(
    `SELECT * FROM self_hosted_licenses WHERE id = :p1`,
    { p1: id }
  );
  return read.rows[0] || null;
}

module.exports = { findByKey, findActiveByUserId, listActiveByUserId, updateValidation, createLicense, listLicenses, expireLicense };
