-- Firebase Configuration table (per project)
CREATE TABLE IF NOT EXISTS firebase_config (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id INTEGER NOT NULL UNIQUE,
  project_id_value TEXT NOT NULL,
  private_key TEXT NOT NULL,
  client_email TEXT NOT NULL,
  is_active INTEGER DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_project_id_firebase_config ON firebase_config(project_id);

-- Trigger to update updated_at for firebase_config
CREATE TRIGGER IF NOT EXISTS update_firebase_config_timestamp AFTER UPDATE ON firebase_config
BEGIN
  UPDATE firebase_config SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;