CREATE TABLE IF NOT EXISTS teams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'creating',
  communication_mode VARCHAR(50) NOT NULL DEFAULT 'leader_only',
  redis_url_secret_name VARCHAR(255) NULL,
  redis_url_secret_key VARCHAR(255) NULL,
  team_token_secret_name VARCHAR(255) NULL,
  team_token_secret_key VARCHAR(255) NULL,
  redis_events_last_id VARCHAR(128) NOT NULL DEFAULT '0-0',
  shared_pvc_name VARCHAR(255) NULL,
  shared_pvc_namespace VARCHAR(255) NULL,
  shared_mount_path VARCHAR(255) NOT NULL DEFAULT '/team',
  storage_class VARCHAR(100) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uk_teams_user_name (user_id, name),
  INDEX idx_teams_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  instance_id INT NULL,
  user_id INT NOT NULL,
  member_key VARCHAR(100) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  role VARCHAR(100) NOT NULL,
  description TEXT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'creating',
  current_task_id INT NULL,
  progress INT NOT NULL DEFAULT 0,
  last_seen_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (instance_id) REFERENCES instances(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uk_team_members_key (team_id, member_key),
  UNIQUE KEY uk_team_members_instance (team_id, instance_id),
  INDEX idx_team_members_team_status (team_id, status),
  INDEX idx_team_members_instance (instance_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  target_member_id INT NOT NULL,
  created_by INT NULL,
  message_id VARCHAR(255) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  payload_json LONGTEXT NOT NULL,
  result_json LONGTEXT NULL,
  error_message TEXT NULL,
  redis_stream_id VARCHAR(128) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dispatched_at TIMESTAMP NULL,
  started_at TIMESTAMP NULL,
  finished_at TIMESTAMP NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (target_member_id) REFERENCES team_members(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uk_team_tasks_message (team_id, message_id),
  INDEX idx_team_tasks_team_status (team_id, status),
  INDEX idx_team_tasks_member_status (target_member_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  member_id INT NULL,
  task_id INT NULL,
  message_id VARCHAR(255) NULL,
  event_type VARCHAR(100) NOT NULL,
  payload_json LONGTEXT NULL,
  redis_stream_id VARCHAR(128) NULL,
  occurred_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (member_id) REFERENCES team_members(id) ON DELETE SET NULL,
  FOREIGN KEY (task_id) REFERENCES team_tasks(id) ON DELETE SET NULL,
  UNIQUE KEY uk_team_events_stream_id (team_id, redis_stream_id),
  INDEX idx_team_events_team_created (team_id, created_at),
  INDEX idx_team_events_task (task_id),
  INDEX idx_team_events_member (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
