CREATE DATABASE IF NOT EXISTS citetrack
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
USE citetrack;

-- USERS
CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role ENUM('USER','ADMIN') NOT NULL DEFAULT 'USER',
  email VARCHAR(190) NOT NULL,
  username VARCHAR(50) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_username (username),
  KEY idx_users_created_at (created_at)
) ENGINE=InnoDB;

-- USER PROFILES (public page data)
CREATE TABLE user_profiles (
  user_id BIGINT UNSIGNED NOT NULL,
  display_name VARCHAR(120) NULL,
  avatar_url VARCHAR(255) NULL,
  main_specialty VARCHAR(120) NULL,
  bio TEXT NULL,

  affiliations_json JSON NULL,

  website_url VARCHAR(255) NULL,
  x_url VARCHAR(255) NULL,
  linkedin_url VARCHAR(255) NULL,
  youtube_url VARCHAR(255) NULL,

  orcid_url VARCHAR(255) NULL,
  scholar_url VARCHAR(255) NULL,
  researchgate_url VARCHAR(255) NULL,
  scopus_url VARCHAR(255) NULL,
  pubmed_url VARCHAR(255) NULL,

  is_public TINYINT(1) NOT NULL DEFAULT 1,
  show_email TINYINT(1) NOT NULL DEFAULT 0,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (user_id),
  CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- LOGIN RATE LIMITING
CREATE TABLE login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ip_hash CHAR(64) NOT NULL,
  identifier VARCHAR(190) NOT NULL, -- email or username
  attempts INT NOT NULL DEFAULT 0,
  last_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  locked_until DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_login_ip_identifier (ip_hash, identifier),
  KEY idx_login_locked_until (locked_until)
) ENGINE=InnoDB;

-- IDEAS
CREATE TABLE ideas (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  note TEXT NULL,
  tags_json JSON NULL,
  priority ENUM('LOW','MEDIUM','HIGH') NOT NULL DEFAULT 'MEDIUM',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ideas_user (user_id),
  KEY idx_ideas_updated (user_id, updated_at),
  CONSTRAINT fk_ideas_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- PROJECTS
CREATE TABLE projects (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  aim_summary TEXT NULL,
  tags_json JSON NULL,

  study_design ENUM(
    'SYSTEMATIC_REVIEW_META_ANALYSIS',
    'CASE_CONTROL',
    'COHORT_PROSPECTIVE',
    'COHORT_RETROSPECTIVE',
    'CASE_REPORT'
  ) NOT NULL,

  status ENUM(
    'IDEA','ACTIVE','SUBMITTED','UNDER_REVIEW','ACCEPTED','PUBLISHED',
    'REJECTED','ON_HOLD','ABANDONED'
  ) NOT NULL DEFAULT 'IDEA',

  visibility ENUM('PRIVATE','PUBLIC') NOT NULL DEFAULT 'PRIVATE',

  target_journal VARCHAR(190) NULL,
  backup_journal VARCHAR(190) NULL,
  coauthors TEXT NULL,

  start_date DATE NULL,
  planned_end_date DATE NULL,

  study_type_details JSON NULL,

  last_activity_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY idx_projects_user (user_id),
  KEY idx_projects_status (user_id, status),
  KEY idx_projects_visibility (visibility, status),
  KEY idx_projects_activity (user_id, last_activity_at),
  CONSTRAINT fk_projects_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- STAGE TEMPLATES (seeded)
CREATE TABLE stage_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  study_design ENUM(
    'SYSTEMATIC_REVIEW_META_ANALYSIS',
    'CASE_CONTROL',
    'COHORT_PROSPECTIVE',
    'COHORT_RETROSPECTIVE',
    'CASE_REPORT'
  ) NOT NULL,
  code VARCHAR(80) NOT NULL,
  name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  sort_order INT NOT NULL,
  suggested_duration_days INT NULL,
  follow_up_cadence_days INT NULL,
  deliverables_json JSON NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_stage_templates_design_code (study_design, code),
  KEY idx_stage_templates_design_order (study_design, sort_order)
) ENGINE=InnoDB;

-- PROJECT STAGES (cloned from templates)
CREATE TABLE project_stages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(80) NOT NULL,
  name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  sort_order INT NOT NULL,

  planned_start DATE NULL,
  planned_end DATE NULL,
  actual_start DATE NULL,
  actual_end DATE NULL,

  stage_status ENUM('NOT_STARTED','IN_PROGRESS','DONE','BLOCKED') NOT NULL DEFAULT 'NOT_STARTED',
  next_follow_up_date DATE NULL,

  deliverables_json JSON NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_project_stages_project_code (project_id, code),
  KEY idx_project_stages_project_order (project_id, sort_order),
  KEY idx_project_stages_followup (project_id, next_follow_up_date),
  CONSTRAINT fk_project_stages_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- TASKS
CREATE TABLE tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  project_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  due_date DATE NULL,
  status ENUM('TODO','DOING','DONE') NOT NULL DEFAULT 'TODO',
  priority ENUM('LOW','MEDIUM','HIGH') NOT NULL DEFAULT 'MEDIUM',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tasks_user_status (user_id, status),
  KEY idx_tasks_due (user_id, due_date),
  KEY idx_tasks_project (project_id),
  CONSTRAINT fk_tasks_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_tasks_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- NOTES (idea or project; exactly one FK must be set)
CREATE TABLE notes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  project_id BIGINT UNSIGNED NULL,
  idea_id BIGINT UNSIGNED NULL,
  content MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notes_user_updated (user_id, updated_at),
  KEY idx_notes_project (project_id),
  KEY idx_notes_idea (idea_id),
  CONSTRAINT fk_notes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_notes_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_notes_idea FOREIGN KEY (idea_id) REFERENCES ideas(id) ON DELETE CASCADE,
  CONSTRAINT chk_notes_one_parent CHECK (
    (project_id IS NOT NULL AND idea_id IS NULL) OR
    (project_id IS NULL AND idea_id IS NOT NULL)
  )
) ENGINE=InnoDB;

