-- 1. Create FCM tokens table
CREATE TABLE IF NOT EXISTS fcm_tokens (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  member_id INTEGER NOT NULL,
  token TEXT NOT NULL,
  device TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (member_id) REFERENCES mobile_users(id) ON DELETE CASCADE,
  UNIQUE(member_id, token)
);

CREATE INDEX IF NOT EXISTS idx_fcm_tokens_member_id ON fcm_tokens(member_id);
CREATE INDEX IF NOT EXISTS idx_fcm_tokens_token ON fcm_tokens(token);

-- 2. Migrate existing tokens from mobile_users to fcm_tokens
-- Note: This will only work if fcm_token column exists in mobile_users
-- If the column doesn't exist, this statement will be skipped by the migration handler
INSERT INTO fcm_tokens (member_id, token, device)
SELECT id, fcm_token, 'Unknown'
FROM mobile_users
WHERE fcm_token IS NOT NULL AND fcm_token != '';

-- 3. Create a temporary table to store mobile_user data without fcm_token
CREATE TABLE mobile_users_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id INTEGER NOT NULL,
  user_identifier TEXT NOT NULL,
  metadata TEXT,
  email TEXT,
  dob DATE,
  city TEXT,
  country TEXT,
  gender TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  UNIQUE(project_id, user_identifier)
);

-- 4. Copy data from the old mobile_users table to the new one
INSERT INTO mobile_users_new (id, project_id, user_identifier, metadata, email, dob, city, country, gender, created_at, updated_at)
SELECT id, project_id, user_identifier, metadata, email, dob, city, country, gender, created_at, updated_at
FROM mobile_users;

-- 5. Drop the old triggers and indexes that depend on the old table
DROP TRIGGER IF EXISTS update_mobile_users_timestamp;
DROP INDEX IF EXISTS idx_project_id_mobile_users;
DROP INDEX IF EXISTS idx_mobile_users_email;

-- 6. Disable foreign key checks temporarily to allow dropping and recreating tables
PRAGMA foreign_keys = OFF;

-- 7. Drop the old table and rename the new one
DROP TABLE mobile_users;
ALTER TABLE mobile_users_new RENAME TO mobile_users;

-- 8. Re-enable foreign key checks
PRAGMA foreign_keys = ON;

-- 9. Re-create triggers and indexes for the new table
CREATE INDEX IF NOT EXISTS idx_project_id_mobile_users ON mobile_users(project_id);
CREATE INDEX IF NOT EXISTS idx_mobile_users_email ON mobile_users(email);

CREATE TRIGGER IF NOT EXISTS update_mobile_users_timestamp AFTER UPDATE ON mobile_users
BEGIN
  UPDATE mobile_users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_fcm_tokens_timestamp AFTER UPDATE ON fcm_tokens
BEGIN
  UPDATE fcm_tokens SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;