CREATE DATABASE IF NOT EXISTS `gyaniguru` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `gyaniguru`;

-- Table for Categories
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for Books
CREATE TABLE IF NOT EXISTS `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `title_bengali` varchar(255) DEFAULT NULL,
  `author` varchar(100) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `original_price` decimal(10,2) NOT NULL,
  `image` varchar(255) NOT NULL,
  `rating` decimal(3,1) DEFAULT 4.5,
  `reviews` int(11) DEFAULT 0,
  `badge` varchar(50) DEFAULT NULL,
  `pages` int(11) DEFAULT NULL,
  `language` varchar(100) DEFAULT NULL,
  `binding` varchar(50) DEFAULT 'Paperback',
  `isbn` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `long_description` text DEFAULT NULL,
  `is_featured` tinyint(1) DEFAULT 0,
  `is_new` tinyint(1) DEFAULT 0,
  `is_upcoming` tinyint(1) DEFAULT 0,
  `release_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for Users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `pincode` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for Admins
CREATE TABLE IF NOT EXISTS `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for Orders
CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `payment_method` varchar(50) NOT NULL,
  `payment_status` enum('pending','paid','failed') DEFAULT 'pending',
  `status` enum('Pending','Processing','Shipped','Delivered','Cancelled') DEFAULT 'Pending',
  `shipping_address` text NOT NULL,
  `razorpay_order_id` varchar(100) DEFAULT NULL,
  `razorpay_payment_id` varchar(100) DEFAULT NULL,
  `razorpay_signature` varchar(255) DEFAULT NULL,
  `order_date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for Order Items
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default categories
INSERT INTO `categories` (`name`, `slug`, `icon`, `description`) VALUES
('Jyotish (Astrology)', 'jyotish', '🔭', 'Explore the ancient science of stars and planets.'),
('Dharma', 'dharma', '🙏', 'Sanatan Dharma and spiritual wisdom.'),
('Vastu Shastra', 'vastu', '🏠', 'The science of architecture and energy.'),
('Spirituality', 'adhyatma', '🧘', 'Inner journey and spiritual growth.'),
('Tantra-Mantra', 'tantra', '📿', 'Mystic sounds and spiritual practices.');

-- Insert default admin (password: admin123)
INSERT INTO `admins` (`username`, `password`, `name`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Admin');

-- Populate some books from the existing JS data
INSERT INTO `books` (`title`, `title_bengali`, `author`, `category_id`, `price`, `original_price`, `image`, `rating`, `reviews`, `badge`, `pages`, `language`, `isbn`, `description`, `long_description`, `is_featured`, `is_new`, `is_upcoming`, `release_date`) VALUES
('Jyotish Rahasya', 'জ্যোতিষ রহস্য', 'Pt. Shivaram Sharma', 1, 449.00, 699.00, 'images/book1.png', 4.8, 342, 'Bestseller', 384, 'Bengali & Hindi', '978-81-XXXX-001', 'The complete guide to Vedic Astrology.', 'This book presents the deepest secrets of Vedic Jyotish...', 1, 0, 0, NULL),
('Dharma Vigyan', 'ধর্ম বিজ্ঞান', 'Pt. Shivaram Sharma', 2, 399.00, 599.00, 'images/book2.png', 4.9, 218, 'New', 312, 'Bengali & Hindi', '978-81-XXXX-002', 'An explanation of the core principles of Sanatan Dharma.', 'Sanatan Dharma is an art of living...', 0, 1, 0, NULL),
('Vastu Vigyan', 'বাস্তু বিজ্ঞান', 'Pt. Shivaram Sharma', 3, 499.00, 799.00, 'images/book3.png', 4.7, 156, 'Popular', 256, 'Bengali & Hindi', '978-81-XXXX-003', 'Complete Vastu guide for home, shop and office.', 'Vastu Shastra is an ancient Indian science...', 1, 0, 0, NULL),
('Karma Chakra', 'কর্ম চক্র', 'Pt. Shivaram Sharma', 4, 349.00, 499.00, 'images/book4.png', 4.6, 289, 'Must Read', 208, 'Bengali & Hindi', '978-81-XXXX-004', 'What is Karma? What is the relationship between destiny and effort?', 'The principle of Karma is the foundational pillar...', 0, 0, 0, NULL),
('Shiva Tattva', 'শিব তত্ত্ব', 'Pt. Shivaram Sharma', 2, 0.00, 0.00, 'images/placeholder.png', 0.0, 0, 'Pre-order', 0, 'Bengali & Hindi', '978-81-XXXX-005', 'The ultimate truth of Shiva consciousness.', 'Delve deep into the mysteries of Shiva Tattva...', 0, 0, 1, '2026-08-15');

-- ==============================================================
-- Migration for existing databases: add upcoming book support
-- ==============================================================
-- Run this if upgrading an existing database:
-- ALTER TABLE books ADD COLUMN IF NOT EXISTS `is_upcoming` tinyint(1) DEFAULT 0 AFTER `is_new`;
-- ALTER TABLE books ADD COLUMN IF NOT EXISTS `release_date` date DEFAULT NULL AFTER `is_upcoming`;