const path = require('path');
const sqlite3 = require('sqlite3').verbose();

const dbPath = path.join(__dirname, '..', 'data', 'app.db');
const db = new sqlite3.Database(dbPath);

const SAMPLE_USERS = [
  {
    username: 'admin',
    password: 'Admin!123',
    role: 'admin',
    displayName: 'Istasyon Admini'
  },
  {
    username: 'yakitsorum',
    password: 'Yakit@123',
    role: 'fuel_manager',
    displayName: 'Yakit Sorumlusu'
  },
  {
    username: 'malsorum1',
    password: 'Mal@123',
    role: 'inventory_manager',
    displayName: 'Mal Sorumlusu 1'
  }
];

const SAMPLE_VEHICLES = [
  { brand: 'Ford', model: 'Transit', year: 2021, plate: '34 AYT 312' },
  { brand: 'Isuzu', model: 'NPR', year: 2019, plate: '34 FZT 908' }
];

const SAMPLE_UNITS = [
  {
    name: 'Merkez Birlik',
    address: 'Cumhuriyet Mah. İstasyon Cad. No:12/1 İstanbul',
    stk: 'STK-4589',
    btk: 'BTK-9021',
    contactName: 'Yzb. Murat Kaya',
    contactRank: 'Yuzbasi',
    contactRegistry: 'MK4587',
    contactIdentity: '25478963210',
    contactPhone: '+90 532 456 78 12'
  },
  {
    name: 'Doğu Lojistik Birimi',
    address: 'Sanayi Mah. Depo Sok. No:8 Erzurum',
    stk: 'STK-7865',
    btk: 'BTK-6674',
    contactName: 'Uzm. Cav. Esra Yilmaz',
    contactRank: 'Uzman Cavus',
    contactRegistry: 'EY3345',
    contactIdentity: '19876543219',
    contactPhone: '+90 532 998 11 44'
  }
];

const SAMPLE_FUEL_PERSONNEL = [
  {
    fullName: 'Astsb. Cahit Demir',
    rank: 'Astsubay',
    registryNumber: 'CD5561',
    identityNumber: '14523698741',
    phone: '+90 532 223 45 67'
  },
  {
    fullName: 'Sv. Uzm. Er Ali Korkmaz',
    rank: 'Sozlesmeli Er',
    registryNumber: 'AK7812',
    identityNumber: '32987456100',
    phone: '+90 555 893 22 10'
  }
];

function run(query, params = []) {
  return new Promise((resolve, reject) => {
    db.run(query, params, function callback(err) {
      if (err) {
        reject(err);
      } else {
        resolve(this);
      }
    });
  });
}

