-- Create project_members table for MySQL
CREATE TABLE IF NOT EXISTS project_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  user_id INT NOT NULL,
  role VARCHAR(50) NOT NULL CHECK(role IN ('Admin', 'Marketer')),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_project_user (project_id, user_id)
) CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE INDEX idx_project_id_members ON project_members(project_id);
CREATE INDEX idx_user_id_members ON project_members(user_id);