-- FastDesk SQLite Schema
-- Migrated from Lovable.app Supabase

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Main cards table (from "links" export)
CREATE TABLE IF NOT EXISTS cards (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  label TEXT NOT NULL,
  content TEXT,
  emoji TEXT,
  visibility TEXT DEFAULT 'private', -- 'private', 'shared', etc.
  enabled BOOLEAN DEFAULT 1,
  position INTEGER,
  storage_path TEXT,
  created_at DATETIME,
  updated_at DATETIME,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Keywords as separate table for full-text search efficiency
CREATE TABLE IF NOT EXISTS keywords (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  card_id TEXT NOT NULL,
  keyword TEXT NOT NULL,
  FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE,
  UNIQUE(card_id, keyword)
);

-- Items table (nested content within cards)
CREATE TABLE IF NOT EXISTS items (
  id TEXT PRIMARY KEY,
  card_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  type TEXT NOT NULL, -- 'link', 'note', 'media_carousel', 'timeline', 'minipost', etc.
  label TEXT NOT NULL,
  url TEXT,
  content TEXT,
  position INTEGER,
  media_url TEXT, -- Keep Supabase URLs for now
  media_type TEXT, -- 'image', 'video', etc.
  media_file_name TEXT,
  storage_path TEXT,
  thumbnail_url TEXT,
  created_at DATETIME,
  updated_at DATETIME,
  created_device TEXT,
  FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Media files table (for future NAS migration)
CREATE TABLE IF NOT EXISTS media_files (
  id TEXT PRIMARY KEY,
  item_id TEXT,
  card_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  original_url TEXT, -- Original Supabase URL
  local_path TEXT, -- Will be populated during Phase 2 migration
  file_name TEXT,
  storage_path TEXT,
  mime_type TEXT,
  file_size INTEGER,
  downloaded BOOLEAN DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE SET NULL,
  FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Indexes for search performance
CREATE INDEX IF NOT EXISTS idx_cards_user_id ON cards(user_id);
CREATE INDEX IF NOT EXISTS idx_cards_enabled ON cards(enabled);
CREATE INDEX IF NOT EXISTS idx_cards_label ON cards(label);
CREATE INDEX IF NOT EXISTS idx_cards_updated_at ON cards(updated_at);

CREATE INDEX IF NOT EXISTS idx_keywords_card_id ON keywords(card_id);
CREATE INDEX IF NOT EXISTS idx_keywords_keyword ON keywords(keyword);

CREATE INDEX IF NOT EXISTS idx_items_card_id ON items(card_id);
CREATE INDEX IF NOT EXISTS idx_items_user_id ON items(user_id);
CREATE INDEX IF NOT EXISTS idx_items_type ON items(type);
CREATE INDEX IF NOT EXISTS idx_items_url ON items(url);

CREATE INDEX IF NOT EXISTS idx_media_card_id ON media_files(card_id);
CREATE INDEX IF NOT EXISTS idx_media_downloaded ON media_files(downloaded);

-- Full-text search virtual table for cards + keywords + items
CREATE VIRTUAL TABLE IF NOT EXISTS cards_fts USING fts5(
  label,
  content,
  keywords,
  item_labels,
  card_id UNINDEXED,
  content=cards,
  content_rowid=rowid
);

-- Triggers to keep FTS index updated
CREATE TRIGGER IF NOT EXISTS cards_fts_insert AFTER INSERT ON cards BEGIN
  INSERT INTO cards_fts(rowid, label, content, keywords, item_labels, card_id)
  VALUES (
    new.rowid,
    new.label,
    new.content,
    COALESCE((SELECT GROUP_CONCAT(keyword, ' ') FROM keywords WHERE card_id = new.id), ''),
    '',
    new.id
  );
END;

CREATE TRIGGER IF NOT EXISTS cards_fts_delete AFTER DELETE ON cards BEGIN
  DELETE FROM cards_fts WHERE card_id = old.id;
END;

CREATE TRIGGER IF NOT EXISTS keywords_fts_update AFTER INSERT ON keywords BEGIN
  UPDATE cards_fts 
  SET keywords = (SELECT GROUP_CONCAT(keyword, ' ') FROM keywords WHERE card_id = new.card_id)
  WHERE card_id = new.card_id;
END;
