const db = require('./db');

// ---- shared SELECT that hydrates a model with tags, projects, files, designer ----
const MODEL_SELECT = `
  SELECT m.id, m.name, m.description, m.source_site, m.source_url, m.source_id,
         m.license, m.commercial_use, m.thumbnail_path, m.print_settings, m.date_added, m.date_modified,
         d.id AS designer_id, d.name AS designer_name, d.profile_url AS designer_url,
         COALESCE(t.tags, '[]'::json)     AS tags,
         COALESCE(p.projects, '[]'::json) AS projects,
         COALESCE(f.files, '[]'::json)    AS files
  FROM models m
  LEFT JOIN designers d ON d.id = m.designer_id
  LEFT JOIN LATERAL (
    SELECT json_agg(tg.name ORDER BY tg.name) AS tags
    FROM model_tags mt JOIN tags tg ON tg.id = mt.tag_id
    WHERE mt.model_id = m.id
  ) t ON true
  LEFT JOIN LATERAL (
    SELECT json_agg(json_build_object('id', pr.id, 'name', pr.name) ORDER BY pr.name) AS projects
    FROM model_projects mp JOIN projects pr ON pr.id = mp.project_id
    WHERE mp.model_id = m.id
  ) p ON true
  LEFT JOIN LATERAL (
    SELECT json_agg(json_build_object(
      'id', fl.id, 'originalName', fl.original_name, 'fileName', fl.file_name,
      'format', fl.format, 'size', fl.size, 'volume', fl.volume,
      'bbox', fl.bbox, 'facets', fl.facets) ORDER BY fl.id) AS files
    FROM files fl WHERE fl.model_id = m.id
  ) f ON true
`;

function shapeModel(row) {
  if (!row) return null;
  const { designer_id, designer_name, designer_url, ...rest } = row;
  return {
    ...rest,
    designer: designer_id ? { id: designer_id, name: designer_name, url: designer_url } : null,
  };
}

// ---------------------------------------------------------------- models
async function getAllModels() {
  const { rows } = await db.query(`${MODEL_SELECT} ORDER BY m.date_added DESC`);
  return rows.map(shapeModel);
}

async function getModel(id) {
  const { rows } = await db.query(`${MODEL_SELECT} WHERE m.id = $1`, [id]);
  return shapeModel(rows[0]);
}

async function findModelBySource(site, sourceId) {
  const { rows } = await db.query(
    `${MODEL_SELECT} WHERE m.source_site = $1 AND m.source_id = $2`, [site, sourceId]);
  return shapeModel(rows[0]);
}

// data = { name, description, source_site, source_url, source_id, license,
//          designer:{name,source_site,profile_url}, thumbnail_path, print_settings,
//          tags:[string], projects:[id], files:[{...}] }
async function createModel(data) {
  return db.transaction(async (client) => {
    let designerId = null;
    if (data.designer && data.designer.name) {
      designerId = await upsertDesigner(client, data.designer);
    }
    const { rows } = await client.query(
      `INSERT INTO models (name, description, source_site, source_url, source_id,
                           license, commercial_use, designer_id, thumbnail_path, print_settings)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING id`,
      [data.name, data.description || '', data.source_site || null, data.source_url || null,
       data.source_id || null, data.license || null, data.commercial_use ?? null, designerId,
       data.thumbnail_path || null, data.print_settings || {}]
    );
    const modelId = rows[0].id;
    await setTags(client, modelId, data.tags || []);
    await setProjects(client, modelId, data.projects || []);
    for (const file of data.files || []) {
      await insertFile(client, modelId, file);
    }
    return modelId;
  }).then(getModel);
}

async function updateModel(id, updates) {
  await db.transaction(async (client) => {
    const fields = [];
    const values = [];
    let i = 1;
    for (const col of ['name', 'description', 'license', 'commercial_use', 'thumbnail_path']) {
      if (updates[col] !== undefined) { fields.push(`${col} = $${i++}`); values.push(updates[col]); }
    }
    if (updates.print_settings !== undefined) {
      fields.push(`print_settings = $${i++}`); values.push(updates.print_settings);
    }
    if (fields.length) {
      fields.push(`date_modified = now()`);
      values.push(id);
      await client.query(`UPDATE models SET ${fields.join(', ')} WHERE id = $${i}`, values);
    }
    if (updates.tags !== undefined) await setTags(client, id, updates.tags);
    if (updates.projects !== undefined) await setProjects(client, id, updates.projects);
  });
  return getModel(id);
}