-- ATTACHMENTS
CREATE TABLE attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  project_id BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime VARCHAR(120) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL,
  storage_path VARCHAR(255) NOT NULL, -- relative to STORAGE_PATH
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_attachments_project (project_id),
  KEY idx_attachments_user (user_id),
  CONSTRAINT fk_attachments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_attachments_project FOREIGN KEY (project_id) NOT NULL FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- PUBLICATIONS
CREATE TABLE publications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  authors TEXT NULL,
  journal VARCHAR(190) NULL,
  year SMALLINT NULL,
  doi VARCHAR(255) NULL,
  link_url VARCHAR(255) NULL,
  is_public TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_publications_user (user_id),
  KEY idx_publications_public (is_public, year),
  CONSTRAINT fk_publications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- FOLLOWS
CREATE TABLE follows (
  follower_id BIGINT UNSIGNED NOT NULL,
  following_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, following_id),
  KEY idx_follows_following (following_id),
  CONSTRAINT fk_follows_follower FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_follows_following FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT chk_follows_not_self CHECK (follower_id <> following_id)
) ENGINE=InnoDB;

-- PUBLIC EVENTS (for /feed)
CREATE TABLE public_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_user_id BIGINT UNSIGNED NOT NULL,
  event_type ENUM('PUBLICATION_CREATED','PROJECT_PUBLISHED_PUBLIC') NOT NULL,
  object_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_events_actor_time (actor_user_id, created_at),
  KEY idx_events_type_time (event_type, created_at),
  CONSTRAINT fk_events_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- MESSAGING
CREATE TABLE conversations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_message_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_conversations_last (last_message_at),
  CONSTRAINT fk_conversations_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE conversation_participants (
  conversation_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  left_at DATETIME NULL,
  PRIMARY KEY (conversation_id, user_id),
  KEY idx_conv_part_user (user_id),
  CONSTRAINT fk_conv_part_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_conv_part_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender_id BIGINT UNSIGNED NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_messages_conv_time (conversation_id, created_at),
  CONSTRAINT fk_messages_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_messages_sender FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE message_reads (
  conversation_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  last_read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (conversation_id, user_id),
  CONSTRAINT fk_reads_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_reads_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- PROFILE VIEWS (dedup/day)
CREATE TABLE profile_views (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  profile_user_id BIGINT UNSIGNED NOT NULL,
  viewer_user_id BIGINT UNSIGNED NULL,
  viewer_hash CHAR(64) NULL,
  view_date DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_profile_views_logged (profile_user_id, viewer_user_id, view_date),
  UNIQUE KEY uq_profile_views_anon (profile_user_id, viewer_hash, view_date),
  KEY idx_profile_views_profile_date (profile_user_id, view_date),
  CONSTRAINT fk_profile_views_profile FOREIGN KEY (profile_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_profile_views_viewer FOREIGN KEY (viewer_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- GENERIC RATE LIMITS (e.g., messaging)
CREATE TABLE rate_limits (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  key_name VARCHAR(80) NOT NULL,
  scope_hash CHAR(64) NOT NULL, -- e.g. ip hash or user hash
  window_start DATETIME NOT NULL,
  window_seconds INT NOT NULL,
  max_count INT NOT NULL,
  current_count INT NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rate_limits_key_scope_window (key_name, scope_hash, window_start),
  KEY idx_rate_limits_window (window_start),
  CONSTRAINT fk_rate_limits_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
