const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.resolve(__dirname, 'cinematch.db');
const db = new sqlite3.Database(dbPath);

db.serialize(() => {
    db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE,
      password TEXT,
      code TEXT UNIQUE NOT NULL,
      partner_code TEXT,
      genres TEXT
    )
  `);

    // Add genres column if it doesn't exist (for existing db)
    db.run(`ALTER TABLE users ADD COLUMN genres TEXT`, (err) => {
        // Ignore error if column already exists
    });

    db.run(`
    CREATE TABLE IF NOT EXISTS swipes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      movie_id INTEGER,
      direction TEXT,
      FOREIGN KEY(user_id) REFERENCES users(id),
      UNIQUE(user_id, movie_id)
    )
  `);

    db.run(`
    CREATE TABLE IF NOT EXISTS watched (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      movie_id INTEGER,
      FOREIGN KEY(user_id) REFERENCES users(id),
      UNIQUE(user_id, movie_id)
    )
  `)
});

module.exports = db;