CREATE DATABASE IF NOT EXISTS y11acom_books CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE y11acom_books;
CREATE TABLE admins(id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(80) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE categories(id INT AUTO_INCREMENT PRIMARY KEY, name_ar VARCHAR(150) NOT NULL, name_fr VARCHAR(150), name_en VARCHAR(150), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE authors(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL, bio TEXT NULL);
CREATE TABLE books(
 id BIGINT AUTO_INCREMENT PRIMARY KEY, category_id INT NULL, author_id INT NULL,
 title_ar VARCHAR(255) NOT NULL, title_fr VARCHAR(255), title_en VARCHAR(255),
 description_ar TEXT, description_fr TEXT, description_en TEXT,
 cover_path VARCHAR(500), file_path VARCHAR(500) NOT NULL,
 file_type ENUM('pdf','epub') NOT NULL, access_type ENUM('free','paid') DEFAULT 'free',
 price DECIMAL(10,2) DEFAULT 0, language VARCHAR(10) DEFAULT 'ar',
 is_featured TINYINT(1) DEFAULT 0, views BIGINT DEFAULT 0, downloads BIGINT DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL,
 FOREIGN KEY(author_id) REFERENCES authors(id) ON DELETE SET NULL,
 INDEX(title_ar), INDEX(created_at), INDEX(is_featured)
);
CREATE TABLE app_users(id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE favorites(user_id BIGINT NOT NULL, book_id BIGINT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(user_id,book_id), FOREIGN KEY(user_id) REFERENCES app_users(id) ON DELETE CASCADE, FOREIGN KEY(book_id) REFERENCES books(id) ON DELETE CASCADE);
CREATE TABLE reading_progress(user_id BIGINT NOT NULL, book_id BIGINT NOT NULL, page_number INT DEFAULT 1, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(user_id,book_id));
INSERT INTO categories(name_ar,name_fr,name_en) VALUES
('روايات','Romans','Novels'),('دين','Religion','Religion'),('تاريخ','Histoire','History'),('علوم','Sciences','Science'),('تنمية بشرية','Développement personnel','Self development');
