const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');
const { importCatalogIfEmpty } = require('./importer');

let pool = null;
let isConnected = false;

function getPool() {
  if (pool) return pool;

  let config;
  if (process.env.DATABASE_URL) {
    const match = process.env.DATABASE_URL.match(/^postgres(?:ql)?:\/\/([^:]+):(.*)@([^:/]+)(?::(\d+))?\/(.+)$/);
    if (match) {
      config = {
        user: match[1],
        password: match[2],
        host: match[3],
        port: match[4] ? parseInt(match[4], 10) : 5432,
        database: match[5]
      };
    } else {
      config = { connectionString: process.env.DATABASE_URL };
    }
  } else {
    config = {
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '5432', 10),
      user: process.env.DB_USER || 'postgres',
      password: process.env.DB_PASSWORD || 'postgres',
      database: process.env.DB_NAME || 'keyselector',
    };
  }

  pool = new Pool(config);

  pool.on('error', (err) => {
    console.error('[PostgreSQL] Unexpected pool error:', err.message);
  });

  return pool;
}

async function initDb() {
  try {
    const p = getPool();
    const client = await p.connect();
    isConnected = true;
    console.log('[PostgreSQL] Successfully connected to database.');

    const schemaPath = path.join(__dirname, '../db/schema.sql');
    if (fs.existsSync(schemaPath)) {
      const sql = fs.readFileSync(schemaPath, 'utf8');
      await client.query(sql);
      console.log('[PostgreSQL] Schema and seed data verified/applied.');
    }

    // Automatically seed full 26,000+ track catalog if DB is empty or below threshold
    await importCatalogIfEmpty(client);

    client.release();
  } catch (err) {
    isConnected = false;
    console.warn('[PostgreSQL] Database connection failed:', err.message);
    console.warn('[PostgreSQL] Running with in-memory fallback until DB is available.');
  }
}

// In-memory fallback if Postgres is temporarily down
const memoryTracks = new Map();

async function query(text, params = []) {
  if (!isConnected) {
    return { rows: [] };
  }
  return getPool().query(text, params);
}

function normalizeTrackTitle(str) {
  if (!str) return '';
  return str
    .toLowerCase()
    .replace(/\(.*?\)|\[.*?\]/g, '') // strip (feat. ...), [feat. ...]
    .replace(/ - .*$/g, '')          // strip trailing - Radio Edit, etc.
    .replace(/[^a-z0-9]/g, ' ')
    .replace(/\s+/g, ' ')
    .trim();
}

function normalizeTrackArtist(str) {
  if (!str) return '';
  const first = str.split(/,|&|feat\.|with|vs\./i)[0];
  return first
    .toLowerCase()
    .replace(/[^a-z0-9]/g, ' ')
    .replace(/\s+/g, ' ')
    .trim();
}

// Find track by artist and title (case insensitive, with fuzzy fallback)
async function findTrack(artist, title) {
  if (!artist || !title) return null;

  const cleanArt = artist.trim();
  const cleanTitle = title.trim();

  if (isConnected) {
    try {
      // 1. Exact match
      const res = await getPool().query(
        `SELECT * FROM tracks 
         WHERE LOWER(artist) = LOWER($1) AND LOWER(title) = LOWER($2)
         LIMIT 1`,
        [cleanArt, cleanTitle]
      );
      if (res.rows.length > 0) return res.rows[0];

      // 2. Fuzzy match with stripped/normalized title & primary artist
      const normTitle = normalizeTrackTitle(cleanTitle);
      const normArt = normalizeTrackArtist(cleanArt);

      if (normTitle.length >= 2 && normArt.length >= 2) {
        const fuzzyRes = await getPool().query(
          `SELECT * FROM tracks 
           WHERE (LOWER(artist) LIKE $1 OR LOWER(artist) LIKE $2)
             AND (LOWER(title) LIKE $3 OR LOWER(title) LIKE $4)
           ORDER BY is_corrected DESC, play_count DESC
           LIMIT 1`,
          [`%${normArt}%`, `${normArt}%`, `%${normTitle}%`, `${normTitle}%`]
        );
        if (fuzzyRes.rows.length > 0) return fuzzyRes.rows[0];
      }
    } catch (e) {
      console.error('[DB] findTrack error:', e.message);
    }
  }

  // Memory fallback
  const exactKey = `${cleanArt.toLowerCase()} - ${cleanTitle.toLowerCase()}`;
  if (memoryTracks.has(exactKey)) return memoryTracks.get(exactKey);

  const normTitle = normalizeTrackTitle(cleanTitle);
  const normArt = normalizeTrackArtist(cleanArt);
  for (const track of memoryTracks.values()) {
    const tNormTitle = normalizeTrackTitle(track.title);
    const tNormArt = normalizeTrackArtist(track.artist);
    if (tNormTitle.includes(normTitle) || normTitle.includes(tNormTitle)) {
      if (tNormArt.includes(normArt) || normArt.includes(tNormArt)) {
        return track;
      }
    }
  }

  return null;
}