function ensureColumn(table, column, definition) {
  return new Promise((resolve, reject) => {
    db.all(`PRAGMA table_info(${table})`, [], (err, rows) => {
      if (err) {
        reject(err);
        return;
      }

      const exists = rows.some((row) => row.name === column);
      if (exists) {
        resolve();
        return;
      }

      db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`, (alterErr) => {
        if (alterErr) {
          reject(alterErr);
        } else {
          resolve();
        }
      });
    });
  });
}

function initialize() {
  return new Promise((resolve, reject) => {
    db.serialize(async () => {
      try {
        await run(
          `CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL CHECK(role IN ('admin','fuel_manager','inventory_manager')),
            display_name TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
          )`
        );

        await run(
          `CREATE TABLE IF NOT EXISTS vehicles (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            brand TEXT NOT NULL,
            model TEXT NOT NULL,
            year INTEGER NOT NULL,
            plate TEXT UNIQUE NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
          )`
        );

        await run(
          `CREATE TABLE IF NOT EXISTS units (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            address TEXT NOT NULL,
            stk TEXT NOT NULL,
            btk TEXT NOT NULL,
            contact_name TEXT NOT NULL,
            contact_rank TEXT NOT NULL,
            contact_registry TEXT NOT NULL,
            contact_identity TEXT NOT NULL,
            contact_phone TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
          )`
        );

        await run(
          `CREATE TABLE IF NOT EXISTS fuel_personnel (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            full_name TEXT NOT NULL,
            rank TEXT NOT NULL,
            registry_number TEXT UNIQUE NOT NULL,
            identity_number TEXT NOT NULL,
            phone TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
          )`
        );

        await run(
          `CREATE TABLE IF NOT EXISTS fuel_slips (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slip_number INTEGER UNIQUE,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            slip_date TEXT NOT NULL,
            force TEXT NOT NULL,
            unit_id INTEGER NOT NULL,
            unit_name TEXT NOT NULL,
            vehicle_id INTEGER NOT NULL,
            vehicle_description TEXT NOT NULL,
            plate TEXT NOT NULL,
            fuel_amount_number REAL NOT NULL,
            fuel_amount_text TEXT NOT NULL,
            fuel_type TEXT NOT NULL,
            receiver_id INTEGER NOT NULL,
            receiver_name TEXT NOT NULL,
            receiver_rank TEXT NOT NULL,
            receiver_registry TEXT NOT NULL,
            receiver_phone TEXT NOT NULL,
            giver_id INTEGER NOT NULL,
            giver_name TEXT NOT NULL,
            giver_rank TEXT NOT NULL,
            giver_registry TEXT NOT NULL,
            giver_phone TEXT NOT NULL,
            notes TEXT,
            inventory_manager_id INTEGER NOT NULL,
            fuel_manager_id INTEGER NOT NULL,
            status TEXT DEFAULT 'pending',
            rejection_reason TEXT,
            FOREIGN KEY (unit_id) REFERENCES units(id),
            FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
            FOREIGN KEY (receiver_id) REFERENCES fuel_personnel(id),
            FOREIGN KEY (giver_id) REFERENCES fuel_personnel(id),
            FOREIGN KEY (inventory_manager_id) REFERENCES users(id),
            FOREIGN KEY (fuel_manager_id) REFERENCES users(id)
          )`
        );

        await Promise.all([
          ensureColumn('fuel_slips', 'inventory_manager_id', 'INTEGER'),
          ensureColumn('fuel_slips', 'fuel_manager_id', 'INTEGER'),
          ensureColumn('fuel_slips', 'status', "TEXT DEFAULT 'pending'"),
          ensureColumn('fuel_slips', 'rejection_reason', 'TEXT')
        ]).catch((err) => {
          // Ignored if columns already exist
          if (err && !/duplicate column/i.test(err.message)) {
            throw err;
          }
        });

        for (const user of SAMPLE_USERS) {
          await run(
            `INSERT OR IGNORE INTO users (username, password, role, display_name)
             VALUES (?, ?, ?, ?)`,
            [user.username, user.password, user.role, user.displayName]
          );
        }

        for (const vehicle of SAMPLE_VEHICLES) {
          await run(
            `INSERT OR IGNORE INTO vehicles (brand, model, year, plate)
             VALUES (?, ?, ?, ?)`,
            [vehicle.brand, vehicle.model, vehicle.year, vehicle.plate]
          );
        }

        for (const unit of SAMPLE_UNITS) {
          await run(
            `INSERT OR IGNORE INTO units (
              name, address, stk, btk, contact_name, contact_rank,
              contact_registry, contact_identity, contact_phone
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
              unit.name,
              unit.address,
              unit.stk,
              unit.btk,
              unit.contactName,
              unit.contactRank,
              unit.contactRegistry,
              unit.contactIdentity,
              unit.contactPhone
            ]
          );
        }

        for (const personnel of SAMPLE_FUEL_PERSONNEL) {
          await run(
            `INSERT OR IGNORE INTO fuel_personnel (
              full_name, rank, registry_number, identity_number, phone
            ) VALUES (?, ?, ?, ?, ?)`,
            [
              personnel.fullName,
              personnel.rank,
              personnel.registryNumber,
              personnel.identityNumber,
              personnel.phone
            ]
          );
        }

        resolve();
      } catch (err) {
        reject(err);
      }
    });
  });
}

function getUserByUsername(username) {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT id, username, password, role, display_name AS displayName
       FROM users
       WHERE username = ?`,
      [username],
      (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      }
    );
  });
}

function createInventoryManager({ username, password, displayName }) {
  return new Promise((resolve, reject) => {
    db.run(
      `INSERT INTO users (username, password, role, display_name)
       VALUES (?, ?, 'inventory_manager', ?)`,
      [username, password, displayName],
      function insertCallback(err) {
        if (err) {
          reject(err);
          return;
        }

        resolve({
          id: this.lastID,
          username,
          displayName,
          role: 'inventory_manager'
        });
      }
    );
  });
}

function listInventoryManagers() {
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT id, username, display_name AS displayName, created_at AS createdAt
       FROM users
       WHERE role = 'inventory_manager'
       ORDER BY created_at DESC`,
      (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      }
    );
  });
}

