import os
import sqlite3
from pathlib import Path

DB_PATH = Path("data/sms.db")

SCHEMA_SQL = """
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

CREATE TABLE IF NOT EXISTS messages (
  id INTEGER PRIMARY KEY,
  address TEXT,
  date_ms INTEGER,
  type INTEGER,
  body TEXT,
  thread_id INTEGER,
  service TEXT,
  raw_json TEXT
);

CREATE INDEX IF NOT EXISTS idx_messages_date ON messages(date_ms);
CREATE INDEX IF NOT EXISTS idx_messages_address ON messages(address);
CREATE INDEX IF NOT EXISTS idx_messages_thread ON messages(thread_id);
CREATE INDEX IF NOT EXISTS idx_messages_type ON messages(type);

-- FTS5 index for fast search + relevance
CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts
USING fts5(body, content='messages', content_rowid='id');

CREATE TRIGGER IF NOT EXISTS messages_ai AFTER INSERT ON messages BEGIN
  INSERT INTO messages_fts(rowid, body) VALUES (new.id, new.body);
END;

CREATE TRIGGER IF NOT EXISTS messages_ad AFTER DELETE ON messages BEGIN
  INSERT INTO messages_fts(messages_fts, rowid, body) VALUES('delete', old.id, old.body);
END;

CREATE TRIGGER IF NOT EXISTS messages_au AFTER UPDATE ON messages BEGIN
  INSERT INTO messages_fts(messages_fts, rowid, body) VALUES('delete', old.id, old.body);
  INSERT INTO messages_fts(rowid, body) VALUES (new.id, new.body);
END;

CREATE TABLE IF NOT EXISTS search_history (
  id INTEGER PRIMARY KEY,
  created_at_ms INTEGER,
  mode TEXT,
  query TEXT,
  filters_json TEXT
);

CREATE TABLE IF NOT EXISTS saved_searches (
  id INTEGER PRIMARY KEY,
  name TEXT,
  created_at_ms INTEGER,
  mode TEXT,
  query TEXT,
  filters_json TEXT
);

CREATE TABLE IF NOT EXISTS bookmarks (
  id INTEGER PRIMARY KEY,
  created_at_ms INTEGER,
  message_id INTEGER UNIQUE,
  note TEXT,
  FOREIGN KEY(message_id) REFERENCES messages(id)
);

CREATE TABLE IF NOT EXISTS tags (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS bookmark_tags (
  bookmark_id INTEGER,
  tag_id INTEGER,
  PRIMARY KEY(bookmark_id, tag_id),
  FOREIGN KEY(bookmark_id) REFERENCES bookmarks(id),
  FOREIGN KEY(tag_id) REFERENCES tags(id)
);
"""

def connect():
  DB_PATH.parent.mkdir(parents=True, exist_ok=True)
  conn = sqlite3.connect(DB_PATH, check_same_thread=False)
  conn.row_factory = sqlite3.Row
  return conn

def init_db():
  conn = connect()
  conn.executescript(SCHEMA_SQL)
  conn.commit()
  conn.close()