/**
 * Local PostgreSQL Database Setup Script
 * Creates database, tables, and indexes for local development
 * 
 * Usage: node scripts/setupLocalDB.js
 */

import { Pool } from 'pg';
import dotenv from 'dotenv';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

dotenv.config();

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// Default local PostgreSQL connection (can be overridden via env)
const localDbConfig = {
  host: process.env.LOCAL_DB_HOST || '100.121.163.23',
  port: process.env.LOCAL_DB_PORT || 5432,
  user: process.env.LOCAL_DB_USER || 'postgres',
  password: process.env.LOCAL_DB_PASSWORD || 'postgres',
  database: process.env.LOCAL_DB_NAME || 'institutional_trader'
};

/**
 * Create database if it doesn't exist
 */
async function createDatabase() {
  // Connect to postgres database to create our database
  const adminPool = new Pool({
    host: localDbConfig.host,
    port: localDbConfig.port,
    user: localDbConfig.user,
    password: localDbConfig.password,
    database: 'postgres' // Connect to default postgres database
  });

  try {
    console.log('🔍 Checking if database exists...');
    const result = await adminPool.query(
      `SELECT 1 FROM pg_database WHERE datname = $1`,
      [localDbConfig.database]
    );

    if (result.rows.length === 0) {
      console.log(`📦 Creating database: ${localDbConfig.database}...`);
      await adminPool.query(`CREATE DATABASE ${localDbConfig.database}`);
      console.log('✅ Database created successfully');
    } else {
      console.log('✅ Database already exists');
    }
  } catch (error) {
    console.error('❌ Error creating database:', error.message);
    throw error;
  } finally {
    await adminPool.end();
  }
}

/**
 * Create all tables
 */