function updateInventoryManager({ id, displayName, password }) {
  const updates = [];
  const params = [];

  if (displayName) {
    updates.push('display_name = ?');
    params.push(displayName);
  }

  if (password) {
    updates.push('password = ?');
    params.push(password);
  }

  if (updates.length === 0) {
    return Promise.resolve();
  }

  params.push(id);

  return run(
    `UPDATE users SET ${updates.join(', ')} WHERE id = ? AND role = 'inventory_manager'`,
    params
  );
}

function deleteInventoryManager(id) {
  return run(`DELETE FROM users WHERE id = ? AND role = 'inventory_manager'`, [id]);
}

function createVehicle({ brand, model, year, plate }) {
  return new Promise((resolve, reject) => {
    db.run(
      `INSERT INTO vehicles (brand, model, year, plate)
       VALUES (?, ?, ?, ?)`,
      [brand, model, year, plate],
      function insertCallback(err) {
        if (err) {
          reject(err);
          return;
        }

        resolve({
          id: this.lastID,
          brand,
          model,
          year,
          plate
        });
      }
    );
  });
}

function listVehicles() {
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT id, brand, model, year, plate, created_at AS createdAt
       FROM vehicles
       ORDER BY created_at DESC`,
      (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      }
    );
  });
}

function updateVehicle({ id, brand, model, year, plate }) {
  return run(
    `UPDATE vehicles
     SET brand = ?, model = ?, year = ?, plate = ?
     WHERE id = ?`,
    [brand, model, year, plate, id]
  );
}

function deleteVehicle(id) {
  return run(`DELETE FROM vehicles WHERE id = ?`, [id]);
}

function createUnit(payload) {
  return new Promise((resolve, reject) => {
    db.run(
      `INSERT INTO units (
        name, address, stk, btk, contact_name, contact_rank,
        contact_registry, contact_identity, contact_phone
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        payload.name,
        payload.address,
        payload.stk,
        payload.btk,
        payload.contactName,
        payload.contactRank,
        payload.contactRegistry,
        payload.contactIdentity,
        payload.contactPhone
      ],
      function insertCallback(err) {
        if (err) {
          reject(err);
          return;
        }

        resolve({
          id: this.lastID,
          ...payload
        });
      }
    );
  });
}

function listUnits() {
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT
        id,
        name,
        address,
        stk,
        btk,
        contact_name AS contactName,
        contact_rank AS contactRank,
        contact_registry AS contactRegistry,
        contact_identity AS contactIdentity,
        contact_phone AS contactPhone,
        created_at AS createdAt
      FROM units
      ORDER BY created_at DESC`,
      (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      }
    );
  });
}

function updateUnit(payload) {
  return run(
    `UPDATE units
     SET name = ?, address = ?, stk = ?, btk = ?, contact_name = ?, contact_rank = ?,
         contact_registry = ?, contact_identity = ?, contact_phone = ?
     WHERE id = ?`,
    [
      payload.name,
      payload.address,
      payload.stk,
      payload.btk,
      payload.contactName,
      payload.contactRank,
      payload.contactRegistry,
      payload.contactIdentity,
      payload.contactPhone,
      payload.id
    ]
  );
}

function deleteUnit(id) {
  return run(`DELETE FROM units WHERE id = ?`, [id]);
}

function createFuelPersonnel(payload) {
  return new Promise((resolve, reject) => {
    db.run(
      `INSERT INTO fuel_personnel (
        full_name, rank, registry_number, identity_number, phone
      )
      VALUES (?, ?, ?, ?, ?)`,
      [
        payload.fullName,
        payload.rank,
        payload.registryNumber,
        payload.identityNumber,
        payload.phone
      ],
      function insertCallback(err) {
        if (err) {
          reject(err);
          return;
        }

        resolve({
          id: this.lastID,
          ...payload
        });
      }
    );
  });
}

function listFuelPersonnel() {
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT
        id,
        full_name AS fullName,
        rank,
        registry_number AS registryNumber,
        identity_number AS identityNumber,
        phone,
        created_at AS createdAt
      FROM fuel_personnel
      ORDER BY created_at DESC`,
      (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      }
    );
  });
}

