
-- Yuitor Admin Panel SQL Dump with Sample Data
CREATE DATABASE IF NOT EXISTS yuitor_admin DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE yuitor_admin;

-- KYC Requests Table
CREATE TABLE IF NOT EXISTS kyc_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    document_type VARCHAR(50),
    document_file VARCHAR(255),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    reviewed_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO kyc_requests (user_id, document_type, document_file, status) VALUES
(2, 'Aadhar', 'aadhar_student_a.pdf', 'pending');

-- Complaints Table
CREATE TABLE IF NOT EXISTS complaints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    category VARCHAR(100),
    message TEXT,
    status ENUM('open', 'in_progress', 'resolved') DEFAULT 'open',
    sla_hours INT DEFAULT 48,
    assigned_to INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO complaints (user_id, category, message) VALUES
(2, 'Technical', 'App not working');

-- Courses Table
CREATE TABLE IF NOT EXISTS courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    description TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO courses (title, description, status, created_by) VALUES
('Math Foundation', 'Basic math for students', 'approved', 3);

-- Book Listings Table
CREATE TABLE IF NOT EXISTS book_listings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    book_title VARCHAR(255),
    subject VARCHAR(100),
    price DECIMAL(10,2),
    condition ENUM('new', 'used'),
    approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO book_listings (user_id, book_title, subject, price, condition) VALUES
(2, 'NCERT Physics Part 1', 'Physics', 150.00, 'used');

-- Referrer Payouts Table
CREATE TABLE IF NOT EXISTS referrer_payouts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT,
    amount DECIMAL(10,2),
    status ENUM('pending', 'paid') DEFAULT 'pending',
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL
);
INSERT INTO referrer_payouts (referrer_id, amount, status) VALUES
(4, 250.00, 'pending');

-- Scholarships Table
CREATE TABLE IF NOT EXISTS scholarships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    discount_percent INT,
    valid_from DATE,
    valid_to DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO scholarships (name, discount_percent, valid_from, valid_to) VALUES
('Merit Offer', 20, '2025-06-01', '2025-06-30');

-- Franchise Table
CREATE TABLE IF NOT EXISTS franchises (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(100),
    owner_name VARCHAR(100),
    contact_email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO franchises (name, city, owner_name, contact_email) VALUES
('Yuitor Lucknow', 'Lucknow', 'Ashok Singh', 'ashok@yuitor.com');

-- Redeem Store Coupons
CREATE TABLE IF NOT EXISTS redeem_coupons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50),
    description TEXT,
    discount_amount DECIMAL(10,2),
    valid_till DATE,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO redeem_coupons (code, description, discount_amount, valid_till) VALUES
('FIRST100', '100₹ off on first course', 100, '2025-12-31');
