-- =====================================================================
--  Gym App  |  MySQL 5.7+ / MariaDB 10.3+
--  استورد هذا الملف من phpMyAdmin (Import) داخل قاعدة البيانات الفارغة.
--  آمن لإعادة التشغيل: كل الجداول IF NOT EXISTS.
-- =====================================================================
SET NAMES utf8mb4;

-- ---------- المستخدمون (أدمن / كابتن / متدرب) ----------
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  role          ENUM('admin','captain','trainee') NOT NULL,
  full_name     VARCHAR(120) NOT NULL,
  username      VARCHAR(30)  NULL,
  phone         VARCHAR(20)  NULL,               -- بصيغة دولية بدون +  مثال 9647701234567
  password_hash VARCHAR(255) NOT NULL,
  avatar_path   VARCHAR(255) NULL,
  is_blocked    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),
  UNIQUE KEY uq_users_username (username),
  UNIQUE KEY uq_users_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- الكباتن + الاشتراك ----------
CREATE TABLE IF NOT EXISTS captains (
  user_id      INT UNSIGNED NOT NULL,
  is_active    TINYINT(1)   NOT NULL DEFAULT 0,   -- يفعّله الأدمن
  paid_until   DATE         NULL,                 -- آخر يوم مدفوع
  activated_at DATETIME     NULL,
  admin_notes  VARCHAR(500) NULL,
  PRIMARY KEY (user_id),
  CONSTRAINT fk_captains_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- سجل الدفعات (لمعرفة دخلك وتاريخ كل تمديد)
CREATE TABLE IF NOT EXISTS subscription_payments (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  captain_id INT UNSIGNED NOT NULL,
  admin_id   INT UNSIGNED NULL,
  months     TINYINT UNSIGNED NOT NULL DEFAULT 1,
  amount     DECIMAL(12,2) NOT NULL DEFAULT 0,
  period_end DATE NOT NULL,
  paid_on    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note       VARCHAR(255) NULL,
  PRIMARY KEY (id),
  KEY idx_pay_captain (captain_id),
  CONSTRAINT fk_pay_captain FOREIGN KEY (captain_id) REFERENCES captains(user_id) ON DELETE CASCADE,
  CONSTRAINT fk_pay_admin   FOREIGN KEY (admin_id)   REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- المتدربون + كودهم ----------
CREATE TABLE IF NOT EXISTS trainees (
  user_id      INT UNSIGNED NOT NULL,
  trainee_code CHAR(8)      NOT NULL,
  captain_id   INT UNSIGNED NULL,
  linked_at    DATETIME     NULL,
  PRIMARY KEY (user_id),
  UNIQUE KEY uq_trainee_code (trainee_code),
  KEY idx_trainees_captain (captain_id),
  CONSTRAINT fk_trainees_user    FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_trainees_captain FOREIGN KEY (captain_id) REFERENCES captains(user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- طلبات الربط (الكابتن يدخل الكود ← المتدرب يوافق)
CREATE TABLE IF NOT EXISTS link_requests (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  captain_id   INT UNSIGNED NOT NULL,
  trainee_id   INT UNSIGNED NOT NULL,
  status       ENUM('pending','accepted','rejected','cancelled') NOT NULL DEFAULT 'pending',
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  responded_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_link_trainee (trainee_id, status),
  KEY idx_link_captain (captain_id, status),
  CONSTRAINT fk_link_captain FOREIGN KEY (captain_id) REFERENCES captains(user_id) ON DELETE CASCADE,
  CONSTRAINT fk_link_trainee FOREIGN KEY (trainee_id) REFERENCES trainees(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- مكتبة التمارين ----------
-- captain_id = NULL  ← تمرين عام من الأدمن، يراه كل الكباتن
CREATE TABLE IF NOT EXISTS exercises (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  captain_id   INT UNSIGNED NULL,
  name         VARCHAR(120) NOT NULL,
  muscle_group VARCHAR(50)  NULL,
  media_type   ENUM('none','image','video') NOT NULL DEFAULT 'none',
  media_path   VARCHAR(255) NULL,
  description  TEXT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ex_captain (captain_id),
  KEY idx_ex_muscle (muscle_group),
  CONSTRAINT fk_ex_captain FOREIGN KEY (captain_id) REFERENCES captains(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- البرامج (تدريبي / غذائي) ----------
-- is_template = 1  ← قالب للكابتن (trainee_id = NULL) ينسخه لأي متدرب
-- status: active (أخضر) / archived (أحمر)
CREATE TABLE IF NOT EXISTS programs (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  captain_id   INT UNSIGNED NOT NULL,
  trainee_id   INT UNSIGNED NULL,
  type         ENUM('training','nutrition') NOT NULL,
  title        VARCHAR(150) NOT NULL,
  status       ENUM('active','archived') NOT NULL DEFAULT 'active',
  is_template  TINYINT(1) NOT NULL DEFAULT 0,
  start_date   DATE NULL,
  end_date     DATE NULL,
  notes        TEXT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activated_at DATETIME NULL,
  archived_at  DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_prog_trainee (trainee_id, type, status),
  KEY idx_prog_captain (captain_id, is_template),
  CONSTRAINT fk_prog_captain FOREIGN KEY (captain_id) REFERENCES captains(user_id) ON DELETE CASCADE,
  CONSTRAINT fk_prog_trainee FOREIGN KEY (trainee_id) REFERENCES trainees(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS program_days (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  program_id INT UNSIGNED NOT NULL,
  day_order  SMALLINT UNSIGNED NOT NULL,
  title      VARCHAR(100) NOT NULL,               -- "اليوم الأول - صدر" أو "وجبة الفطور"
  PRIMARY KEY (id),
  UNIQUE KEY uq_day_order (program_id, day_order),
  CONSTRAINT fk_day_program FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- عنصر واحد يخدم النوعين: تمرين (sets/reps/rest) أو وجبة (details/calories)
CREATE TABLE IF NOT EXISTS program_items (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  day_id       INT UNSIGNED NOT NULL,
  item_order   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  exercise_id  INT UNSIGNED NULL,
  title        VARCHAR(150) NOT NULL,
  details      TEXT NULL,
  sets         TINYINT UNSIGNED NULL,
  reps         VARCHAR(20) NULL,                  -- "12" أو "8-10"
  rest_seconds SMALLINT UNSIGNED NULL,
  calories     SMALLINT UNSIGNED NULL,
  notes        VARCHAR(500) NULL,
  PRIMARY KEY (id),
  KEY idx_item_day (day_id, item_order),
  CONSTRAINT fk_item_day      FOREIGN KEY (day_id)      REFERENCES program_days(id) ON DELETE CASCADE,
  CONSTRAINT fk_item_exercise FOREIGN KEY (exercise_id) REFERENCES exercises(id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- تسجيل المتدرب لتمرينه ----------
CREATE TABLE IF NOT EXISTS workout_logs (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  trainee_id      INT UNSIGNED NOT NULL,
  program_item_id INT UNSIGNED NULL,
  log_date        DATE NOT NULL,
  sets_done       TINYINT UNSIGNED NULL,
  reps_done       VARCHAR(20) NULL,
  weight_kg       DECIMAL(6,2) NULL,
  completed       TINYINT(1) NOT NULL DEFAULT 1,
  notes           VARCHAR(500) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_log_trainee_date (trainee_id, log_date),
  CONSTRAINT fk_log_trainee FOREIGN KEY (trainee_id)      REFERENCES trainees(user_id)      ON DELETE CASCADE,
  CONSTRAINT fk_log_item    FOREIGN KEY (program_item_id) REFERENCES program_items(id)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- التقدم: قياسات + صور ----------
CREATE TABLE IF NOT EXISTS measurements (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  trainee_id  INT UNSIGNED NOT NULL,
  measured_on DATE NOT NULL,
  weight_kg   DECIMAL(5,2) NULL,
  waist_cm    DECIMAL(5,1) NULL,
  chest_cm    DECIMAL(5,1) NULL,
  arm_cm      DECIMAL(5,1) NULL,
  hips_cm     DECIMAL(5,1) NULL,
  thigh_cm    DECIMAL(5,1) NULL,
  notes       VARCHAR(500) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_meas_trainee (trainee_id, measured_on),
  CONSTRAINT fk_meas_trainee FOREIGN KEY (trainee_id) REFERENCES trainees(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS progress_photos (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  trainee_id INT UNSIGNED NOT NULL,
  photo_path VARCHAR(255) NOT NULL,
  pose       ENUM('front','side','back','other') NOT NULL DEFAULT 'front',
  taken_on   DATE NOT NULL,
  notes      VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_photo_trainee (trainee_id, taken_on),
  CONSTRAINT fk_photo_trainee FOREIGN KEY (trainee_id) REFERENCES trainees(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- التعليقات بين الكابتن والمتدرب ----------
CREATE TABLE IF NOT EXISTS comments (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id   INT UNSIGNED NOT NULL,
  trainee_id  INT UNSIGNED NOT NULL,             -- صاحب المحادثة
  target_type ENUM('program','photo','general') NOT NULL DEFAULT 'general',
  target_id   INT UNSIGNED NULL,
  body        TEXT NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_comment_thread (trainee_id, target_type, target_id),
  CONSTRAINT fk_comment_author  FOREIGN KEY (author_id)  REFERENCES users(id)            ON DELETE CASCADE,
  CONSTRAINT fk_comment_trainee FOREIGN KEY (trainee_id) REFERENCES trainees(user_id)    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- الإشعارات ----------
CREATE TABLE IF NOT EXISTS notifications (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  title      VARCHAR(150) NOT NULL,
  body       VARCHAR(500) NULL,
  is_read    TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notif_user (user_id, is_read, created_at),
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_tokens (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  token      VARCHAR(255) NOT NULL,
  platform   ENUM('android','ios') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_device_token (token),
  KEY idx_device_user (user_id),
  CONSTRAINT fk_device_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;