import sqlite3 from "sqlite3";
import fs from "fs";
import path from "path";
import { config } from "../config.js";

const dbFile = config.databaseFile;
fs.mkdirSync(path.dirname(dbFile), { recursive: true });

sqlite3.verbose();
export const db = new sqlite3.Database(dbFile);

export function initDb() {
  return new Promise((resolve, reject) => {
    db.serialize(() => {
      db.run(`
        CREATE TABLE IF NOT EXISTS models (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          chat_id TEXT NOT NULL,
          message_id INTEGER NOT NULL,
          name TEXT,
          description TEXT,
          filename TEXT,
          extension TEXT,
          mime_type TEXT,
          size_bytes INTEGER,
          thumbnail_path TEXT,
          message_date TEXT,
          indexed_at TEXT DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(chat_id, message_id)
        )
      `, err => {
        if (err) return reject(err);

        db.run(`
          CREATE TABLE IF NOT EXISTS sources (
            chat_id TEXT PRIMARY KEY,
            title TEXT,
            username TEXT,
            type TEXT,
            enabled INTEGER DEFAULT 1,
            last_message_id INTEGER DEFAULT 0,
            last_sync_at TEXT,
            added_at TEXT DEFAULT CURRENT_TIMESTAMP
          )
        `, err2 => {
          if (err2) return reject(err2);

          db.run(`
            CREATE TABLE IF NOT EXISTS users (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              username TEXT NOT NULL UNIQUE,
              password_hash TEXT NOT NULL,
              role TEXT NOT NULL DEFAULT 'member',
              created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
          `, err3 => {
            if (err3) return reject(err3);

            db.run(`
              CREATE TABLE IF NOT EXISTS web_sessions (
                token_hash TEXT PRIMARY KEY,
                user_id INTEGER NOT NULL,
                expires_at INTEGER NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(user_id) REFERENCES users(id)
              )
            `, err4 => {
              if (err4) return reject(err4);
              resolve();
            });
          });
        });
      });
    });
  });
}

export function upsertModel(model) {
  return new Promise((resolve, reject) => {
    db.run(`
      INSERT INTO models (
        chat_id, message_id, name, description, filename, extension,
        mime_type, size_bytes, thumbnail_path, message_date
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(chat_id, message_id) DO UPDATE SET
        name = excluded.name,
        description = excluded.description,
        filename = excluded.filename,
        extension = excluded.extension,
        mime_type = excluded.mime_type,
        size_bytes = excluded.size_bytes,
        thumbnail_path = excluded.thumbnail_path,
        message_date = excluded.message_date,
        indexed_at = CURRENT_TIMESTAMP
    `, [
      model.chat_id,
      model.message_id,
      model.name,
      model.description,
      model.filename,
      model.extension,
      model.mime_type,
      model.size_bytes,
      model.thumbnail_path,
      model.message_date
    ], function(err) {
      if (err) reject(err);
      else resolve(this.lastID);
    });
  });
}

export function searchModels(query = "", limit = 50) {
  return new Promise((resolve, reject) => {
    const clean = String(query).trim();
    const like = `%${clean}%`;

    db.all(`
      SELECT
        id, name, description, filename, extension, mime_type,
        size_bytes, thumbnail_path, message_date
      FROM models
      WHERE ? = ''
         OR name LIKE ?
         OR description LIKE ?
         OR filename LIKE ?
      ORDER BY message_date DESC, id DESC
      LIMIT ?
    `, [clean, like, like, like, Number(limit)], (err, rows) => {
      if (err) reject(err);
      else resolve(rows);
    });
  });
}

export function getModelById(id) {
  return new Promise((resolve, reject) => {
    db.get(`
      SELECT * FROM models WHERE id = ? LIMIT 1
    `, [Number(id)], (err, row) => {
      if (err) reject(err);
      else resolve(row || null);
    });
  });
}

export function upsertSource(source) {
  return new Promise((resolve, reject) => {
    db.run(`
      INSERT INTO sources (
        chat_id, title, username, type, enabled
      )
      VALUES (?, ?, ?, ?, 1)
      ON CONFLICT(chat_id) DO UPDATE SET
        title = excluded.title,
        username = excluded.username,
        type = excluded.type
    `, [
      source.id,
      source.title,
      source.username,
      source.type
    ], function(err) {
      if (err) reject(err);
      else resolve();
    });
  });
}

