-- =========================================================
-- StudyTrack :: Pro Features Migration
-- Run this once via phpMyAdmin (or `mysql -u ... -p db < database_pro_features.sql`)
-- Safe to re-run — uses IF NOT EXISTS / catches duplicate column errors.
-- =========================================================

-- ── 1. Pro status columns on users ─────────────────────────────
ALTER TABLE `users`
  ADD COLUMN `is_pro` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Quick flag, kept in sync with pro_expires_at' AFTER `study_goal_minutes`,
  ADD COLUMN `pro_plan` VARCHAR(20) DEFAULT NULL COMMENT 'monthly / yearly' AFTER `is_pro`,
  ADD COLUMN `pro_started_at` DATETIME DEFAULT NULL AFTER `pro_plan`,
  ADD COLUMN `pro_expires_at` DATETIME DEFAULT NULL COMMENT 'NULL + is_pro=1 = lifetime/admin-granted' AFTER `pro_started_at`;

-- ── 2. Site settings (key/value store used across admin panel) ─
CREATE TABLE IF NOT EXISTS `site_settings` (
  `setting_key`   VARCHAR(100) NOT NULL PRIMARY KEY,
  `setting_value` TEXT DEFAULT NULL,
  `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT IGNORE INTO `site_settings` (`setting_key`, `setting_value`) VALUES
  ('pro_price_monthly', '500'),
  ('pro_price_yearly',  '5000'),
  ('pro_currency',      'LKR'),
  ('free_ai_daily_limit', '3');

-- ── 3. PayHere payment records ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `payhere_payments` (
  `id`               INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`          INT NOT NULL,
  `order_id`         VARCHAR(60) NOT NULL UNIQUE,
  `plan`             VARCHAR(20) NOT NULL COMMENT 'monthly / yearly',
  `amount`           DECIMAL(10,2) NOT NULL,
  `currency`         VARCHAR(10) NOT NULL DEFAULT 'LKR',
  `status`           VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending / completed / failed / canceled / chargedback',
  `payhere_payment_id` VARCHAR(100) DEFAULT NULL,
  `method`           VARCHAR(30) DEFAULT NULL,
  `raw_payload`      TEXT DEFAULT NULL,
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX (`user_id`),
  INDEX (`status`)
) ENGINE=InnoDB;

-- ── 4. Daily AI usage log (drives the free-tier quota) ──────────
CREATE TABLE IF NOT EXISTS `ai_usage_log` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT NOT NULL,
  `feature`    VARCHAR(40) NOT NULL COMMENT 'ai_tips_chat / ai_tips_generate / ai_analysis',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX (`user_id`, `created_at`)
) ENGINE=InnoDB;
