-- =========================================================
-- Secure Bangla PDF eBook Store — Database Schema
-- Engine: MySQL 8+ / InnoDB / utf8mb4
-- =========================================================

CREATE DATABASE IF NOT EXISTS `pdfstore` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `pdfstore`;

-- ---------------------------------------------------------
-- Admins
-- ---------------------------------------------------------
CREATE TABLE admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin','editor','support') NOT NULL DEFAULT 'editor',
    status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Users
-- ---------------------------------------------------------
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(30) DEFAULT NULL,
    password VARCHAR(255) NOT NULL,
    profile_image VARCHAR(255) DEFAULT NULL,
    status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    remember_token VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Categories
-- ---------------------------------------------------------
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Books
-- buy_link  -> OPTIONAL external "Buy Now" link the admin can add/edit.
--              When set (buy_mode = 'external'), the Buy button sends the
--              user straight to this URL (e.g. a hosted payment page,
--              bKash personal link, SSLCommerz hosted checkout, etc).
-- buy_mode  -> 'internal' = use the site's own checkout/payment flow.
--              'external' = use buy_link instead.
-- ---------------------------------------------------------
CREATE TABLE books (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    subtitle VARCHAR(255) DEFAULT NULL,
    author VARCHAR(150) NOT NULL,
    description TEXT,
    category_id INT UNSIGNED DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    discount_price DECIMAL(10,2) DEFAULT NULL,
    cover VARCHAR(255) DEFAULT NULL,
    private_file_path VARCHAR(255) DEFAULT NULL,
    total_pages INT UNSIGNED DEFAULT 0,
    preview_pages INT UNSIGNED DEFAULT 4,
    buy_link VARCHAR(500) DEFAULT NULL,
    buy_mode ENUM('internal','external') NOT NULL DEFAULT 'internal',
    status ENUM('draft','published') NOT NULL DEFAULT 'draft',
    featured TINYINT(1) NOT NULL DEFAULT 0,
    bestseller TINYINT(1) NOT NULL DEFAULT 0,
    new_release TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_category (category_id),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Orders / Order items (internal checkout flow)
-- ---------------------------------------------------------
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    transaction_id VARCHAR(150) DEFAULT NULL,
    status ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_user (user_id),
    INDEX idx_txn (transaction_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    book_id INT UNSIGNED NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    INDEX idx_order (order_id),
    INDEX idx_book (book_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Purchases — this is the single source of truth the reader
-- and download-blocking middleware check against.
-- ---------------------------------------------------------
CREATE TABLE purchases (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    book_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED DEFAULT NULL,
    access_token VARCHAR(64) NOT NULL,
    purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL DEFAULT NULL,
    status ENUM('active','revoked') NOT NULL DEFAULT 'active',
    UNIQUE KEY uniq_user_book (user_id, book_id),
    INDEX idx_book (book_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Reading progress / sessions / bookmarks
-- ---------------------------------------------------------
CREATE TABLE reading_progress (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    book_id INT UNSIGNED NOT NULL,
    current_page INT UNSIGNED NOT NULL DEFAULT 1,
    percentage DECIMAL(5,2) NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_progress (user_id, book_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE reading_sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    book_id INT UNSIGNED NOT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP NULL DEFAULT NULL,
    duration INT UNSIGNED DEFAULT 0,
    last_page INT UNSIGNED DEFAULT 1,
    INDEX idx_user_book (user_id, book_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE bookmarks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    book_id INT UNSIGNED NOT NULL,
    page_number INT UNSIGNED NOT NULL,
    note VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_book (user_id, book_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Payments & activity log
-- ---------------------------------------------------------
CREATE TABLE payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED NOT NULL,
    gateway VARCHAR(50) NOT NULL,
    transaction_id VARCHAR(150) DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
    response_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE activity_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    book_id INT UNSIGNED DEFAULT NULL,
    metadata TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Site settings (key/value) — site name, watermark toggle,
-- manual payment (bKash/Nagad) receiving numbers, etc.
-- ---------------------------------------------------------
CREATE TABLE settings (
    `key` VARCHAR(100) PRIMARY KEY,
    `value` TEXT
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Manual payment claims — used when no automated gateway is
-- configured yet. The user pays via bKash/Nagad "Send Money"
-- to the number shown, then submits their sender number and
-- transaction ID here for an admin to verify before access
-- is granted. This avoids ever unlocking a book purely on
-- client-side/browser confirmation.
-- ---------------------------------------------------------
CREATE TABLE manual_payment_claims (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    method ENUM('bkash','nagad','rocket','bank') NOT NULL,
    sender_number VARCHAR(30) NOT NULL,
    transaction_id VARCHAR(100) NOT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    admin_note VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_order (order_id),
    INDEX idx_status (status),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Seed data
-- ---------------------------------------------------------
INSERT INTO categories (name, slug) VALUES
('উপন্যাস','uponnash'),
('আত্মউন্নয়ন','self-development'),
('মনোবিজ্ঞান','psychology'),
('নন-ফিকশন','non-fiction');

INSERT INTO settings (`key`, `value`) VALUES
('bkash_number', '01700000000'),
('nagad_number', '01700000000'),
('watermark_enabled', '1');

-- NOTE: No admin account is seeded here on purpose (a hardcoded password
-- hash in a public SQL file is a security risk). After importing this
-- schema, create your first admin by visiting /admin/setup.php once —
-- see the README for details. That page deletes/locks itself after use.