-- =====================================================================
-- Gym Management System - database schema  (schema version 1)
-- Target: MySQL 5.7+/8.x or MariaDB 10.3+ (what Namecheap cPanel offers)
-- Import in phpMyAdmin AFTER selecting your (empty) database.
-- NO seed data: the database starts empty. The first gym + owner are
-- created by the one-time POST /api/v1/setup endpoint.
-- Money: BIGINT UNSIGNED, integer PAISE (100 paise = Rs 1). Never floats.
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS schema_migrations (
  version     INT UNSIGNED NOT NULL,
  applied_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- gyms
CREATE TABLE gyms (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(120) NOT NULL,
  owner_name      VARCHAR(120) NOT NULL,
  phone           VARCHAR(15)  NULL,
  email           VARCHAR(190) NULL,
  address         VARCHAR(255) NULL,
  member_seq      INT UNSIGNED NOT NULL DEFAULT 0,   -- counter for MEM0000001 codes
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT chk_gyms_seq CHECK (member_seq <= 9999999)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------- users
CREATE TABLE users (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  gym_id          INT UNSIGNED NOT NULL,
  name            VARCHAR(120) NOT NULL,
  email           VARCHAR(190) NOT NULL,
  phone           VARCHAR(15)  NULL,
  password_hash   VARCHAR(255) NOT NULL,              -- argon2id or bcrypt
  role            ENUM('owner','staff') NOT NULL DEFAULT 'staff',
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  failed_logins   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until    DATETIME NULL DEFAULT NULL,         -- UTC
  last_login_at   DATETIME NULL DEFAULT NULL,
  password_changed_at DATETIME NULL DEFAULT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      TIMESTAMP NULL DEFAULT NULL,
  -- soft delete must not block re-using an email: unique only while live
  email_live      VARCHAR(190) GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email_live (email_live),         -- login identifier is global
  KEY idx_users_gym (gym_id, role),
  CONSTRAINT fk_users_gym FOREIGN KEY (gym_id) REFERENCES gyms(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ refresh_tokens
-- Opaque tokens: only a SHA-256 hash is stored. Rotation with reuse
-- detection: all tokens sharing family_id are revoked if a rotated token
-- is presented again.
CREATE TABLE refresh_tokens (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         INT UNSIGNED NOT NULL,
  gym_id          INT UNSIGNED NOT NULL,
  token_hash      CHAR(64) NOT NULL,
  family_id       CHAR(32) NOT NULL,
  expires_at      DATETIME NOT NULL,
  revoked_at      DATETIME NULL DEFAULT NULL,
  replaced_by_id  BIGINT UNSIGNED NULL DEFAULT NULL,
  device_label    VARCHAR(120) NULL,
  ip              VARCHAR(45) NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rt_hash (token_hash),
  KEY idx_rt_user (user_id, revoked_at),
  KEY idx_rt_family (family_id),
  KEY idx_rt_expires (expires_at),
  CONSTRAINT fk_rt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_rt_gym  FOREIGN KEY (gym_id)  REFERENCES gyms(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ login_attempts
-- Backs the rate limiter / lockout (per identifier and per IP).
CREATE TABLE login_attempts (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  identifier      VARCHAR(190) NOT NULL,              -- lower-cased email
  ip              VARCHAR(45)  NOT NULL,
  success         TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_la_ident (identifier, attempted_at),
  KEY idx_la_ip (ip, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------- members
CREATE TABLE members (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  gym_id          INT UNSIGNED NOT NULL,
  member_code     CHAR(10) NOT NULL,                  -- 'MEM' + 7 digits
  name            VARCHAR(120) NOT NULL,
  phone           VARCHAR(10) NOT NULL,               -- exactly 10 digits (CHECK below); 10-digit Indian mobile (6-9 start)
  email           VARCHAR(190) NULL,
  gender          ENUM('male','female','other') NULL,
  dob             DATE NULL,
  address         VARCHAR(255) NULL,
  emergency_contact VARCHAR(120) NULL,
  notes           VARCHAR(500) NULL,
  joined_on       DATE NOT NULL,
  created_by      INT UNSIGNED NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      TIMESTAMP NULL DEFAULT NULL,
  phone_live      VARCHAR(10) GENERATED ALWAYS AS (IF(deleted_at IS NULL, phone, NULL)) STORED,
  birth_md        SMALLINT UNSIGNED GENERATED ALWAYS AS (MONTH(dob) * 100 + DAY(dob)) STORED,  -- e.g. 0314 -> 314
  PRIMARY KEY (id),
  UNIQUE KEY uq_members_code (gym_id, member_code),
  UNIQUE KEY uq_members_phone_live (gym_id, phone_live),  -- unique per gym, soft-delete safe
  KEY idx_members_name (gym_id, name),
  KEY idx_members_birth (gym_id, birth_md),
  KEY idx_members_created (gym_id, created_at),
  CONSTRAINT fk_members_gym  FOREIGN KEY (gym_id) REFERENCES gyms(id) ON DELETE RESTRICT,
  CONSTRAINT fk_members_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT chk_members_phone CHECK (phone REGEXP '^[6-9][0-9]{9}$'),
  CONSTRAINT chk_members_code  CHECK (member_code REGEXP '^MEM[0-9]{7}$')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------- plans
CREATE TABLE plans (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  gym_id          INT UNSIGNED NOT NULL,
  name            VARCHAR(80) NOT NULL,
  duration_months TINYINT UNSIGNED NOT NULL,
  price_paise     BIGINT UNSIGNED NOT NULL,
  description     VARCHAR(255) NULL,
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_plans_gym (gym_id, is_active, deleted_at),
  CONSTRAINT fk_plans_gym FOREIGN KEY (gym_id) REFERENCES gyms(id) ON DELETE RESTRICT,
  CONSTRAINT chk_plans_duration CHECK (duration_months BETWEEN 1 AND 60),
  CONSTRAINT chk_plans_price CHECK (price_paise > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------- memberships
-- Plan name/duration/price are SNAPSHOTS so editing a plan never rewrites history.
-- end_date = start_date + duration_months - 1 day  (computed by the server, stored).
-- Active/Expiring/Expired is NOT stored: derived from end_date at query time.
-- A membership replaced by a renewal gets superseded_by_id + superseded_plan_name.
CREATE TABLE memberships (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  gym_id          INT UNSIGNED NOT NULL,
  member_id       INT UNSIGNED NOT NULL,
  plan_id         INT UNSIGNED NULL,
  plan_name       VARCHAR(80) NOT NULL,
  duration_months TINYINT UNSIGNED NOT NULL,
  start_date      DATE NOT NULL,
  end_date        DATE NOT NULL,
  price_paise     BIGINT UNSIGNED NOT NULL,
  discount_paise  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  final_paise     BIGINT GENERATED ALWAYS AS (CAST(price_paise AS SIGNED) - CAST(discount_paise AS SIGNED)) STORED,
  paid_paise      BIGINT UNSIGNED NOT NULL DEFAULT 0,   -- maintained transactionally with payments
  due_paise       BIGINT GENERATED ALWAYS AS (CAST(price_paise AS SIGNED) - CAST(discount_paise AS SIGNED) - CAST(paid_paise AS SIGNED)) STORED,
  superseded_by_id INT UNSIGNED NULL DEFAULT NULL,
  superseded_plan_name VARCHAR(80) NULL DEFAULT NULL,   -- shown as "renewed with <plan>"
  created_by      INT UNSIGNED NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_ms_member (gym_id, member_id, start_date),
  KEY idx_ms_end (gym_id, end_date),
  KEY idx_ms_start (gym_id, start_date),
  KEY idx_ms_due (gym_id, due_paise),
  KEY idx_ms_plan (plan_id),
  CONSTRAINT fk_ms_gym    FOREIGN KEY (gym_id)    REFERENCES gyms(id)    ON DELETE RESTRICT,
  CONSTRAINT fk_ms_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE RESTRICT,
  CONSTRAINT fk_ms_plan   FOREIGN KEY (plan_id)   REFERENCES plans(id)   ON DELETE SET NULL,
  CONSTRAINT fk_ms_super  FOREIGN KEY (superseded_by_id) REFERENCES memberships(id) ON DELETE SET NULL,
  CONSTRAINT fk_ms_user   FOREIGN KEY (created_by) REFERENCES users(id)  ON DELETE SET NULL,
  CONSTRAINT chk_ms_discount CHECK (discount_paise <= price_paise),
  CONSTRAINT chk_ms_paid     CHECK (paid_paise + discount_paise <= price_paise),
  CONSTRAINT chk_ms_dates    CHECK (end_date >= start_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------ payments
-- Append-only ledger. A mistake is corrected by VOIDING (owner only), which
-- the server reverses in memberships.paid_paise inside one transaction.
CREATE TABLE payments (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gym_id          INT UNSIGNED NOT NULL,
  member_id       INT UNSIGNED NOT NULL,
  membership_id   INT UNSIGNED NOT NULL,
  amount_paise    BIGINT UNSIGNED NOT NULL,
  method          ENUM('cash','upi','card','bank_transfer','other') NOT NULL DEFAULT 'cash',
  paid_on         DATE NOT NULL,
  reference       VARCHAR(80) NULL,
  note            VARCHAR(255) NULL,
  received_by     INT UNSIGNED NULL,
  voided_at       DATETIME NULL DEFAULT NULL,
  voided_by       INT UNSIGNED NULL DEFAULT NULL,
  void_reason     VARCHAR(255) NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pay_gym_date (gym_id, paid_on),
  KEY idx_pay_member (gym_id, member_id, paid_on),
  KEY idx_pay_membership (membership_id),
  CONSTRAINT fk_pay_gym        FOREIGN KEY (gym_id)        REFERENCES gyms(id)        ON DELETE RESTRICT,
  CONSTRAINT fk_pay_member     FOREIGN KEY (member_id)     REFERENCES members(id)     ON DELETE RESTRICT,
  CONSTRAINT fk_pay_membership FOREIGN KEY (membership_id) REFERENCES memberships(id) ON DELETE RESTRICT,
  CONSTRAINT fk_pay_user       FOREIGN KEY (received_by)   REFERENCES users(id)       ON DELETE SET NULL,
  CONSTRAINT fk_pay_voider     FOREIGN KEY (voided_by)     REFERENCES users(id)       ON DELETE SET NULL,
  CONSTRAINT chk_pay_amount CHECK (amount_paise > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------ expenses
CREATE TABLE expenses (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  gym_id          INT UNSIGNED NOT NULL,
  category        VARCHAR(60) NOT NULL,
  title           VARCHAR(120) NOT NULL,
  amount_paise    BIGINT UNSIGNED NOT NULL,
  expense_date    DATE NOT NULL,
  method          ENUM('cash','upi','card','bank_transfer','other') NOT NULL DEFAULT 'cash',
  note            VARCHAR(255) NULL,
  created_by      INT UNSIGNED NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_exp_gym_date (gym_id, expense_date),
  KEY idx_exp_cat (gym_id, category),
  CONSTRAINT fk_exp_gym  FOREIGN KEY (gym_id) REFERENCES gyms(id) ON DELETE RESTRICT,
  CONSTRAINT fk_exp_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT chk_exp_amount CHECK (amount_paise > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------ settings
-- Key/value per gym. Includes WhatsApp config (token stored here; never
-- returned to the client in full - the API masks it).
CREATE TABLE settings (
  gym_id          INT UNSIGNED NOT NULL,
  setting_key     VARCHAR(60) NOT NULL,
  setting_value   TEXT NULL,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (gym_id, setting_key),
  CONSTRAINT fk_settings_gym FOREIGN KEY (gym_id) REFERENCES gyms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------- message_queue
-- Filled by the app (profile created, membership activated) and by
-- cron/daily.php (reminders). dedupe_key makes cron idempotent.
CREATE TABLE message_queue (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gym_id          INT UNSIGNED NOT NULL,
  member_id       INT UNSIGNED NULL,
  channel         ENUM('whatsapp') NOT NULL DEFAULT 'whatsapp',
  template        VARCHAR(60) NOT NULL,               -- profile_created, membership_activated, expiring_soon, expired, dues_pending, birthday
  to_phone        VARCHAR(10) NOT NULL,
  params          TEXT NOT NULL,                      -- JSON array of template body params
  status          ENUM('pending','sending','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
  attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_error      VARCHAR(500) NULL,
  provider_msg_id VARCHAR(120) NULL,
  dedupe_key      VARCHAR(120) NOT NULL,
  send_after      DATETIME NOT NULL,
  sent_at         DATETIME NULL DEFAULT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_mq_dedupe (gym_id, dedupe_key),
  KEY idx_mq_status (status, send_after),
  CONSTRAINT fk_mq_gym    FOREIGN KEY (gym_id)    REFERENCES gyms(id)    ON DELETE CASCADE,
  CONSTRAINT fk_mq_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------- audit_log
CREATE TABLE audit_log (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gym_id          INT UNSIGNED NULL,
  user_id         INT UNSIGNED NULL,
  action          VARCHAR(40) NOT NULL,               -- create | update | delete | payment | void | login | logout | export ...
  entity_type     VARCHAR(40) NOT NULL,
  entity_id       BIGINT UNSIGNED NULL,
  details         TEXT NULL,                          -- JSON (no secrets, no password hashes)
  ip              VARCHAR(45) NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_gym_time (gym_id, created_at),
  KEY idx_audit_entity (entity_type, entity_id),
  KEY idx_audit_user (user_id)
  -- intentionally no FKs: the audit trail must survive deletions
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO schema_migrations (version) VALUES (1);