-- ==========================================================
--  Humble IT Solution — Admin Panel  :: MySQL Schema
--  Import:  mysql -u root -p humble_admin < schema.sql
-- ==========================================================

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

-- ---------- Admins ----------
CREATE TABLE IF NOT EXISTS `admins` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(100) NOT NULL,
  `email`         VARCHAR(150) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role`          ENUM('ADMIN','STAFF','VIEWER') NOT NULL DEFAULT 'STAFF',
  `created_at`    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Products ----------
CREATE TABLE IF NOT EXISTS `products` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(150) NOT NULL,
  `sku`        VARCHAR(60)  DEFAULT NULL,
  `price`      DECIMAL(10,2) NOT NULL DEFAULT 0,
  `mrp`        DECIMAL(10,2) NOT NULL DEFAULT 0,
  `stock`      INT NOT NULL DEFAULT 0,
  `is_active`  TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Orders ----------
CREATE TABLE IF NOT EXISTS `orders` (
  `id`             INT AUTO_INCREMENT PRIMARY KEY,
  `order_no`       VARCHAR(40) NOT NULL UNIQUE,
  `customer_name`  VARCHAR(150) NOT NULL,
  `phone`          VARCHAR(25)  NOT NULL,
  `district`       VARCHAR(80)  NOT NULL,
  `address`        TEXT,
  `product_id`     INT DEFAULT NULL,
  `quantity`       INT NOT NULL DEFAULT 1,
  `unit_price`     DECIMAL(10,2) NOT NULL DEFAULT 0,
  `delivery_fee`   DECIMAL(10,2) NOT NULL DEFAULT 0,
  `total`          DECIMAL(10,2) NOT NULL DEFAULT 0,
  `status`         ENUM('pending','confirmed','processing','shipped','delivered','cancelled')
                   NOT NULL DEFAULT 'pending',
  `courier`        VARCHAR(60) DEFAULT NULL,
  `tracking_no`    VARCHAR(80) DEFAULT NULL,
  `note`           TEXT,
  `created_at`     DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status`  (`status`),
  KEY `idx_created` (`created_at`),
  KEY `idx_district`(`district`),
  CONSTRAINT `fk_order_product` FOREIGN KEY (`product_id`)
    REFERENCES `products`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Abandoned leads (incomplete checkouts) ----------
CREATE TABLE IF NOT EXISTS `leads` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(150) DEFAULT NULL,
  `phone`      VARCHAR(25)  DEFAULT NULL,
  `district`   VARCHAR(80)  DEFAULT NULL,
  `source`     VARCHAR(60)  DEFAULT NULL,
  `recovered`  TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Stock movement log ----------
CREATE TABLE IF NOT EXISTS `stock_log` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL,
  `change_qty` INT NOT NULL,
  `reason`     VARCHAR(120) DEFAULT NULL,
  `admin_id`   INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==========================================================
--  Seed data
-- ==========================================================

-- password below is: admin123   (change it after first login!)
INSERT INTO `admins` (`name`,`email`,`password_hash`,`role`) VALUES
('Humble Studio','admin@humbleitsolution.com','$2y$10$z0OgmBb6xMsk.G43YIv6pe7lZy42tVcmRTQ98Zw3rRKaA/71L6yHq','ADMIN')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

INSERT INTO `products` (`id`,`name`,`sku`,`price`,`mrp`,`stock`) VALUES
(1,'HUMBLE Premium Pack','HIS-PREM-01',2000.00,2580.00,99)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

INSERT INTO `orders`
(`order_no`,`customer_name`,`phone`,`district`,`address`,`product_id`,`quantity`,`unit_price`,`delivery_fee`,`total`,`status`,`created_at`) VALUES
('HIS-2026-0001','MD Imran Sarker','01686569742','ঢাকা','13/1 Shajahan Road, Bonmala, Tongi',1,1,2000,60,2060,'pending','2026-10-17 11:24:00'),
('HIS-2026-0002','Rakib Hasan','01712345678','গাজীপুর','Board Bazar, Gazipur',1,2,2000,80,4080,'confirmed','2026-10-18 09:05:00'),
('HIS-2026-0003','Nusrat Jahan','01812345678','চট্টগ্রাম','Agrabad, Chattogram',1,1,2000,120,2120,'shipped','2026-10-18 15:40:00'),
('HIS-2026-0004','Sabbir Ahmed','01912345678','সিলেট','Zindabazar, Sylhet',1,1,2000,120,2120,'delivered','2026-10-19 10:12:00'),
('HIS-2026-0005','Tanvir Islam','01512345678','ঢাকা','Mirpur 10, Dhaka',1,3,2000,60,6060,'processing','2026-10-19 13:30:00')
ON DUPLICATE KEY UPDATE `customer_name`=VALUES(`customer_name`);

INSERT INTO `leads` (`name`,`phone`,`district`,`source`) VALUES
('Unknown','01622334455','ঢাকা','facebook'),
('Mehedi','01733445566','খুলনা','tiktok');