-- ============================================================
-- বাংলা ই-কমার্স ডেটাবেজ  (v2 — আপডেটেড)
-- Encoding: utf8mb4  |  Collation: utf8mb4_unicode_ci
-- ============================================================

SET SQL_MODE   = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone  = "+06:00";
SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `bengali_shop`
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `bengali_shop`;

-- ============================================================
-- 1. USERS
-- role: 'user' | 'admin' | 'investor'
-- share_percent: শুধু investor রোলের জন্য (0–100)
-- ============================================================
CREATE TABLE `users` (
  `id`            int(11)      NOT NULL AUTO_INCREMENT,
  `name`          varchar(255) NOT NULL,
  `phone`         varchar(20)  NOT NULL,
  `address`       text         DEFAULT NULL,
  `email`         varchar(255) DEFAULT NULL,
  `password`      varchar(255) NOT NULL,
  `role`          enum('user','admin','investor') NOT NULL DEFAULT 'user',
  `share_percent` decimal(5,2) NOT NULL DEFAULT '0.00',   -- ← নতুন কলাম
  `created_at`    datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. CATEGORIES
-- ============================================================
CREATE TABLE `categories` (
  `id`     int(11)      NOT NULL AUTO_INCREMENT,
  `name`   varchar(255) NOT NULL,
  `slug`   varchar(255) NOT NULL,
  `status` tinyint(1)   NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. PRODUCTS
-- ============================================================
CREATE TABLE `products` (
  `id`          int(11)        NOT NULL AUTO_INCREMENT,
  `name`        varchar(255)   NOT NULL,
  `slug`        varchar(255)   NOT NULL,
  `description` text           DEFAULT NULL,
  `price`       decimal(10,2)  NOT NULL DEFAULT '0.00',
  `stock`       int(11)        NOT NULL DEFAULT '0',
  `category_id` int(11)        DEFAULT NULL,
  `image`       varchar(255)   DEFAULT NULL,
  `is_flash`    tinyint(1)     NOT NULL DEFAULT '0',
  `rating`      decimal(3,2)   NOT NULL DEFAULT '0.00',
  `created_at`  datetime       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. PRODUCT VARIATIONS
-- ============================================================
CREATE TABLE `product_variations` (
  `id`              int(11)       NOT NULL AUTO_INCREMENT,
  `product_id`      int(11)       NOT NULL,
  `variation_name`  varchar(100)  NOT NULL,   -- যেমন: সাইজ, রঙ
  `variation_value` varchar(255)  NOT NULL,   -- যেমন: S, M, L / লাল, নীল
  `extra_price`     decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. ORDERS
-- ============================================================
CREATE TABLE `orders` (
  `id`               int(11)       NOT NULL AUTO_INCREMENT,
  `user_id`          int(11)       DEFAULT NULL,   -- NULL = গেস্ট চেকআউট
  `customer_name`    varchar(255)  NOT NULL,
  `customer_phone`   varchar(20)   NOT NULL,
  `customer_address` text          NOT NULL,
  `inside_dhaka`     tinyint(1)    NOT NULL DEFAULT '0',
  `total_amount`     decimal(10,2) NOT NULL DEFAULT '0.00',
  `delivery_charge`  decimal(10,2) NOT NULL DEFAULT '0.00',
  `status`           enum('pending','processing','shipped','delivered','cancelled')
                                   NOT NULL DEFAULT 'pending',
  `created_at`       datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status`  (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. ORDER ITEMS
-- ============================================================
CREATE TABLE `order_items` (
  `id`             int(11)       NOT NULL AUTO_INCREMENT,
  `order_id`       int(11)       NOT NULL,
  `product_id`     int(11)       NOT NULL,
  `variation_info` text          DEFAULT NULL,   -- যেমন: "সাইজ: M, রঙ: লাল"
  `quantity`       int(11)       NOT NULL DEFAULT '1',
  `price`          decimal(10,2) NOT NULL DEFAULT '0.00',   -- variation extra সহ একক মূল্য
  PRIMARY KEY (`id`),
  KEY `order_id`   (`order_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. REVIEWS
-- ============================================================
CREATE TABLE `reviews` (
  `id`         int(11)   NOT NULL AUTO_INCREMENT,
  `product_id` int(11)   NOT NULL,
  `user_id`    int(11)   NOT NULL,
  `rating`     tinyint(1) NOT NULL DEFAULT '5',
  `comment`    text      DEFAULT NULL,
  `status`     enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `created_at` datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `user_id`    (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. SETTINGS  (key-value store)
-- ============================================================
CREATE TABLE `settings` (
  `id`            int(11)      NOT NULL AUTO_INCREMENT,
  `setting_key`   varchar(100) NOT NULL,
  `setting_value` text         DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. MENUS  (হেডার মেনু)
-- ============================================================
CREATE TABLE `menus` (
  `id`       int(11)      NOT NULL AUTO_INCREMENT,
  `name`     varchar(255) NOT NULL,
  `link`     varchar(500) NOT NULL,
  `position` int(11)      NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. FLASH SALE PRODUCTS
-- ============================================================
CREATE TABLE `flash_sale_products` (
  `id`          int(11) NOT NULL AUTO_INCREMENT,
  `product_id`  int(11) NOT NULL,
  `flash_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. INVESTOR TRANSACTIONS
-- amount > 0 → ইনভেস্টরকে দেওয়া হয়েছে (প্রদত্ত)
-- amount < 0 → ইনভেস্টর থেকে নেওয়া হয়েছে (গৃহীত)
-- ============================================================
CREATE TABLE `investor_transactions` (
  `id`               int(11)        NOT NULL AUTO_INCREMENT,
  `investor_id`      int(11)        NOT NULL,
  `amount`           decimal(12,2)  NOT NULL,
  `note`             text           DEFAULT NULL,
  `transaction_date` date           NOT NULL,
  PRIMARY KEY (`id`),
  KEY `investor_id` (`investor_id`),
  KEY `transaction_date` (`transaction_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. EXPENSES  (বিজ্ঞাপন, বেতন, সার্ভার ইত্যাদি)
-- ============================================================
CREATE TABLE `expenses` (
  `id`           int(11)       NOT NULL AUTO_INCREMENT,
  `title`        varchar(255)  NOT NULL,
  `category`     varchar(100)  DEFAULT NULL,
  `amount`       decimal(10,2) NOT NULL DEFAULT '0.00',
  `expense_date` date          NOT NULL,
  `note`         text          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `expense_date` (`expense_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FOREIGN KEYS
-- ============================================================
ALTER TABLE `products`
  ADD CONSTRAINT `fk_product_category`
    FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `product_variations`
  ADD CONSTRAINT `fk_variation_product`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `orders`
  ADD CONSTRAINT `fk_order_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `order_items`
  ADD CONSTRAINT `fk_item_order`
    FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_item_product`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `reviews`
  ADD CONSTRAINT `fk_review_product`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_review_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `flash_sale_products`
  ADD CONSTRAINT `fk_flash_product`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `investor_transactions`
  ADD CONSTRAINT `fk_txn_investor`
    FOREIGN KEY (`investor_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

-- ============================================================
-- DEFAULT DATA
-- ============================================================

-- সাইট সেটিংস
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name',              'বাংলা শপ'),
('site_logo',              ''),
('contact_phone',          '01700000000'),
('contact_email',          'info@bangla-shop.com'),
('contact_address',        'ঢাকা, বাংলাদেশ'),
('inside_dhaka_charge',    '60'),
('outside_dhaka_charge',   '120'),
('review_enabled',         '1'),
('dhaka_checkbox_enabled', '1'),
('banner_image',           '');

-- ডিফল্ট অ্যাডমিন (password: admin123)
-- লগইনের পরে অবশ্যই পাসওয়ার্ড পরিবর্তন করুন!
INSERT INTO `users` (`name`, `phone`, `email`, `password`, `role`, `share_percent`) VALUES
('Admin', '01700000001', 'admin@shop.com',
 '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
 'admin', 0.00);

-- ডিফল্ট মেনু
INSERT INTO `menus` (`name`, `link`, `position`) VALUES
('হোম',           'index.php',    1),
('পণ্যসমূহ',     'category.php', 2),
('আমাদের সম্পর্কে', '#about',   3),
('যোগাযোগ',      '#contact',     4);

-- স্যাম্পল ক্যাটেগরি
INSERT INTO `categories` (`name`, `slug`, `status`) VALUES
('পোশাক',        'poshak',       1),
('ইলেকট্রনিক্স', 'electronics',  1),
('গৃহস্থালি',    'grihosthali',  1);


-- ============================================================
-- আপগ্রেড স্ক্রিপ্ট (যদি পুরনো v1 ডেটাবেজ থাকে)
-- নতুন ইনস্টলে এটা দরকার নেই — শুধু ALTER TABLE চালান
-- ============================================================
-- ALTER TABLE `users` ADD COLUMN `share_percent` decimal(5,2) NOT NULL DEFAULT '0.00' AFTER `role`;
-- UPDATE `users` SET `share_percent` = CAST(address AS DECIMAL(5,2)) WHERE role = 'investor';
-- ============================================================

-- ============================================================
-- v4 ADDITIONS
-- ============================================================

-- Steadfast courier & Facebook Pixel settings
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES
('steadfast_api_key',    ''),
('steadfast_api_secret', ''),
('steadfast_enabled',    '0'),
('fb_pixel_id',          ''),
('fb_pixel_enabled',     '0');

-- Offline sale order type support
-- Add order_type column to orders table
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS
  `order_type` enum('online','offline') NOT NULL DEFAULT 'online' AFTER `status`;

-- Steadfast consignment tracking
CREATE TABLE IF NOT EXISTS `courier_logs` (
  `id`              int(11)      NOT NULL AUTO_INCREMENT,
  `order_id`        int(11)      NOT NULL,
  `consignment_id`  varchar(100) DEFAULT NULL,
  `tracking_code`   varchar(100) DEFAULT NULL,
  `courier_status`  varchar(50)  DEFAULT NULL,
  `response`        text         DEFAULT NULL,
  `created_at`      datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- COUPONS TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS `coupons` (
  `id`                int(11)        NOT NULL AUTO_INCREMENT,
  `code`              varchar(50)    NOT NULL,
  `discount_type`     enum('percent','fixed') NOT NULL DEFAULT 'percent',
  `discount_value`    decimal(10,2)  NOT NULL DEFAULT '0.00',
  `min_order_amount`  decimal(10,2)  NOT NULL DEFAULT '0.00',
  `max_discount`      decimal(10,2)  NOT NULL DEFAULT '0.00',
  `usage_limit`       int(11)        NOT NULL DEFAULT '0',
  `used_count`        int(11)        NOT NULL DEFAULT '0',
  `expiry_date`       date           DEFAULT NULL,
  `is_active`         tinyint(1)     NOT NULL DEFAULT '1',
  `created_at`        datetime       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample coupons
INSERT IGNORE INTO `coupons` (`code`,`discount_type`,`discount_value`,`min_order_amount`,`max_discount`,`usage_limit`,`is_active`) VALUES
('EID20',   'percent', 20.00, 500.00, 200.00, 100, 1),
('SAVE50',  'fixed',   50.00, 300.00,   0.00,  50, 1),
('WELCOME', 'percent', 10.00,   0.00, 100.00,   0, 1);

-- ============================================================
-- UPGRADE SCRIPT (if upgrading from older version)
-- ============================================================
-- ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `order_type` enum('online','offline') NOT NULL DEFAULT 'online' AFTER `status`;
-- ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `delivery_charge` decimal(10,2) NOT NULL DEFAULT '0.00' AFTER `total_amount`;
-- CREATE TABLE IF NOT EXISTS `courier_logs` (...) -- see above
