import { Sequelize } from 'sequelize';
import dotenv from 'dotenv';

dotenv.config();

// Store dynamic database connections
const projectConnections = new Map();

// Get or create a database connection for a specific project
export const getProjectConnection = async (dbname) => {
  try {
    // Check if connection already exists
    if (projectConnections.has(dbname)) {
      const connection = projectConnections.get(dbname);
      // Test the connection
      await connection.authenticate();
      return connection;
    }

    // Create new connection for the project database
    const connection = new Sequelize({
      dialect: 'postgres',
      host: process.env.DB_HOST,
      port: process.env.DB_PORT,
      username: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: dbname,
      logging: false, // Disable logging for project databases
      pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
      }
    });

    // Test the connection
    await connection.authenticate();
    
    // Store the connection
    projectConnections.set(dbname, connection);
    
    console.log(`Connected to project database: ${dbname}`);
    return connection;
  } catch (error) {
    console.error(`Error connecting to project database ${dbname}:`, error);
    throw error;
  }
};

// Create a new database for a project
export const createProjectDatabase = async (dbname) => {
  try {
    // Connect to the main database to create the new database
    const mainConnection = new Sequelize({
      dialect: 'postgres',
      host: process.env.DB_HOST,
      port: process.env.DB_PORT,
      username: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
    });

    // Create the database
    await mainConnection.query(`CREATE DATABASE "${dbname}"`);
    console.log(`Database ${dbname} created successfully`);
    
    // Close the main connection
    await mainConnection.close();

    // Create tables in the new database
    const projectConnection = await getProjectConnection(dbname);
    await createProjectTables(projectConnection);
    
    return projectConnection;
  } catch (error) {
    console.error(`Error creating project database ${dbname}:`, error);
    throw error;
  }
};

// Create required tables in project database
export const createProjectTables = async (connection) => {
  try {
    // Create surveys table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS surveys (
        survey_id SERIAL PRIMARY KEY,
        tree_name VARCHAR(200) NOT NULL,
        local_name VARCHAR(200),
        other_name VARCHAR(200),
        latitude DECIMAL(10, 8) NOT NULL,
        longitude DECIMAL(11, 8) NOT NULL,
        tree_height DECIMAL(8, 2),
        tree_girth DECIMAL(8, 2),
        tree_age INTEGER,
        tree_condition VARCHAR(50),
        tree_ownership VARCHAR(50),
        economic_importance VARCHAR(50),
        remarks TEXT,
        species_type VARCHAR(50),
        is_heritage BOOLEAN DEFAULT FALSE,
        is_rare BOOLEAN DEFAULT FALSE,
        is_indigenous BOOLEAN DEFAULT TRUE,
        is_exotic BOOLEAN DEFAULT FALSE,
        host_plants TEXT,
        images JSON,
        user_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        treelogy_id INTEGER NOT NULL,
        survey_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        entered_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        status VARCHAR(20) DEFAULT 'ACTIVE',
        verified BOOLEAN DEFAULT FALSE,
        verified_by INTEGER,
        verified_at TIMESTAMP
      )
    `);

    // Create indexes for better performance
    await connection.query(`
      CREATE INDEX IF NOT EXISTS idx_surveys_lat_lng ON surveys(latitude, longitude)
    `);
    
    await connection.query(`
      CREATE INDEX IF NOT EXISTS idx_surveys_treelogy_id ON surveys(treelogy_id)
    `);
    
    await connection.query(`
      CREATE INDEX IF NOT EXISTS idx_surveys_user_id ON surveys(user_id)
    `);
    
    await connection.query(`
      CREATE INDEX IF NOT EXISTS idx_surveys_survey_date ON surveys(survey_date)
    `);

    console.log('Project tables created successfully');
  } catch (error) {
    console.error('Error creating project tables:', error);
    throw error;
  }
};

// Close all project connections
export const closeAllProjectConnections = async () => {
  try {
    for (const [dbname, connection] of projectConnections) {
      await connection.close();
      console.log(`Closed connection to ${dbname}`);
    }
    projectConnections.clear();
  } catch (error) {
    console.error('Error closing project connections:', error);
  }
};

// Check if a project database exists
export const checkProjectDatabaseExists = async (dbname) => {
  try {
    const connection = await getProjectConnection(dbname);
    return true;
  } catch (error) {
    return false;
  }
};

// Get all project databases
export const getAllProjectDatabases = async () => {
  try {
    const mainConnection = new Sequelize({
      dialect: 'postgres',
      host: process.env.DB_HOST,
      port: process.env.DB_PORT,
      username: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
    });

    const [results] = await mainConnection.query(`
      SELECT datname FROM pg_database 
      WHERE datname NOT IN ('template0', 'template1', 'postgres')
      AND datname LIKE '%_%'
    `);

    await mainConnection.close();
    return results.map(row => row.datname);
  } catch (error) {
    console.error('Error getting project databases:', error);
    throw error;
  }
};
