import pkg from 'pg';
const { Pool } = pkg;
import type { Pool as PoolType, PoolConfig } from 'pg';

let pool: PoolType | null = null;

export interface DatabaseConfig {
  host?: string;
  port?: number;
  user?: string;
  password?: string;
  database?: string;
  connectionString?: string;
}

/**
 * Get or initialize the database pool
 */
export function getDatabase(): PoolType {
  if (!pool) {
    throw new Error('Database not initialized. Call setupDatabase() first.');
  }
  return pool;
}

/**
 * Initialize database with connection parameters
 */
export async function setupDatabase(config?: DatabaseConfig): Promise<PoolType> {
  // Close existing pool if it exists to prevent resource leaks
  if (pool) {
    try {
      await pool.end();
    } catch (err) {
      console.error('Error closing existing pool:', err);
    }
  }

  const poolConfig: PoolConfig = {};

  // Support connection string or individual parameters
  if (config?.connectionString) {
    poolConfig.connectionString = config.connectionString;
  } else {
    // Use provided config or environment variables or defaults
    poolConfig.host = config?.host || process.env.PGHOST || 'localhost';
    poolConfig.port = config?.port || parseInt(process.env.PGPORT || '5432', 10);
    poolConfig.user = config?.user || process.env.PGUSER || 'postgres';
    poolConfig.password = config?.password || process.env.PGPASSWORD || '';
    poolConfig.database = config?.database || process.env.PGDATABASE || 'remix_mcp';
  }

  pool = new Pool(poolConfig);

  console.error(`Database pool initialized`);

  return pool;
}

// Initialize database schema
export async function initializeDatabase() {
  const db = getDatabase();
  
  try {
    // Conversations table
    await db.query(`
      CREATE TABLE IF NOT EXISTS conversations (
        id TEXT PRIMARY KEY,
        parent_id TEXT,
        title TEXT NOT NULL,
        task_description TEXT NOT NULL,
        created_at BIGINT NOT NULL,
        FOREIGN KEY (parent_id) REFERENCES conversations(id) ON DELETE CASCADE
      )
    `);

    // Messages table
    await db.query(`
      CREATE TABLE IF NOT EXISTS messages (
        id TEXT PRIMARY KEY,
        conversation_id TEXT NOT NULL,
        prev_message_id TEXT,
        next_message_id TEXT,
        author TEXT NOT NULL CHECK(author IN ('User', 'Copilot')),
        payload TEXT NOT NULL,
        created_at BIGINT NOT NULL,
        FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
        FOREIGN KEY (prev_message_id) REFERENCES messages(id) ON DELETE SET NULL,
        FOREIGN KEY (next_message_id) REFERENCES messages(id) ON DELETE SET NULL
      )
    `);

    // Create indexes for better query performance
    await db.query(`
      CREATE INDEX IF NOT EXISTS idx_conversations_parent 
      ON conversations(parent_id)
    `);

    await db.query(`
      CREATE INDEX IF NOT EXISTS idx_messages_conversation 
      ON messages(conversation_id)
    `);

    await db.query(`
      CREATE INDEX IF NOT EXISTS idx_messages_prev 
      ON messages(prev_message_id)
    `);

    await db.query(`
      CREATE INDEX IF NOT EXISTS idx_messages_next 
      ON messages(next_message_id)
    `);

    // Create GIN index for full-text search on messages using PostgreSQL's built-in text search
    await db.query(`
      CREATE INDEX IF NOT EXISTS idx_messages_payload_gin 
      ON messages USING gin(to_tsvector('english', payload))
    `);

    console.error('Database schema initialized');
  } catch (error) {
    console.error('Error initializing database schema:', error);
    throw error;
  }
}

export interface Conversation {
  id: string;
  parent_id: string | null;
  title: string;
  task_description: string;
  created_at: number;
}

export interface Message {
  id: string;
  conversation_id: string;
  prev_message_id: string | null;
  next_message_id: string | null;
  author: 'User' | 'Copilot';
  payload: string;
  created_at: number;
}

export interface MessageInput {
  author: 'User' | 'Copilot';
  payload: string;
}