db.js

import sqlite3 from 'sqlite3';
sqlite3.verbose(); // Enable verbose mode for debugging

// Function to connect to the SQLite database
export function connectToDatabase(dbFilePath = './store.db') {
  return new sqlite3.Database(dbFilePath, (err) => {
    if (err) {
      console.error('Error connecting to SQLite database:', err.message);
    } else {
      console.log(`Connected to SQLite database at ${dbFilePath}`);
    }
  });
}

// Function to create the 'news' table if it doesn't exist
export function createNewsTable(db) {
  const createTableQuery = `
    CREATE TABLE IF NOT EXISTS news (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      url TEXT UNIQUE NOT NULL,
      content TEXT NOT NULL,
      datetime DATETIME DEFAULT CURRENT_TIMESTAMP,
      author TEXT NOT NULL,
      editor TEXT,
      report TEXT
    );
  `;

  db.run(createTableQuery, (err) => {
    if (err) {
      console.error('Error creating table:', err.message);
    } else {
      console.log('Table "news" created or already exists.');
    }
  });
}

export function createWhaleFeedTable(db) {
  const createTableQuery = `
    CREATE TABLE IF NOT EXISTS whale_feed (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      contract TEXT NOT NULL,
      action TEXT NOT NULL,
      name TEXT NOT NULL,
      symbol TEXT NOT NULL,
      url TEXT UNIQUE NOT NULL,
      usd TEXT NOT NULL,
      price TEXT NOT NULL,
      volume TEXT NOT NULL,
      liquidity TEXT NOT NULL,
      marketcap TEXT NOT NULL,
      datetime DATETIME DEFAULT CURRENT_TIMESTAMP
    );
  `;

  db.run(createTableQuery, (err) => {
    if (err) {
      console.error('Error creating table:', err.message);
    } else {
      console.log('Table "whale_feed" created or already exists.');

      db.serialize(() => {
        // Check if the `action` column exists in the `whale_feed` table
        db.all(`PRAGMA table_info(whale_feed)`, (err, rows) => {
          if (err) {
            console.error("Error checking table info:", err.message);
            return;
          }

          // Check for the `action` column in the table schema
          const columnExists = rows.some((row) => row.name === 'action');

          if (!columnExists) {
            // Add the `action` column since it doesn't exist
            db.run(`ALTER TABLE whale_feed ADD COLUMN action TEXT`, (alterErr) => {
              if (alterErr) {
                console.error("Error adding column:", alterErr.message);
              } else {
                console.log("Column 'action' successfully added to 'whale_feed' table.");
              }
            });
          } else {
            console.log("Column 'action' already exists in 'whale_feed' table.");
          }
        });
      });

    }
  });
}

// Function to insert a record into the 'news' table
export function insertNewsRecord(db, title, url, datetime, content, author, editor = null, report = null) {
  const insertQuery = `
    INSERT INTO news (title, url, datetime, content, author, editor, report)
    VALUES (?, ?, ?, ?, ?, ?, ?);
  `;

  db.run(insertQuery, [title, url, datetime, content, author, editor, report], function (err) {
    if (err) {
      console.error('Error inserting news record:', err.message);
    } else {
      console.log(`News record inserted successfully with ID: ${this.lastID}`);
    }
  });
}

// Function to insert a record into the 'news' table
export function insertWhaleFeedRecord(db, contract, action, name, symbol, url, usd, price, volume, liquidity, marketcap, time) {
  const insertQuery = `
    INSERT INTO whale_feed (contract, action, name, symbol, url, usd, price, volume, liquidity, marketcap, datetime)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
  `;

  db.run(insertQuery, [contract, action, name, symbol, url, usd, price, volume, liquidity, marketcap, time], function (err) {
    if (err) {
      console.error('Error inserting whale feed record:', err.message);
    } else {
      console.log(`Whale feed record inserted successfully with ID: ${this.lastID}`);
    }
  });
}

// Function to read all records from the 'news' table
export function readAllNewsRecords(db) {
  const selectAllQuery = `
    SELECT * FROM news;
  `;

  db.all(selectAllQuery, [], (err, rows) => {
    if (err) {
      console.error('Error reading all news records:', err.message);
    } else {
      console.log('All news records:');
      console.table(rows); // Nicely format the output as a table
    }
  });
}

// Function to read a specific record by title
export function readNewsByTitle(db, title) {
  const selectByTitleQuery = `
    SELECT * FROM news
    WHERE title = ?;
  `;

  db.get(selectByTitleQuery, [title], (err, row) => {
    if (err) {
      console.error('Error reading news by title:', err.message);
    } else if (row) {
      console.log('News record found:');
      console.table(row);
    } else {
      console.log(`No news record found with title: "${title}"`);
    }
  });
}

export function readNewsByUrl(db, url, foundCallback, notFoundCallback) {
  const selectByUrlQuery = `
    SELECT * FROM news
    WHERE url = ?;
  `;

  db.get(selectByUrlQuery, [url], (err, row) => {
    if (err) {
      console.error('Error reading news by url:', err.message);
    } else if (row) {
      //   console.log('News record found:', row);
      if (foundCallback) {
        foundCallback(row);
      }
    } else {
      //   console.log(`No news record found with url: "${url}"`);
      if (notFoundCallback) {
        notFoundCallback();
      }
    }
  });
}

function migrateAndTestNewsTable(db) {
  // Step 1: Create the 'news' table
  createNewsTable(db);

  return;

  // Step 2: Insert sample news records
  const sampleNews = [
    {
      title: 'Breaking News: JavaScript Takes Over the World',
      url: 'https://example.com/javascript-news',
      datetime: '2025-01-23 05:26:21 CST',
      content: 'JavaScript has become the most popular language ever.',
      author: 'John Doe',
      editor: 'Jane Smith',
      report: 'Verified by the Editorial Team'
    },
    {
      title: 'SQLite: The Lightweight Database That Could',
      url: 'https://example.com/sqlite-database',
      datetime: '2025-01-23 05:26:21 CST',
      content: 'SQLite continues to power small and large applications alike.',
      author: 'Alice Johnson',
      editor: 'Bob Brown',
      report: 'Reviewed and approved'
    }
  ];

  sampleNews.forEach((news) => {
    insertNewsRecord(
      db,
      news.title,
      news.url,
      news.datetime,
      news.content,
      news.author,
      news.editor,
      news.report
    );
  });

  // Step 3: Read all records
  setTimeout(() => {
    readAllNewsRecords(db);

    // Step 4: Read a specific record by title
    readNewsByTitle(db, 'Breaking News: JavaScript Takes Over the World');
    readNewsByUrl(db, 'https://example.com/javascript-news');
  }, 1000); // Add a slight delay to ensure inserts are complete
}

function migrateWhaleFeedTable(db) {
  createWhaleFeedTable(db);
}

// Example usage:
export function main() {
  const db = connectToDatabase('./store.db'); // Connect to the database

  migrateAndTestNewsTable(db);
  migrateWhaleFeedTable(db);

  // Close the database connection when done
  setTimeout(() => {
    db.close((err) => {
      if (err) {
        console.error('Error closing database:', err.message);
      } else {
        console.log('Database connection closed.');
      }
    });
  }, 3000); // Wait for all queries to complete
}

// Run the example
// main();