function updateFuelPersonnel(payload) {
  return run(
    `UPDATE fuel_personnel
     SET full_name = ?, rank = ?, registry_number = ?, identity_number = ?, phone = ?
     WHERE id = ?`,
    [
      payload.fullName,
      payload.rank,
      payload.registryNumber,
      payload.identityNumber,
      payload.phone,
      payload.id
    ]
  );
}

function deleteFuelPersonnel(id) {
  return run(`DELETE FROM fuel_personnel WHERE id = ?`, [id]);
}

function getVehicleById(id) {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT id, brand, model, year, plate, created_at AS createdAt
       FROM vehicles
       WHERE id = ?`,
      [id],
      (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      }
    );
  });
}

function getUnitById(id) {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT id, name, address, stk, btk, contact_name AS contactName, contact_rank AS contactRank,
              contact_registry AS contactRegistry, contact_identity AS contactIdentity,
              contact_phone AS contactPhone
       FROM units
       WHERE id = ?`,
      [id],
      (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      }
    );
  });
}

function getFuelPersonnelById(id) {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT id, full_name AS fullName, rank, registry_number AS registryNumber,
              identity_number AS identityNumber, phone
       FROM fuel_personnel
       WHERE id = ?`,
      [id],
      (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      }
    );
  });
}

function getInventoryManagerById(id) {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT id, username, display_name AS displayName
       FROM users
       WHERE id = ? AND role = 'inventory_manager'`,
      [id],
      (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      }
    );
  });
}

async function createFuelSlip(payload) {
  const nextNumber = await new Promise((resolve, reject) => {
    db.get(`SELECT IFNULL(MAX(slip_number), 0) + 1 AS nextNumber FROM fuel_slips`, [], (err, row) => {
      if (err) {
        reject(err);
      } else {
        resolve(row ? row.nextNumber : 1);
      }
    });
  });

  return new Promise((resolve, reject) => {
    db.run(
      `INSERT INTO fuel_slips (
        slip_number,
        slip_date,
        force,
        unit_id,
        unit_name,
        vehicle_id,
        vehicle_description,
        plate,
        fuel_amount_number,
        fuel_amount_text,
        fuel_type,
        receiver_id,
        receiver_name,
        receiver_rank,
        receiver_registry,
        receiver_phone,
        giver_id,
        giver_name,
        giver_rank,
        giver_registry,
        giver_phone,
        notes,
        inventory_manager_id,
        fuel_manager_id,
        status,
        rejection_reason
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)` ,
      [
        nextNumber,
        payload.date,
        payload.force,
        payload.unit.id,
        payload.unit.name,
        payload.vehicle.id,
        payload.vehicle.description,
        payload.vehicle.plate,
        payload.fuelAmountNumber,
        payload.fuelAmountText,
        payload.fuelType,
        payload.receiver.id,
        payload.receiver.fullName,
        payload.receiver.rank,
        payload.receiver.registryNumber,
        payload.receiver.phone,
        payload.giver.id,
        payload.giver.fullName,
        payload.giver.rank,
        payload.giver.registryNumber,
        payload.giver.phone,
        payload.notes || null,
        payload.inventoryManager.id,
        payload.fuelManagerId,
        'pending',
        null
      ],
      function insertCallback(err) {
        if (err) {
          reject(err);
        } else {
          resolve({ id: this.lastID, slipNumber: nextNumber });
        }
      }
    );
  });
}