export function listSources() {
  return new Promise((resolve, reject) => {
    db.all(`
      SELECT
        chat_id,
        title,
        username,
        type,
        enabled,
        last_message_id,
        last_sync_at,
        added_at
      FROM sources
      ORDER BY title COLLATE NOCASE
    `, (err, rows) => {
      if (err) reject(err);
      else resolve(rows);
    });
  });
}

export function getEnabledSources() {
  return new Promise((resolve, reject) => {
    db.all(`
      SELECT *
      FROM sources
      WHERE enabled = 1
      ORDER BY title COLLATE NOCASE
    `, (err, rows) => {
      if (err) reject(err);
      else resolve(rows);
    });
  });
}

export function setSourceEnabled(chatId, enabled) {
  return new Promise((resolve, reject) => {
    db.run(
      `UPDATE sources SET enabled = ? WHERE chat_id = ?`,
      [enabled ? 1 : 0, String(chatId)],
      err => err ? reject(err) : resolve()
    );
  });
}

export function updateSourceProgress(chatId, lastMessageId) {
  return new Promise((resolve, reject) => {
    db.run(`
      UPDATE sources
      SET last_message_id = ?,
          last_sync_at = CURRENT_TIMESTAMP
      WHERE chat_id = ?
    `, [Number(lastMessageId || 0), String(chatId)], err => {
      if (err) reject(err);
      else resolve();
    });
  });
}


export function updateModelThumbnail(chatId, messageId, thumbnailPath) {
  return new Promise((resolve, reject) => {
    db.run(
      `
      UPDATE models
      SET thumbnail_path = ?,
          indexed_at = CURRENT_TIMESTAMP
      WHERE chat_id = ? AND message_id = ?
      `,
      [
        thumbnailPath,
        String(chatId),
        Number(messageId)
      ],
      err => err ? reject(err) : resolve()
    );
  });
}


export function resetSourceProgress(chatId = null) {
  return new Promise((resolve, reject) => {
    const sql = chatId
      ? `UPDATE sources SET last_message_id = 0 WHERE chat_id = ?`
      : `UPDATE sources SET last_message_id = 0`;

    const params = chatId ? [String(chatId)] : [];

    db.run(sql, params, err => err ? reject(err) : resolve());
  });
}


export function createUser({ username, passwordHash, role = "member" }) {
  return new Promise((resolve, reject) => {
    db.run(
      `
      INSERT INTO users (username, password_hash, role)
      VALUES (?, ?, ?)
      `,
      [username, passwordHash, role],
      function (err) {
        if (err) return reject(err);
        resolve({
          id: this.lastID,
          username,
          role
        });
      }
    );
  });
}

export function findUserByUsername(username) {
  return new Promise((resolve, reject) => {
    db.get(
      `
      SELECT id, username, password_hash, role
      FROM users
      WHERE username = ?
      LIMIT 1
      `,
      [String(username).trim().toLowerCase()],
      (err, row) => {
        if (err) reject(err);
        else resolve(row || null);
      }
    );
  });
}

export function countUsers() {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT COUNT(*) AS count FROM users`,
      (err, row) => {
        if (err) reject(err);
        else resolve(Number(row?.count || 0));
      }
    );
  });
}

export function createWebSession({ tokenHash, userId, expiresAt }) {
  return new Promise((resolve, reject) => {
    db.run(
      `
      INSERT INTO web_sessions (token_hash, user_id, expires_at)
      VALUES (?, ?, ?)
      `,
      [tokenHash, Number(userId), Number(expiresAt)],
      err => err ? reject(err) : resolve()
    );
  });
}

export function getWebSession(tokenHash) {
  return new Promise((resolve, reject) => {
    db.get(
      `
      SELECT
        s.token_hash,
        s.expires_at,
        u.id,
        u.username,
        u.role
      FROM web_sessions s
      JOIN users u ON u.id = s.user_id
      WHERE s.token_hash = ?
        AND s.expires_at > ?
      LIMIT 1
      `,
      [tokenHash, Date.now()],
      (err, row) => {
        if (err) reject(err);
        else resolve(row || null);
      }
    );
  });
}

export function deleteWebSession(tokenHash) {
  return new Promise((resolve, reject) => {
    db.run(
      `DELETE FROM web_sessions WHERE token_hash = ?`,
      [tokenHash],
      err => err ? reject(err) : resolve()
    );
  });
}