// Returns the on-disk file rows so the caller can unlink them.
async function deleteModel(id) {
  const { rows } = await db.query('SELECT file_path, glb_path FROM files WHERE model_id = $1', [id]);
  const model = await db.query('SELECT thumbnail_path FROM models WHERE id = $1', [id]);
  const result = await db.query('DELETE FROM models WHERE id = $1 RETURNING id', [id]);
  if (result.rowCount === 0) return null;
  return {
    filePaths: rows.map((r) => r.file_path),
    glbPaths: rows.map((r) => r.glb_path).filter(Boolean),
    thumbnailPath: model.rows[0] && model.rows[0].thumbnail_path,
  };
}

async function addFiles(modelId, files) {
  await db.transaction(async (client) => {
    for (const file of files) await insertFile(client, modelId, file);
  });
  return getModel(modelId);
}

// Record a 3MF's GLB conversion + computed geometry on the file row.
async function updateFileConversion(id, { glb_path, volume, bbox }) {
  await db.query(
    `UPDATE files SET glb_path = $2,
            volume = COALESCE($3, volume),
            bbox = COALESCE($4, bbox)
     WHERE id = $1`,
    [id, glb_path, volume ?? null, bbox ?? null]);
}

async function insertFile(client, modelId, file) {
  await client.query(
    `INSERT INTO files (model_id, file_name, original_name, file_path, format, size, volume, bbox, facets)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)`,
    [modelId, file.fileName, file.originalName, file.filePath, file.format,
     file.size || 0, file.volume ?? null, file.bbox ?? null, file.facets ?? null]
  );
}

// ---------------------------------------------------------------- tags
async function setTags(client, modelId, tagNames) {
  await client.query('DELETE FROM model_tags WHERE model_id = $1', [modelId]);
  for (const raw of tagNames) {
    const name = String(raw).trim();
    if (!name) continue;
    const { rows } = await client.query(
      `INSERT INTO tags (name) VALUES ($1)
       ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name RETURNING id`, [name]);
    await client.query(
      'INSERT INTO model_tags (model_id, tag_id) VALUES ($1,$2) ON CONFLICT DO NOTHING',
      [modelId, rows[0].id]);
  }
}

async function getAllTags() {
  const { rows } = await db.query('SELECT name FROM tags ORDER BY name');
  return rows.map((r) => r.name);
}

// ---------------------------------------------------------------- projects
async function setProjects(client, modelId, projectIds) {
  await client.query('DELETE FROM model_projects WHERE model_id = $1', [modelId]);
  for (const pid of projectIds) {
    await client.query(
      'INSERT INTO model_projects (model_id, project_id) VALUES ($1,$2) ON CONFLICT DO NOTHING',
      [modelId, pid]);
  }
}

async function getAllProjects() {
  const { rows } = await db.query(`
    SELECT p.id, p.name, p.description, p.date_created, p.date_modified,
           COUNT(mp.model_id)::int AS model_count
    FROM projects p
    LEFT JOIN model_projects mp ON mp.project_id = p.id
    GROUP BY p.id ORDER BY p.name`);
  return rows;
}

async function getProject(id) {
  const { rows } = await db.query('SELECT * FROM projects WHERE id = $1', [id]);
  return rows[0] || null;
}

async function createProject({ name, description }) {
  const { rows } = await db.query(
    'INSERT INTO projects (name, description) VALUES ($1,$2) RETURNING *',
    [name, description || '']);
  return rows[0];
}

async function updateProject(id, { name, description }) {
  const { rows } = await db.query(
    `UPDATE projects SET name = COALESCE($2, name),
            description = COALESCE($3, description), date_modified = now()
     WHERE id = $1 RETURNING *`, [id, name ?? null, description ?? null]);
  return rows[0] || null;
}

async function deleteProject(id) {
  const { rowCount } = await db.query('DELETE FROM projects WHERE id = $1', [id]);
  return rowCount > 0;
}

// ---------------------------------------------------------------- license rules
async function getLicenseRule(name) {
  const { rows } = await db.query('SELECT name, commercial FROM license_rules WHERE name = $1', [name]);
  return rows[0] || null;
}