function listFuelSlips() {
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT
        fs.id,
        fs.slip_number AS slipNumber,
        fs.created_at AS createdAt,
        fs.slip_date AS slipDate,
        fs.force,
        fs.unit_id AS unitId,
        fs.unit_name AS unitName,
        fs.vehicle_id AS vehicleId,
        fs.vehicle_description AS vehicleDescription,
        fs.plate,
        fs.fuel_amount_number AS fuelAmountNumber,
        fs.fuel_amount_text AS fuelAmountText,
        fs.fuel_type AS fuelType,
        fs.receiver_id AS receiverId,
        fs.receiver_name AS receiverName,
        fs.receiver_rank AS receiverRank,
        fs.receiver_registry AS receiverRegistry,
        fs.receiver_phone AS receiverPhone,
        fs.giver_id AS giverId,
        fs.giver_name AS giverName,
        fs.giver_rank AS giverRank,
        fs.giver_registry AS giverRegistry,
        fs.giver_phone AS giverPhone,
        fs.notes,
        fs.inventory_manager_id AS inventoryManagerId,
        inv.display_name AS inventoryManagerName,
        fs.fuel_manager_id AS fuelManagerId,
        fs.status,
        fs.rejection_reason AS rejectionReason
      FROM fuel_slips fs
      LEFT JOIN users inv ON inv.id = fs.inventory_manager_id
      ORDER BY fs.created_at DESC`,
      (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      }
    );
  });
}

function listFuelSlipsByInventoryManager(inventoryManagerId) {
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT
        fs.id,
        fs.slip_number AS slipNumber,
        fs.created_at AS createdAt,
        fs.slip_date AS slipDate,
        fs.force,
        fs.unit_id AS unitId,
        fs.unit_name AS unitName,
        fs.vehicle_id AS vehicleId,
        fs.vehicle_description AS vehicleDescription,
        fs.plate,
        fs.fuel_amount_number AS fuelAmountNumber,
        fs.fuel_amount_text AS fuelAmountText,
        fs.fuel_type AS fuelType,
        fs.receiver_id AS receiverId,
        fs.receiver_name AS receiverName,
        fs.receiver_rank AS receiverRank,
        fs.receiver_registry AS receiverRegistry,
        fs.receiver_phone AS receiverPhone,
        fs.giver_id AS giverId,
        fs.giver_name AS giverName,
        fs.giver_rank AS giverRank,
        fs.giver_registry AS giverRegistry,
        fs.giver_phone AS giverPhone,
        fs.notes,
        fs.inventory_manager_id AS inventoryManagerId,
        inv.display_name AS inventoryManagerName,
        fs.fuel_manager_id AS fuelManagerId,
        fs.status,
        fs.rejection_reason AS rejectionReason
      FROM fuel_slips fs
      LEFT JOIN users inv ON inv.id = fs.inventory_manager_id
      WHERE fs.inventory_manager_id = ?
      ORDER BY fs.created_at DESC`,
      [inventoryManagerId],
      (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      }
    );
  });
}

function getFuelSlipById(id) {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT
        fs.id,
        fs.slip_number AS slipNumber,
        fs.created_at AS createdAt,
        fs.slip_date AS slipDate,
        fs.force,
        fs.unit_id AS unitId,
        fs.unit_name AS unitName,
        fs.vehicle_id AS vehicleId,
        fs.vehicle_description AS vehicleDescription,
        fs.plate,
        fs.fuel_amount_number AS fuelAmountNumber,
        fs.fuel_amount_text AS fuelAmountText,
        fs.fuel_type AS fuelType,
        fs.receiver_id AS receiverId,
        fs.receiver_name AS receiverName,
        fs.receiver_rank AS receiverRank,
        fs.receiver_registry AS receiverRegistry,
        fs.receiver_phone AS receiverPhone,
        fs.giver_id AS giverId,
        fs.giver_name AS giverName,
        fs.giver_rank AS giverRank,
        fs.giver_registry AS giverRegistry,
        fs.giver_phone AS giverPhone,
        fs.notes,
        fs.inventory_manager_id AS inventoryManagerId,
        inv.display_name AS inventoryManagerName,
        fs.fuel_manager_id AS fuelManagerId,
        fs.status,
        fs.rejection_reason AS rejectionReason
      FROM fuel_slips fs
      LEFT JOIN users inv ON inv.id = fs.inventory_manager_id
      WHERE fs.id = ?`,
      [id],
      (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      }
    );
  });
}

function updateFuelSlipStatus({ id, status, rejectionReason }) {
  return run(
    `UPDATE fuel_slips
     SET status = ?, rejection_reason = ?
     WHERE id = ?`,
    [status, rejectionReason || null, id]
  );
}

module.exports = {
  db,
  initialize,
  getUserByUsername,
  createInventoryManager,
  listInventoryManagers,
  updateInventoryManager,
  deleteInventoryManager,
  createVehicle,
  listVehicles,
  updateVehicle,
  deleteVehicle,
  createUnit,
  listUnits,
  updateUnit,
  deleteUnit,
  createFuelPersonnel,
  listFuelPersonnel,
  updateFuelPersonnel,
  deleteFuelPersonnel,
  getVehicleById,
  getUnitById,
  getFuelPersonnelById,
  getInventoryManagerById,
  createFuelSlip,
  listFuelSlips,
  listFuelSlipsByInventoryManager,
  getFuelSlipById,
  updateFuelSlipStatus
};