PRAGMA foreign_keys=ON;
CREATE TABLE IF NOT EXISTS users(
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL DEFAULT '',
  google_id TEXT UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS movies(
  id INTEGER PRIMARY KEY,
  tmdb_id INTEGER UNIQUE,
  title TEXT NOT NULL,
  year INTEGER,
  runtime INTEGER,
  poster_url TEXT,
  genres TEXT,
  plot TEXT,
  release_date TEXT,
  watch_date TEXT,
  watched INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS rsvps(
  movie_id INTEGER NOT NULL,
  user_id TEXT NOT NULL,
  status TEXT NOT NULL CHECK(status IN ('yes','maybe','no')),
  proposed_date TEXT,
  PRIMARY KEY(movie_id,user_id),
  FOREIGN KEY(movie_id) REFERENCES movies(id) ON DELETE CASCADE,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS ratings(
  movie_id INTEGER NOT NULL,
  user_id TEXT NOT NULL,
  score REAL NOT NULL CHECK(score>=0 AND score<=10),
  review TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY(movie_id,user_id),
  FOREIGN KEY(movie_id) REFERENCES movies(id) ON DELETE CASCADE,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_movies_dates ON movies(watch_date, release_date);
CREATE INDEX IF NOT EXISTS idx_rsvps_movie ON rsvps(movie_id);
CREATE INDEX IF NOT EXISTS idx_ratings_movie ON ratings(movie_id);
