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

const DB_PATH = path.join(__dirname, '../database/datacenter.db');

class Database {
  constructor() {
    this.db = null;
  }

  init() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(DB_PATH, (err) => {
        if (err) {
          reject(err);
        } else {
          console.log('Connected to SQLite database');
          this.createTables()
            .then(() => this.seedDeviceTypes())
            .then(() => this.ensureDefaultProject())
            .then(resolve)
            .catch(reject);
        }
      });
    });
  }

  createTables() {
    return new Promise((resolve, reject) => {
      this.db.serialize(() => {
        // Projects table
        this.db.run(`
          CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            description TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
          )
        `);

        // Racks table
        this.db.run(`
          CREATE TABLE IF NOT EXISTS racks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            project_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            x REAL NOT NULL,
            y REAL NOT NULL,
            width REAL DEFAULT 520,
            height REAL DEFAULT 1510,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
            UNIQUE(project_id, name)
          )
        `);

        // Device types table (library of available devices)
        this.db.run(`
          CREATE TABLE IF NOT EXISTS device_types (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            ports_count INTEGER NOT NULL DEFAULT 24,
            color TEXT DEFAULT '#4A90E2'
          )
        `);

        // Devices table (instances placed in racks)
        this.db.run(`
          CREATE TABLE IF NOT EXISTS devices (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            device_type_id INTEGER NOT NULL,
            rack_id INTEGER NOT NULL,
            position INTEGER NOT NULL,
            name TEXT NOT NULL,
            logical_x REAL,
            logical_y REAL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (device_type_id) REFERENCES device_types(id),
            FOREIGN KEY (rack_id) REFERENCES racks(id) ON DELETE CASCADE
          )
        `);

        // Connections table
        this.db.run(`
          CREATE TABLE IF NOT EXISTS connections (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            source_device_id INTEGER NOT NULL,
            source_port INTEGER NOT NULL,
            target_device_id INTEGER NOT NULL,
            target_port INTEGER NOT NULL,
            waypoints TEXT,
            waypoints_physical TEXT,
            waypoints_logical TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (source_device_id) REFERENCES devices(id) ON DELETE CASCADE,
            FOREIGN KEY (target_device_id) REFERENCES devices(id) ON DELETE CASCADE,
            UNIQUE(source_device_id, source_port),
            UNIQUE(target_device_id, target_port)
          )
        `, (err) => {
          if (err) {
            reject(err);
          } else {
            // Add waypoints column if it doesn't exist (for existing databases)
            this.db.run(`
              ALTER TABLE connections ADD COLUMN waypoints TEXT
            `, (err) => {
              // Ignore error if column already exists

              // Add view-specific waypoints columns
              this.db.run(`
                ALTER TABLE connections ADD COLUMN waypoints_physical TEXT
              `, (err) => {
                // Ignore error if column already exists
                this.db.run(`
                  ALTER TABLE connections ADD COLUMN waypoints_logical TEXT
                `, (err) => {
                  // Ignore error if column already exists

                  // Add logical view position columns to devices if they don't exist
                  this.db.run(`
                    ALTER TABLE devices ADD COLUMN logical_x REAL
                  `, (err) => {
                    // Ignore error if column already exists
                    this.db.run(`
                      ALTER TABLE devices ADD COLUMN logical_y REAL
                    `, (err) => {
                      // Ignore error if column already exists
                      this.db.run(`
                        ALTER TABLE devices ADD COLUMN rack_units INTEGER DEFAULT 1
                      `, (err) => {
                        // Ignore error if column already exists
                        resolve();
                      });
                    });
                  });
                });
              });
            });
          }
        });
      });
    });
  }

  seedDeviceTypes() {
    return new Promise((resolve, reject) => {
      const stmt = this.db.prepare('INSERT OR IGNORE INTO device_types (name, ports_count, color) VALUES (?, ?, ?)');

      const deviceTypes = [
        ['Switch 24-Port', 24, '#4A90E2'],
        ['Switch 48-Port', 48, '#5CA6E8'],
        ['Router', 8, '#E27D60'],
        ['Firewall', 6, '#E8A87C'],
        ['Server', 4, '#41B3A3'],
        ['Storage', 8, '#38A169'],
        ['Patch Panel 24', 24, '#9B59B6'],
        ['Patch Panel 48', 48, '#A569BD']
      ];

      deviceTypes.forEach(([name, ports, color]) => {
        stmt.run(name, ports, color);
      });

      stmt.finalize((err) => {
        if (err) reject(err);
        else {
          console.log('Device types seeded');
          resolve();
        }
      });
    });
  }

  ensureDefaultProject() {
    return new Promise((resolve, reject) => {
      this.db.run(
        'INSERT OR IGNORE INTO projects (id, name, description) VALUES (1, ?, ?)',
        ['Default Project', 'Default datacenter project'],
        (err) => {
          if (err) reject(err);
          else {
            console.log('Default project ensured');
            resolve();
          }
        }
      );
    });
  }

  // Project operations
  getAllProjects() {
    return new Promise((resolve, reject) => {
      this.db.all('SELECT * FROM projects ORDER BY updated_at DESC', (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  getProject(id) {
    return new Promise((resolve, reject) => {
      this.db.get('SELECT * FROM projects WHERE id = ?', [id], (err, row) => {
        if (err) reject(err);
        else resolve(row);
      });
    });
  }

  createProject(name, description = '') {
    return new Promise((resolve, reject) => {
      this.db.run(
        'INSERT INTO projects (name, description) VALUES (?, ?)',
        [name, description],
        function(err) {
          if (err) reject(err);
          else resolve({ id: this.lastID, name, description });
        }
      );
    });
  }

  updateProject(id, name, description) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE projects SET name = ?, description = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [name, description, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  deleteProject(id) {
    return new Promise((resolve, reject) => {
      // Check if this is the last project
      this.db.get('SELECT COUNT(*) as count FROM projects', (err, row) => {
        if (err) {
          reject(err);
          return;
        }

        if (row.count <= 1) {
          reject(new Error('Cannot delete the last project'));
          return;
        }

        // Delete the project (cascade will handle racks, devices, connections)
        this.db.run('DELETE FROM projects WHERE id = ?', [id], (err) => {
          if (err) reject(err);
          else resolve();
        });
      });
    });
  }

  // Rack operations
  getAllRacks(projectId) {
    return new Promise((resolve, reject) => {
      this.db.all('SELECT * FROM racks WHERE project_id = ? ORDER BY name', [projectId], (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  createRack(projectId, name, x, y) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'INSERT INTO racks (project_id, name, x, y) VALUES (?, ?, ?, ?)',
        [projectId, name, x, y],
        (err) => {
          if (err) {
            reject(err);
          } else {
            // Fetch the complete rack data with width and height defaults
            this.db.get(
              'SELECT * FROM racks WHERE project_id = ? AND name = ? ORDER BY id DESC LIMIT 1',
              [projectId, name],
              (err, row) => {
                if (err) reject(err);
                else resolve(row);
              }
            );
          }
        }
      );
    });
  }

  getNextRackName(projectId, prefix = 'RACK') {
    return new Promise((resolve, reject) => {
      this.db.all(
        `SELECT name FROM racks WHERE project_id = ? AND name LIKE ? ORDER BY name DESC`,
        [projectId, `${prefix}.%`],
        (err, rows) => {
          if (err) {
            reject(err);
          } else if (rows.length === 0) {
            resolve(`${prefix}.01`);
          } else {
            const lastNum = parseInt(rows[0].name.split('.').pop());
            const nextNum = (lastNum + 1).toString().padStart(2, '0');
            resolve(`${prefix}.${nextNum}`);
          }
        }
      );
    });
  }

  updateRackPosition(id, x, y) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE racks SET x = ?, y = ? WHERE id = ?',
        [x, y, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  updateRackName(id, name) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE racks SET name = ? WHERE id = ?',
        [name, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  deleteRack(id) {
    return new Promise((resolve, reject) => {
      this.db.run('DELETE FROM racks WHERE id = ?', [id], (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  // Device type operations
  getAllDeviceTypes() {
    return new Promise((resolve, reject) => {
      this.db.all('SELECT * FROM device_types ORDER BY name', (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  // Device operations
  getAllDevices(projectId) {
    return new Promise((resolve, reject) => {
      this.db.all(`
        SELECT d.*, dt.name as type_name, dt.ports_count, dt.color
        FROM devices d
        JOIN device_types dt ON d.device_type_id = dt.id
        JOIN racks r ON d.rack_id = r.id
        WHERE r.project_id = ?
        ORDER BY d.rack_id, d.position
      `, [projectId], (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  createDevice(deviceTypeId, rackId, position, name) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'INSERT INTO devices (device_type_id, rack_id, position, name) VALUES (?, ?, ?, ?)',
        [deviceTypeId, rackId, position, name],
        function(err) {
          if (err) reject(err);
          else resolve({ id: this.lastID });
        }
      );
    });
  }

  deleteDevice(id) {
    return new Promise((resolve, reject) => {
      this.db.run('DELETE FROM devices WHERE id = ?', [id], (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  updateDeviceRack(id, rackId, position) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE devices SET rack_id = ?, position = ? WHERE id = ?',
        [rackId, position, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  updateDeviceLogicalPosition(id, x, y) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE devices SET logical_x = ?, logical_y = ? WHERE id = ?',
        [x, y, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  updateDeviceName(id, name) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE devices SET name = ? WHERE id = ?',
        [name, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  updateDeviceRackUnits(id, rackUnits) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE devices SET rack_units = ? WHERE id = ?',
        [rackUnits, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  updateConnection(id, sourceDeviceId, sourcePort, targetDeviceId, targetPort) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE connections SET source_device_id = ?, source_port = ?, target_device_id = ?, target_port = ? WHERE id = ?',
        [sourceDeviceId, sourcePort, targetDeviceId, targetPort, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  // Connection operations
  getAllConnections(projectId) {
    return new Promise((resolve, reject) => {
      this.db.all(`
        SELECT c.* FROM connections c
        JOIN devices d ON c.source_device_id = d.id
        JOIN racks r ON d.rack_id = r.id
        WHERE r.project_id = ?
      `, [projectId], (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  createConnection(sourceDeviceId, sourcePort, targetDeviceId, targetPort) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'INSERT INTO connections (source_device_id, source_port, target_device_id, target_port) VALUES (?, ?, ?, ?)',
        [sourceDeviceId, sourcePort, targetDeviceId, targetPort],
        function(err) {
          if (err) reject(err);
          else resolve({ id: this.lastID });
        }
      );
    });
  }

  deleteConnection(id) {
    return new Promise((resolve, reject) => {
      this.db.run('DELETE FROM connections WHERE id = ?', [id], (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  updateConnectionWaypoints(id, waypoints, view = null) {
    return new Promise((resolve, reject) => {
      const waypointsJson = JSON.stringify(waypoints);

      let query, params;
      if (view === 'physical') {
        query = 'UPDATE connections SET waypoints_physical = ? WHERE id = ?';
        params = [waypointsJson, id];
      } else if (view === 'logical') {
        query = 'UPDATE connections SET waypoints_logical = ? WHERE id = ?';
        params = [waypointsJson, id];
      } else {
        // Legacy support - update old waypoints column
        query = 'UPDATE connections SET waypoints = ? WHERE id = ?';
        params = [waypointsJson, id];
      }

      this.db.run(query, params, (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  getUsedPorts(deviceId) {
    return new Promise((resolve, reject) => {
      this.db.all(`
        SELECT source_port as port FROM connections WHERE source_device_id = ?
        UNION
        SELECT target_port as port FROM connections WHERE target_device_id = ?
      `, [deviceId, deviceId], (err, rows) => {
        if (err) reject(err);
        else resolve(rows.map(r => r.port));
      });
    });
  }

  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }
}

module.exports = new Database();