import { Low } from 'lowdb';
import { JSONFile } from 'lowdb/node';
import path from 'path';
import { fileURLToPath } from 'url';
import fs from 'fs';

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

// Get database path from environment or use default
const dbPath = process.env.DATABASE_PATH || path.join(__dirname, '../database/exchange.json');

// Ensure database directory exists
const dbDir = path.dirname(dbPath);
if (!fs.existsSync(dbDir)) {
  fs.mkdirSync(dbDir, { recursive: true });
}

// Default database structure
const defaultData = {
  users: [],
  exchange_requests: [],
  exchange_rates: [],
  password_reset_tokens: [],
  _meta: {
    lastId: {
      users: 0,
      exchange_requests: 0,
      exchange_rates: 0,
      password_reset_tokens: 0
    }
  }
};

// Create database connection
const adapter = new JSONFile(dbPath);
const db = new Low(adapter, defaultData);

// Read data from JSON file, this will set db.data content
await db.read();

// If file doesn't exist or is empty, db.data will be null
// In this case, set default data
if (!db.data) {
  db.data = defaultData;
  await db.write();
}

if (process.env.NODE_ENV === 'production') {
  console.log('Database connected successfully');
} else {
  console.log('Connected to LowDB JSON database at:', dbPath);
}

// Helper function to get next ID for a table
const getNextId = (tableName) => {
  db.data._meta.lastId[tableName] += 1;
  return db.data._meta.lastId[tableName];
};

// Wrapper functions for database operations to maintain SQL-like interface
export const dbRun = async (sql, params = []) => {
  try {
    const lowerSql = sql.toLowerCase().trim();
    
    if (lowerSql.startsWith('insert into')) {
      // Parse INSERT statement
      const tableMatch = lowerSql.match(/insert into (\w+)/);
      if (!tableMatch) throw new Error('Invalid INSERT statement');
      
      const tableName = tableMatch[1];
      
      // Parse VALUES
      const valuesMatch = sql.match(/values\s*\((.*)\)/i);
      if (!valuesMatch) throw new Error('No VALUES clause found');
      
      // Parse column names  
      const columnsMatch = sql.match(/\(([^)]+)\)\s+values/i);
      if (!columnsMatch) throw new Error('No columns specified');
      
      const columns = columnsMatch[1].split(',').map(col => col.trim());
      
      // Create record object
      const record = {};
      
      // Add ID if not provided
      if (!columns.includes('id')) {
        record.id = getNextId(tableName);
      }
      
      // Map params to columns
      columns.forEach((col, index) => {
        if (index < params.length) {
          record[col] = params[index];
        }
      });
      
      // Add timestamps
      const now = new Date().toISOString();
      if (!record.created_at) record.created_at = now;
      if (!record.updated_at) record.updated_at = now;
      
      // Add to table
      db.data[tableName].push(record);
      await db.write();
      
      return { lastID: record.id, changes: 1 };
      
    } else if (lowerSql.startsWith('update')) {
      // Parse UPDATE statement
      const tableMatch = lowerSql.match(/update (\w+)/);
      if (!tableMatch) throw new Error('Invalid UPDATE statement');
      
      const tableName = tableMatch[1];
      
      // Parse SET clause
      const setMatch = sql.match(/set\s+(.+?)\s+where/i);
      if (!setMatch) throw new Error('No SET clause found');
      
      // Parse WHERE clause
      const whereMatch = sql.match(/where\s+(.+)$/i);
      if (!whereMatch) throw new Error('No WHERE clause found');
      
      const whereClause = whereMatch[1];
      
      // Handle different WHERE conditions
      let record = null;
      if (whereClause.includes('id = ?')) {
        const id = params[params.length - 1];
        record = db.data[tableName].find(item => item.id == id);
      } else if (whereClause.includes('currency_pair = ?')) {
        const currencyPair = params[params.length - 1];
        record = db.data[tableName].find(item => item.currency_pair === currencyPair);
      }
      
      if (record) {
        // Parse SET assignments
        const assignments = setMatch[1].split(',');
        let paramIndex = 0;
        
        assignments.forEach(assignment => {
          const [column, value] = assignment.split('=').map(s => s.trim());
          if (value === '?') {
            record[column] = params[paramIndex++];
          } else if (value === 'CURRENT_TIMESTAMP') {
            record[column] = new Date().toISOString();
          }
        });
        
        // Don't override updated_at if it was explicitly set in the query
        if (!setMatch[1].includes('updated_at')) {
          record.updated_at = new Date().toISOString();
        }
        
        await db.write();
        return { changes: 1 };
      }
      
      return { changes: 0 };
      
    } else if (lowerSql.startsWith('delete')) {
      // Parse DELETE statement
      const tableMatch = lowerSql.match(/delete from (\w+)/);
      if (!tableMatch) throw new Error('Invalid DELETE statement');
      
      const tableName = tableMatch[1];
      
      // Parse WHERE clause
      const whereMatch = sql.match(/where\s+(.+)$/i);
      if (!whereMatch) throw new Error('No WHERE clause found');
      
      const whereClause = whereMatch[1];
      
      // Simple WHERE parsing for id = ?
      if (whereClause.includes('id = ?')) {
        const id = params[0];
        const index = db.data[tableName].findIndex(item => item.id == id);
        
        if (index !== -1) {
          db.data[tableName].splice(index, 1);
          await db.write();
          return { changes: 1 };
        }
      }
      
      return { changes: 0 };
    }
    
    throw new Error('Unsupported SQL operation');
    
  } catch (error) {
    throw error;
  }
};