async function upsertLicenseRule(name, commercial) {
  await db.query(
    `INSERT INTO license_rules (name, commercial) VALUES ($1, $2)
     ON CONFLICT (name) DO UPDATE SET commercial = EXCLUDED.commercial`,
    [name, commercial ?? null]);
}

async function listLicenseRules() {
  const { rows } = await db.query(`
    SELECT lr.name, lr.commercial, COUNT(m.id)::int AS model_count
    FROM license_rules lr
    LEFT JOIN models m ON m.license = lr.name
    GROUP BY lr.name, lr.commercial
    ORDER BY lr.name`);
  return rows;
}

async function distinctModelLicenses() {
  const { rows } = await db.query("SELECT DISTINCT license FROM models WHERE license IS NOT NULL AND license <> ''");
  return rows.map((r) => r.license);
}

// Push a rule's value onto every model that carries that license.
async function applyLicenseRuleToModels(name, commercial) {
  await db.query('UPDATE models SET commercial_use = $2 WHERE license = $1', [name, commercial ?? null]);
}

// Align every model's commercial_use to its license rule (license = source of truth).
async function reconcileModelsToRules() {
  await db.query(`
    UPDATE models m SET commercial_use = lr.commercial
    FROM license_rules lr
    WHERE m.license = lr.name AND m.commercial_use IS DISTINCT FROM lr.commercial`);
}

// ---------------------------------------------------------------- users / auth
async function countUsers() {
  const { rows } = await db.query('SELECT COUNT(*)::int AS n FROM users');
  return rows[0].n;
}

async function getUserByEmail(email) {
  const { rows } = await db.query('SELECT * FROM users WHERE lower(email) = lower($1)', [email]);
  return rows[0] || null;
}

async function getUserById(id) {
  const { rows } = await db.query('SELECT id, email, created_at FROM users WHERE id = $1', [id]);
  return rows[0] || null;
}

async function createUser(email, passwordHash) {
  const { rows } = await db.query(
    'INSERT INTO users (email, password_hash) VALUES ($1, $2) RETURNING id, email, created_at',
    [email.trim(), passwordHash]);
  return rows[0];
}

async function updateUserPassword(id, passwordHash) {
  await db.query('UPDATE users SET password_hash = $2 WHERE id = $1', [id, passwordHash]);
}

async function createPasswordReset(userId, tokenHash, expiresAt) {
  await db.query(
    'INSERT INTO password_resets (user_id, token_hash, expires_at) VALUES ($1,$2,$3)',
    [userId, tokenHash, expiresAt]);
}

async function getValidReset(tokenHash) {
  const { rows } = await db.query(
    `SELECT * FROM password_resets
     WHERE token_hash = $1 AND used = false AND expires_at > now()`, [tokenHash]);
  return rows[0] || null;
}

async function markResetUsed(id) {
  await db.query('UPDATE password_resets SET used = true WHERE id = $1', [id]);
}

// ---------------------------------------------------------------- settings
async function getSetting(key) {
  const { rows } = await db.query('SELECT value FROM settings WHERE key = $1', [key]);
  return rows.length ? rows[0].value : null;
}

// value of '' or null clears the setting.
async function setSetting(key, value) {
  if (value === null || value === '') {
    await db.query('DELETE FROM settings WHERE key = $1', [key]);
    return;
  }
  await db.query(
    `INSERT INTO settings (key, value, updated_at) VALUES ($1, $2, now())
     ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, updated_at = now()`,
    [key, value]);
}

// ---------------------------------------------------------------- designers
async function upsertDesigner(client, { name, source_site, profile_url }) {
  const { rows } = await client.query(
    `INSERT INTO designers (name, source_site, profile_url) VALUES ($1,$2,$3)
     ON CONFLICT (name, source_site) DO UPDATE SET profile_url = EXCLUDED.profile_url
     RETURNING id`, [name, source_site || null, profile_url || null]);
  return rows[0].id;
}

module.exports = {
  getAllModels, getModel, findModelBySource, createModel, updateModel, deleteModel, addFiles,
  updateFileConversion,
  getAllTags,
  getAllProjects, getProject, createProject, updateProject, deleteProject,
  getSetting, setSetting,
  countUsers, getUserByEmail, getUserById, createUser, updateUserPassword,
  createPasswordReset, getValidReset, markResetUsed,
  getLicenseRule, upsertLicenseRule, listLicenseRules, distinctModelLicenses, applyLicenseRuleToModels,
  reconcileModelsToRules,
};