-- RideTrack v0.1 schema (MySQL 5.7+ / MariaDB 10.3+)

CREATE TABLE IF NOT EXISTS trails (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,          -- mã tuyến / nội dung QR
  name VARCHAR(190) NOT NULL,
  description TEXT,
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  off_route_meters INT NOT NULL DEFAULT 40,  -- ngưỡng cảnh báo đi lệch
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS trail_points (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  trail_id INT NOT NULL,
  seq INT NOT NULL,                          -- thứ tự trên vệt
  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  recorded_at DATETIME NULL,
  KEY idx_trail_seq (trail_id, seq),
  FOREIGN KEY (trail_id) REFERENCES trails(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS trail_notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  trail_id INT NOT NULL,
  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  title VARCHAR(190) NOT NULL,
  note TEXT,
  image_path VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_trail (trail_id),
  FOREIGN KEY (trail_id) REFERENCES trails(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