export const dbGet = async (sql, params = []) => {
  try {
    const lowerSql = sql.toLowerCase().trim();
    
    if (lowerSql.startsWith('select')) {
      // Parse SELECT statement
      const tableMatch = lowerSql.match(/from (\w+)/);
      if (!tableMatch) throw new Error('Invalid SELECT statement');
      
      const tableName = tableMatch[1];
      let records = db.data[tableName] || [];
      
      // Parse WHERE clause
      const whereMatch = sql.match(/where\s+(.+?)(?:\s+order|\s+limit|$)/i);
      
      if (whereMatch) {
        const whereClause = whereMatch[1];
        
        // Handle different WHERE conditions
        if (whereClause.includes('username = ?')) {
          records = records.filter(item => item.username === params[0]);
        } else if (whereClause.includes('id = ?')) {
          records = records.filter(item => item.id == params[0]);
        } else if (whereClause.includes('token = ?')) {
          records = records.filter(item => item.token === params[0]);
        } else if (whereClause.includes('currency_pair = ?')) {
          records = records.filter(item => item.currency_pair === params[0]);
        }
      }
      
      // Handle COUNT queries
      if (lowerSql.includes('count(*)')) {
        return { total: records.length };
      }
      
      // Return first record for GET
      return records[0] || null;
    }
    
    return null;
  } catch (error) {
    throw error;
  }
};

export const dbAll = async (sql, params = []) => {
  try {
    const lowerSql = sql.toLowerCase().trim();
    
    if (lowerSql.startsWith('select')) {
      // Parse SELECT statement
      const tableMatch = lowerSql.match(/from (\w+)/);
      if (!tableMatch) throw new Error('Invalid SELECT statement');
      
      const tableName = tableMatch[1];
      
      // Ensure db.data exists and table exists
      if (!db.data || !db.data[tableName]) {
        console.warn(`Table ${tableName} does not exist, returning empty array`);
        return [];
      }
      
      let records = Array.isArray(db.data[tableName]) ? [...db.data[tableName]] : [];
      
      // Parse WHERE clause
      const whereMatch = sql.match(/where\s+(.+?)(?:\s+order|\s+limit|$)/i);
      
      if (whereMatch) {
        const whereClause = whereMatch[1];
        
        // Handle different WHERE conditions
        if (whereClause.includes('status = ?')) {
          records = records.filter(item => item && item.status === params[0]);
        } else if (whereClause.includes('user_id = ?')) {
          records = records.filter(item => item && item.user_id == params[0]);
        }
      }
      
      // Parse ORDER BY
      const orderMatch = sql.match(/order by\s+(\w+)(?:\s+(asc|desc))?/i);
      if (orderMatch) {
        const column = orderMatch[1];
        const direction = orderMatch[2] ? orderMatch[2].toLowerCase() : 'asc';
        
        records.sort((a, b) => {
          if (!a || !b) return 0;
          if (direction === 'desc') {
            return new Date(b[column]) - new Date(a[column]);
          }
          return new Date(a[column]) - new Date(b[column]);
        });
      }
      
      // Parse LIMIT and OFFSET
      const limitMatch = sql.match(/limit\s+(\d+)(?:\s+offset\s+(\d+))?/i);
      if (limitMatch) {
        const limit = parseInt(limitMatch[1]);
        const offset = limitMatch[2] ? parseInt(limitMatch[2]) : 0;
        records = records.slice(offset, offset + limit);
      }
      
      // Always return an array
      return Array.isArray(records) ? records : [];
    }
    
    return [];
  } catch (error) {
    if (process.env.NODE_ENV !== 'production') {
      console.error('Database query error:', error);
    }
    return [];
  }
};

// Initialize database schema (no-op for JSON, but maintain compatibility)
export async function initializeDatabase() {
  try {
    // Ensure all required tables exist in the data structure
    if (!db.data.users) db.data.users = [];
    if (!db.data.exchange_requests) db.data.exchange_requests = [];
    if (!db.data.exchange_rates) db.data.exchange_rates = [];
    if (!db.data.password_reset_tokens) db.data.password_reset_tokens = [];
    
    if (!db.data._meta) {
      db.data._meta = {
        lastId: {
          users: 0,
          exchange_requests: 0,
          exchange_rates: 0,
          password_reset_tokens: 0
        }
      };
    }
    
    await db.write();
    
    if (process.env.NODE_ENV !== 'production') {
      console.log('Database schema initialized successfully');
    }
    return true;
  } catch (error) {
    console.error('Error initializing database:', error.message);
    throw error;
  }
}

export default db;