// Save or update track
async function saveTrack({ title, artist, camelot_key, bpm, music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, tags = [], is_corrected = false, source = 'manual' }) {
  if (!title || !artist || !camelot_key || !bpm) return null;

  if (isConnected) {
    try {
      const res = await getPool().query(
        `INSERT INTO tracks (title, artist, camelot_key, bpm, music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, tags, is_corrected, source, play_count, updated_at)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, 1, CURRENT_TIMESTAMP)
         ON CONFLICT (artist, title) DO UPDATE 
         SET play_count = tracks.play_count + 1,
             cover_url = COALESCE(EXCLUDED.cover_url, tracks.cover_url),
             preview_url = COALESCE(EXCLUDED.preview_url, tracks.preview_url),
             duration_sec = COALESCE(EXCLUDED.duration_sec, tracks.duration_sec),
             energy = COALESCE(EXCLUDED.energy, tracks.energy),
             danceability = COALESCE(EXCLUDED.danceability, tracks.danceability),
             happiness = COALESCE(EXCLUDED.happiness, tracks.happiness),
             camelot_key = CASE WHEN tracks.is_corrected THEN tracks.camelot_key ELSE EXCLUDED.camelot_key END,
             bpm = CASE WHEN tracks.is_corrected THEN tracks.bpm ELSE EXCLUDED.bpm END,
             music_key = CASE WHEN tracks.is_corrected THEN tracks.music_key ELSE EXCLUDED.music_key END,
             updated_at = CURRENT_TIMESTAMP
         RETURNING *`,
        [title.trim(), artist.trim(), camelot_key, Math.round(bpm), music_key, cover_url, preview_url, duration_sec || null, energy || null, danceability || null, happiness || null, tags, is_corrected, source]
      );
      return res.rows[0];
    } catch (e) {
      console.error('[DB] saveTrack error:', e.message);
    }
  }

  const track = { title, artist, camelot_key, bpm: Math.round(bpm), music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, tags, is_corrected, source };
  const key = `${artist.trim().toLowerCase()} - ${title.trim().toLowerCase()}`;
  memoryTracks.set(key, track);
  return track;
}

// Save explicit user correction
async function correctTrack({ artist, title, camelot_key, bpm, music_key }) {
  if (!artist || !title || !camelot_key || !bpm) return null;

  if (isConnected) {
    try {
      const res = await getPool().query(
        `INSERT INTO tracks (title, artist, camelot_key, bpm, music_key, is_corrected, updated_at)
         VALUES ($1, $2, $3, $4, $5, TRUE, CURRENT_TIMESTAMP)
         ON CONFLICT (artist, title) DO UPDATE 
         SET camelot_key = EXCLUDED.camelot_key,
             bpm = EXCLUDED.bpm,
             music_key = EXCLUDED.music_key,
             is_corrected = TRUE,
             updated_at = CURRENT_TIMESTAMP
         RETURNING *`,
        [title.trim(), artist.trim(), camelot_key, Math.round(bpm), music_key]
      );
      return res.rows[0];
    } catch (e) {
      console.error('[DB] correctTrack error:', e.message);
    }
  }

  const existing = await findTrack(artist, title) || { title, artist };
  existing.camelot_key = camelot_key;
  existing.bpm = Math.round(bpm);
  existing.music_key = music_key;
  existing.is_corrected = true;
  const key = `${artist.trim().toLowerCase()} - ${title.trim().toLowerCase()}`;
  memoryTracks.set(key, existing);
  return existing;
}

// Get suggestions matching compatible camelot keys and BPM range
async function getSuggestions(compatibleKeys = [], minBpm = 80, maxBpm = 180, limit = 10, excludeArtist = '', excludeTitle = '') {
  if (isConnected) {
    try {
      const res = await getPool().query(
        `SELECT id, title, artist, camelot_key, bpm, music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, tags, play_count
         FROM tracks
         WHERE camelot_key = ANY($1)
           AND bpm BETWEEN $2 AND $3
           AND NOT (LOWER(artist) = LOWER($4) AND LOWER(title) = LOWER($5))
         ORDER BY play_count DESC, id DESC
         LIMIT $6`,
        [compatibleKeys, Math.round(minBpm), Math.round(maxBpm), excludeArtist, excludeTitle, limit]
      );
      return res.rows;
    } catch (e) {
      console.error('[DB] getSuggestions error:', e.message);
    }
  }

  // Fallback search in memory
  const results = [];
  for (const track of memoryTracks.values()) {
    if (compatibleKeys.includes(track.camelot_key) && track.bpm >= minBpm && track.bpm <= maxBpm) {
      if (excludeArtist && track.artist.toLowerCase() === excludeArtist.toLowerCase()) continue;
      results.push(track);
      if (results.length >= limit) break;
    }
  }
  return results;
}

// Search tracks by query
async function searchTracks(queryStr, limit = 10) {
  if (!queryStr) return [];
  const term = `%${queryStr.trim().toLowerCase()}%`;

  if (isConnected) {
    try {
      const res = await getPool().query(
        `SELECT id, title, artist, camelot_key, bpm, music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, is_corrected
         FROM tracks
         WHERE LOWER(title) LIKE $1 
            OR LOWER(artist) LIKE $1
            OR LOWER(artist || ' ' || title) LIKE $1
            OR LOWER(title || ' ' || artist) LIKE $1
         ORDER BY is_corrected DESC, play_count DESC
         LIMIT $2`,
        [term, limit]
      );
      return res.rows;
    } catch (e) {
      console.error('[DB] searchTracks error:', e.message);
    }
  }

  const results = [];
  const q = queryStr.toLowerCase();
  for (const track of memoryTracks.values()) {
    const combined1 = `${track.artist} ${track.title}`.toLowerCase();
    const combined2 = `${track.title} ${track.artist}`.toLowerCase();
    if (track.title.toLowerCase().includes(q) || track.artist.toLowerCase().includes(q) || combined1.includes(q) || combined2.includes(q)) {
      results.push(track);
      if (results.length >= limit) break;
    }
  }
  return results;
}

module.exports = {
  initDb,
  query,
  findTrack,
  saveTrack,
  correctTrack,
  getSuggestions,
  searchTracks,
  isDbConnected: () => isConnected
};