-- Video Call App Database Schema (MySQL for cPanel Import)

-- 1. Users Table (Handles both Fans & Girl/Models)
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) UNIQUE NOT NULL,
  `password_hash` VARCHAR(255) NULL,
  `google_id` VARCHAR(100) NULL,
  `avatar` VARCHAR(255) DEFAULT 'default_avatar.png',
  `role` ENUM('user', 'girl', 'admin') DEFAULT 'user',
  `coins` INT DEFAULT 0,
  `referrer_used` VARCHAR(50) NULL,
  `status` ENUM('active', 'blocked') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Girl/Model Profiles Table
CREATE TABLE IF NOT EXISTS `girls_profiles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNIQUE NOT NULL,
  `referrer_slug` VARCHAR(50) UNIQUE NOT NULL,
  `bio` TEXT NULL,
  `call_rate_per_min` INT DEFAULT 50,
  `video_teaser_url` VARCHAR(255) NULL,
  `cover_photo` VARCHAR(255) NULL,
  `gallery_images` JSON NULL,
  `is_online` TINYINT(1) DEFAULT 0,
  `is_verified` TINYINT(1) DEFAULT 1,
  `total_earned_coins` INT DEFAULT 0,
  `upi_id` VARCHAR(100) NULL,
  `bank_name` VARCHAR(100) NULL,
  `account_number` VARCHAR(50) NULL,
  `ifsc_code` VARCHAR(20) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Coin Packages Table (PhonePe Recharges)
CREATE TABLE IF NOT EXISTS `coin_packages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(100) NOT NULL,
  `coins_amount` INT NOT NULL,
  `price_inr` DECIMAL(10,2) NOT NULL,
  `badge` VARCHAR(50) NULL DEFAULT 'Popular',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Populate default coin packages
INSERT IGNORE INTO `coin_packages` (`id`, `title`, `coins_amount`, `price_inr`, `badge`) VALUES
(1, 'Starter Pack', 100, 99.00, 'Basic'),
(2, 'Popular Pack', 350, 299.00, 'Best Value'),
(3, 'VIP Pack', 700, 499.00, 'Popular'),
(4, 'Ultra VIP Pack', 1500, 999.00, 'Super Saver');

-- 4. Virtual Gifts Table
CREATE TABLE IF NOT EXISTS `virtual_gifts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `coins_cost` INT NOT NULL,
  `icon_url` VARCHAR(255) NOT NULL,
  `animation_type` VARCHAR(50) DEFAULT 'lottie_rose',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `virtual_gifts` (`id`, `name`, `coins_cost`, `icon_url`, `animation_type`) VALUES
(1, 'Rose', 10, 'rose.png', 'rose'),
(2, 'Heart', 50, 'heart.png', 'heart'),
(3, 'Crown', 200, 'crown.png', 'crown'),
(4, 'Diamond', 500, 'diamond.png', 'diamond');

-- 5. PhonePe Transactions Table
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` VARCHAR(100) UNIQUE NOT NULL,
  `user_id` INT NOT NULL,
  `package_id` INT NULL,
  `amount_inr` DECIMAL(10,2) NOT NULL,
  `coins_credited` INT NOT NULL,
  `payment_id` VARCHAR(100) NULL,
  `payment_status` ENUM('pending', 'success', 'failed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Call Logs & Per-Minute Billing Table
CREATE TABLE IF NOT EXISTS `call_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `channel_name` VARCHAR(100) NOT NULL,
  `user_id` INT NOT NULL,
  `girl_id` INT NOT NULL,
  `duration_seconds` INT DEFAULT 0,
  `coins_per_min` INT NOT NULL,
  `total_coins_spent` INT DEFAULT 0,
  `girl_earned_coins` INT DEFAULT 0,
  `admin_commission_coins` INT DEFAULT 0,
  `status` ENUM('ongoing', 'completed', 'missed', 'rejected') DEFAULT 'ongoing',
  `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `ended_at` TIMESTAMP NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`girl_id`) REFERENCES `girls_profiles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Girl Withdrawal Requests Table
CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `girl_id` INT NOT NULL,
  `amount_coins` INT NOT NULL,
  `amount_inr` DECIMAL(10,2) NOT NULL,
  `upi_id` VARCHAR(100) NOT NULL,
  `bank_details` TEXT NULL,
  `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  `utr_number` VARCHAR(100) NULL,
  `admin_notes` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`girl_id`) REFERENCES `girls_profiles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert Seed Admin User & Sample Girl (Ranu) safely
INSERT IGNORE INTO `users` (`id`, `name`, `email`, `password_hash`, `role`) VALUES
(1, 'Admin Superuser', 'admin@videocall.com', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1eGkCjB8N1R7s6N9uF9/3A5lF5u.2G.', 'admin'),
(2, 'Ranu Roy', 'ranu@model.com', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1eGkCjB8N1R7s6N9uF9/3A5lF5u.2G.', 'girl');

INSERT IGNORE INTO `girls_profiles` (`id`, `user_id`, `referrer_slug`, `bio`, `call_rate_per_min`, `cover_photo`, `is_online`, `upi_id`) VALUES
(1, 2, 'ranu', 'Hey fans! I am Ranu. Connect with me 1-on-1 for private video calls ❤️', 50, 'ranu_banner.jpg', 1, 'ranu@upi');