-- ============================================================
-- Learnillion — Migration 006: users, auth, subscriptions, views
-- ============================================================

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(190) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `stripe_customer_id` VARCHAR(120) DEFAULT NULL,
  `stripe_subscription_id` VARCHAR(120) DEFAULT NULL,
  `subscription_status` ENUM('none','trialing','active','past_due','canceled','incomplete') DEFAULT 'none',
  `subscription_current_period_end` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_stripe_customer (`stripe_customer_id`),
  INDEX idx_sub_status (`subscription_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pwreset_user FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX idx_token (`token_hash`),
  INDEX idx_expires (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Every time a free user opens a lesson, we insert a row. Used for daily limit.
-- Also serves as "lessons viewed / completed" history on the account page.
CREATE TABLE IF NOT EXISTS `user_lesson_views` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `lesson_id` INT UNSIGNED NOT NULL,
  `viewed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `completed` TINYINT(1) DEFAULT 0,
  CONSTRAINT fk_lv_user   FOREIGN KEY (`user_id`)   REFERENCES `users`(`id`)   ON DELETE CASCADE,
  CONSTRAINT fk_lv_lesson FOREIGN KEY (`lesson_id`) REFERENCES `lessons`(`id`) ON DELETE CASCADE,
  INDEX idx_user_date (`user_id`, `viewed_at`),
  INDEX idx_lesson (`lesson_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_path_views` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `skill_path_id` INT UNSIGNED NOT NULL,
  `viewed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pv_user FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT fk_pv_path FOREIGN KEY (`skill_path_id`) REFERENCES `skill_paths`(`id`) ON DELETE CASCADE,
  INDEX idx_user_date (`user_id`, `viewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate limiting login attempts
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ip` VARCHAR(64) NOT NULL,
  `email` VARCHAR(190) DEFAULT NULL,
  `success` TINYINT(1) DEFAULT 0,
  `attempted_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ip (`ip`, `attempted_at`),
  INDEX idx_email (`email`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;