async function createTables() {
  const pool = new Pool({
    host: localDbConfig.host,
    port: localDbConfig.port,
    user: localDbConfig.user,
    password: localDbConfig.password,
    database: localDbConfig.database
  });

  try {
    console.log('📋 Creating tables...');

    // OptionsFlow_monthly table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS "OptionsFlow_monthly" (
        "CreatedDate" TEXT,
        "CreatedTime" TEXT,
        "Symbol" TEXT,
        "Type" TEXT,
        "Volume" TEXT,
        "Price" TEXT,
        "Side" TEXT,
        "CallPut" TEXT,
        "Strike" TEXT,
        "Spot" TEXT,
        "Premium" TEXT,
        "ExpirationDate" TEXT,
        "Color" TEXT,
        "ImpliedVolatility" TEXT,
        "Dte" TEXT,
        "ER" TEXT,
        "StockEtf" TEXT,
        "Sector" TEXT,
        "Uoa" TEXT,
        "Weekly" TEXT,
        "MktCap" TEXT,
        "OI" TEXT
      );
    `);
    console.log('   ✅ OptionsFlow_monthly table created');

    // OptionsVolume table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS "OptionsVolume" (
        "SYMBOL" TEXT,
        "VOL" NUMERIC,
        "C VOL" NUMERIC,
        "P VOL" NUMERIC,
        "P/C" NUMERIC,
        "AVG VOL" NUMERIC,
        "RATIO" NUMERIC,
        "COI" NUMERIC,
        "POI" NUMERIC,
        "DCOI" NUMERIC,
        "DPOI" NUMERIC
      );
    `);
    console.log('   ✅ OptionsVolume table created');

    // OptionsOpenInterest table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS "OptionsOpenInterest" (
        "SYMBOL" TEXT,
        "EXP" TEXT,
        "C/P" TEXT,
        "STRIKE" NUMERIC,
        "OI" NUMERIC,
        "DOI" NUMERIC
      );
    `);
    console.log('   ✅ OptionsOpenInterest table created');

    // Prices tables (for price context in queries)
    await pool.query(`
      CREATE TABLE IF NOT EXISTS prices_intraday_1m (
        symbol TEXT,
        ts TIMESTAMP WITH TIME ZONE,
        open NUMERIC,
        high NUMERIC,
        low NUMERIC,
        close NUMERIC,
        volume NUMERIC
      );
    `);
    console.log('   ✅ prices_intraday_1m table created');

    await pool.query(`
      CREATE TABLE IF NOT EXISTS prices_daily (
        symbol TEXT,
        "Date" DATE,
        open NUMERIC,
        high NUMERIC,
        low NUMERIC,
        close NUMERIC,
        volume NUMERIC
      );
    `);
    console.log('   ✅ prices_daily table created');

    // AlertStream_monthly table (for alerts/catalysts)
    await pool.query(`
      CREATE TABLE IF NOT EXISTS "AlertStream_monthly" (
        "date" TEXT,
        "timestamp" TEXT,
        "ticker" TEXT,
        "type" TEXT,
        "message" TEXT,
        "price" NUMERIC,
        "volume" NUMERIC
      );
    `);
    console.log('   ✅ AlertStream_monthly table created');

    // Create indexes for better query performance
    console.log('📊 Creating indexes...');
    
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_optionsflow_symbol 
      ON "OptionsFlow_monthly"("Symbol");
    `);
    
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_optionsflow_createddate 
      ON "OptionsFlow_monthly"("CreatedDate");
    `);
    
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_optionsflow_callput 
      ON "OptionsFlow_monthly"("CallPut");
    `);
    
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_prices_symbol_ts 
      ON prices_intraday_1m(symbol, ts);
    `);
    
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_prices_daily_symbol_date 
      ON prices_daily(symbol, "Date");
    `);
    
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_alertstream_ticker 
      ON "AlertStream_monthly"("ticker");
    `);
    
    console.log('   ✅ Indexes created');

    console.log('\n✅ All tables and indexes created successfully!');

  } catch (error) {
    console.error('❌ Error creating tables:', error.message);
    throw error;
  } finally {
    await pool.end();
  }
}

/**
 * Test connection
 */
async function testConnection() {
  const pool = new Pool({
    host: localDbConfig.host,
    port: localDbConfig.port,
    user: localDbConfig.user,
    password: localDbConfig.password,
    database: localDbConfig.database
  });

  try {
    const result = await pool.query('SELECT NOW()');
    console.log('✅ Database connection successful');
    console.log(`   Connected to: ${localDbConfig.database}@${localDbConfig.host}:${localDbConfig.port}`);
    return true;
  } catch (error) {
    console.error('❌ Database connection failed:', error.message);
    return false;
  } finally {
    await pool.end();
  }
}

/**
 * Main setup function
 */
async function main() {
  console.log('🚀 Setting up local PostgreSQL database...\n');
  console.log('Configuration:');
  console.log(`   Host: ${localDbConfig.host}`);
  console.log(`   Port: ${localDbConfig.port}`);
  console.log(`   User: ${localDbConfig.user}`);
  console.log(`   Database: ${localDbConfig.database}\n`);

  try {
    // Test connection first
    console.log('🔌 Testing connection...');
    const adminPool = new Pool({
      host: localDbConfig.host,
      port: localDbConfig.port,
      user: localDbConfig.user,
      password: localDbConfig.password,
      database: 'postgres'
    });

    try {
      await adminPool.query('SELECT 1');
      console.log('✅ PostgreSQL server is running\n');
    } catch (error) {
      console.error('❌ Cannot connect to PostgreSQL server');
      console.error('\nPlease ensure:');
      console.error('1. PostgreSQL is installed and running');
      console.error('2. Connection details in .env are correct');
      console.error('3. See LOCAL_DB_SETUP.md for installation instructions');
      process.exit(1);
    } finally {
      await adminPool.end();
    }

    // Create database
    await createDatabase();
    console.log('');

    // Create tables
    await createTables();
    console.log('');

    // Test final connection
    await testConnection();
    console.log('\n🎉 Setup complete! You can now import CSV files.');

  } catch (error) {
    console.error('\n❌ Setup failed:', error.message);
    process.exit(1);
  }
}

// Run if called directly
if (import.meta.url === `file://${process.argv[1]}`) {
  main();
}

export { createDatabase, createTables, testConnection };