'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 = $1`,
    [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 result = await pool.query(
    `UPDATE self_hosted_licenses
        SET last_validated_at = NOW(),
            instance_id = COALESCE(instance_id, $2)
      WHERE id = $1
      RETURNING *`,
    [id, instanceId || null]
  );
  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 result = await pool.query(
    `INSERT INTO self_hosted_licenses
       (license_key, user_id, plan_tier, max_connections, max_health_checks_per_month, expires_at)
     VALUES ($1, $2, $3, $4, $5, $6)
     RETURNING *`,
    [licenseKey, userId || null, planTier, maxConnections, maxHealthChecks, expiresAt || null]
  );
  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 = $1 AND plan_tier = $2 AND status = 'active'
      ORDER BY created_at DESC
      LIMIT 1`,
    [userId, 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 = $1 AND status = 'active'
      ORDER BY created_at DESC`,
    [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 = $1 RETURNING *`,
    [id]
  );
  return result.rows[0] || null;
}

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