-- Add 'archived' status to notifications table
-- Note: SQLite doesn't support ALTER TABLE MODIFY COLUMN for CHECK constraints
-- We need to recreate the table with the new status value

-- Create new table with archived status
CREATE TABLE IF NOT EXISTS notifications_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  data TEXT,
  target_type TEXT NOT NULL CHECK(target_type IN ('all', 'groups', 'users')),
  target_ids TEXT,
  status TEXT DEFAULT 'pending' CHECK(status IN ('draft', 'pending', 'sending', 'sent', 'failed', 'cancelled', 'archived')),
  scheduled_at DATETIME NULL,
  sent_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  image_url TEXT NULL,
  link TEXT NULL,
  deep_link TEXT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Copy data from old table
INSERT INTO notifications_new SELECT * FROM notifications;

-- Drop old table
DROP TABLE notifications;

-- Rename new table
ALTER TABLE notifications_new RENAME TO notifications;

-- Recreate indexes
CREATE INDEX IF NOT EXISTS idx_project_id_notifications ON notifications(project_id);
CREATE INDEX IF NOT EXISTS idx_status ON notifications(status);
CREATE INDEX IF NOT EXISTS idx_scheduled_at ON notifications(scheduled_at);