-- ============================================================
-- Ebook Platform Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS ebook_platform
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE ebook_platform;

-- ------------------------------------------------------------
-- Table: users
-- ------------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('superadmin','admin','manager','reader','writer') DEFAULT 'reader',
    status ENUM('active','inactive') DEFAULT 'active',
    referral_code VARCHAR(20) UNIQUE,
    referred_by INT NULL,
    affiliate_balance DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: subscription_packages
-- ------------------------------------------------------------
CREATE TABLE subscription_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    duration_days INT NOT NULL,
    max_books_read INT DEFAULT 0,          -- 0 = unlimited
    price DECIMAL(10,2) NOT NULL,
    features TEXT,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    created_by INT,
    approved_by INT,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: subscriptions
-- ------------------------------------------------------------
CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    package_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('active','expired','cancelled') DEFAULT 'active',
    auto_renew TINYINT(1) DEFAULT 0,
    payment_status ENUM('pending','paid') DEFAULT 'pending',
    used_balance DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES subscription_packages(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: categories
-- ------------------------------------------------------------
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: books
-- ------------------------------------------------------------
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE,
    description TEXT,
    cover_image VARCHAR(255),
    file_url VARCHAR(255),
    file_type VARCHAR(20) DEFAULT 'pdf',
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    download_enabled TINYINT(1) DEFAULT 0,
    created_by INT,
    author_name VARCHAR(255),
    views_count INT DEFAULT 0,
    read_count INT DEFAULT 0,
    is_featured TINYINT(1) DEFAULT 0,
    is_popular TINYINT(1) DEFAULT 0,
    writer_special TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Indexes for search and filtering
ALTER TABLE books ADD INDEX idx_books_title (title);
ALTER TABLE books ADD INDEX idx_books_author (author_name);
ALTER TABLE books ADD INDEX idx_books_featured (is_featured);
ALTER TABLE books ADD INDEX idx_books_popular (is_popular);
ALTER TABLE books ADD INDEX idx_books_writer_special (writer_special);

-- ------------------------------------------------------------
-- Table: book_category (many-to-many)
-- ------------------------------------------------------------
CREATE TABLE book_category (
    book_id INT,
    category_id INT,
    PRIMARY KEY (book_id, category_id),
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: purchases
-- ------------------------------------------------------------
CREATE TABLE purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    commission_amount DECIMAL(10,2) DEFAULT 0,
    status ENUM('pending','approved','rejected','completed') DEFAULT 'pending',
    payment_method VARCHAR(50),
    used_balance DECIMAL(10,2) DEFAULT 0,
    download_token VARCHAR(100),
    downloaded_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: wishlist
-- ------------------------------------------------------------
CREATE TABLE wishlist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    book_id INT,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    UNIQUE KEY unique_wishlist (user_id, book_id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: reading_activity
-- ------------------------------------------------------------
CREATE TABLE reading_activity (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    book_id INT,
    last_read_page INT DEFAULT 0,
    progress DECIMAL(5,2) DEFAULT 0,
    started_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,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: writer_applications
-- ------------------------------------------------------------
CREATE TABLE writer_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    reason TEXT,
    reviewed_by INT,
    reviewed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: commissions
-- ------------------------------------------------------------
CREATE TABLE commissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_id INT,
    writer_id INT,
    amount DECIMAL(10,2) NOT NULL,
    percentage DECIMAL(5,2),
    status ENUM('pending','paid') DEFAULT 'pending',
    paid_at TIMESTAMP NULL,
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (writer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: payment_approvals
-- ------------------------------------------------------------
CREATE TABLE payment_approvals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_id INT,
    requested_by INT,
    approved_by INT,
    decision ENUM('approved','rejected') DEFAULT 'approved',
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: referrals
-- ------------------------------------------------------------
CREATE TABLE referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    referred_user_id INT NOT NULL,
    referral_code VARCHAR(20),
    status ENUM('pending','successful') DEFAULT 'pending',
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    first_payment_at TIMESTAMP NULL,
    commission_earned DECIMAL(10,2) DEFAULT 0,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: commission_transactions
-- ------------------------------------------------------------
CREATE TABLE commission_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    type ENUM('credit','debit') NOT NULL,
    reference_type ENUM('referral','purchase','subscription','withdrawal','manual') NOT NULL,
    reference_id INT,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: withdrawal_requests
-- ------------------------------------------------------------
CREATE TABLE withdrawal_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    mobile_banking_name VARCHAR(50),
    mobile_banking_number VARCHAR(20),
    status ENUM('pending','approved','rejected','completed') DEFAULT 'pending',
    admin_note TEXT,
    reviewed_by INT,
    reviewed_at TIMESTAMP NULL,
    transaction_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: affiliate_settings
-- ------------------------------------------------------------
CREATE TABLE affiliate_settings (
    id INT PRIMARY KEY DEFAULT 1,
    commission_type ENUM('percentage','fixed') DEFAULT 'percentage',
    commission_value DECIMAL(10,2) DEFAULT 10.00,
    threshold_amount DECIMAL(10,2) DEFAULT 50.00,
    updated_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Insert default affiliate settings row
INSERT INTO affiliate_settings (id, commission_type, commission_value, threshold_amount)
VALUES (1, 'percentage', 10.00, 50.00);

-- ------------------------------------------------------------
-- Table: notifications
-- ------------------------------------------------------------
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    message TEXT,
    link VARCHAR(255),
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- End of schema
-- ============================================================