-- Malapit — schema (MySQL 8.0+)
-- ST_Distance_Sphere requires MySQL 5.7.6+; JSON + CHECK constraints need 8.0.

CREATE TABLE IF NOT EXISTS users (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name          VARCHAR(80)  NOT NULL,
  bio           VARCHAR(500) DEFAULT '',
  photo_url     VARCHAR(500) DEFAULT NULL,

  -- Store the birthdate, never a plain "age" column.
  -- An age column is wrong the day after you write it.
  birthdate     DATE NOT NULL,

  gender        ENUM('man','woman','nonbinary')            NOT NULL,
  interested_in ENUM('man','woman','nonbinary','everyone')  NOT NULL DEFAULT 'everyone',

  -- Last known position. Written on app open, not on every request.
  lat           DECIMAL(10,8) DEFAULT NULL,
  lng           DECIMAL(11,8) DEFAULT NULL,
  city          VARCHAR(100)  DEFAULT NULL,
  located_at    DATETIME      DEFAULT NULL,

  -- Search preferences
  min_age       TINYINT UNSIGNED NOT NULL DEFAULT 18,
  max_age       TINYINT UNSIGNED NOT NULL DEFAULT 99,
  max_distance_km SMALLINT UNSIGNED NOT NULL DEFAULT 25,

  is_active     BOOLEAN  NOT NULL DEFAULT TRUE,
  last_active   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT chk_age_range CHECK (min_age >= 18 AND max_age >= min_age),

  -- The bounding-box prefilter reads lat then lng, so a composite index
  -- in that order lets MySQL use the range scan on lat and filter lng from
  -- the index without touching the row.
  INDEX idx_discovery (is_active, lat, lng),
  INDEX idx_birthdate (birthdate),
  INDEX idx_last_active (last_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS swipes (
  from_user_id BIGINT UNSIGNED NOT NULL,
  to_user_id   BIGINT UNSIGNED NOT NULL,
  liked        BOOLEAN NOT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (from_user_id, to_user_id),
  INDEX idx_incoming_likes (to_user_id, liked),
  FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (to_user_id)   REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- user_a_id is always the smaller id, so a pair can only ever produce one row.
CREATE TABLE IF NOT EXISTS matches (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_a_id  BIGINT UNSIGNED NOT NULL,
  user_b_id  BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_pair (user_a_id, user_b_id),
  INDEX idx_user_b (user_b_id),
  FOREIGN KEY (user_a_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (user_b_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blocks (
  blocker_id BIGINT UNSIGNED NOT NULL,
  blocked_id BIGINT UNSIGNED NOT NULL,
  reason     VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (blocker_id, blocked_id),
  INDEX idx_blocked (blocked_id),
  FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;