--
-- Host: localhost    Database: pharmacy
-- ------------------------------------------------------


--
-- Table structure for table `addresses`
--

CREATE TABLE `addresses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `country` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `postcode` varchar(20) DEFAULT NULL,
  `address` text,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `addresses`
--

INSERT INTO `addresses` VALUES (1,'India','West Bengal','Kolkata','700001','Park Street',22.57260000,88.36390000,1,'2025-08-04 07:34:31','2025-08-04 07:34:31'),(2,'India','Maharashtra','Mumbai','400001','Marine Drive',18.93880000,72.83550000,1,'2025-08-04 07:34:31','2025-08-04 07:34:31'),(3,'India','Delhi','New Delhi','110001','Connaught Place',28.63150000,77.21670000,1,'2025-08-04 07:34:31','2025-08-04 07:34:31'),(4,'India','Karnataka','Bangalore','560001','MG Road',12.97160000,77.59460000,1,'2025-08-04 07:34:31','2025-08-04 07:34:31'),(5,'India','Tamil Nadu','Chennai','600001','Anna Salai',13.08270000,80.27070000,1,'2025-08-04 07:34:31','2025-08-04 07:34:31');

--
-- Table structure for table `bonuses`
--

CREATE TABLE `bonuses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `bonus_type` enum('percentage','fixed') DEFAULT 'percentage',
  `value` decimal(10,2) DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `bonuses`
--

INSERT INTO `bonuses` VALUES (1,'New Year Bonus','percentage',10.00,1,'2025-08-18 10:52:41','2025-08-18 10:52:41'),(2,'Festive Bonus','fixed',500.00,1,'2025-08-18 10:52:41','2025-08-18 10:52:41'),(3,'Performance Bonus','percentage',5.00,1,'2025-08-18 10:52:41','2025-08-18 10:52:41'),(4,'Referral Bonus','fixed',200.00,0,'2025-08-18 10:52:41','2025-08-18 10:52:41');

--
-- Table structure for table `brands`
--

CREATE TABLE `brands` (
  `id` int NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(100) DEFAULT NULL,
  `company_name` varchar(100) DEFAULT NULL,
  `images` text,
  `description` text,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `brands`
--

INSERT INTO `brands` VALUES (1,'MediPlus','PharmaCorp Ltd.',NULL,'Affordable generics all',1,'2025-08-04 07:34:52','2025-08-06 11:20:23'),(2,'HealFast','HealthCo',NULL,'Fast healing tablets',1,'2025-08-04 07:34:52','2025-08-04 07:34:52'),(3,'VitaGlow','VitaPharma',NULL,'Vitamins & supplements',1,'2025-08-04 07:34:52','2025-08-04 07:34:52'),(4,'SafeMeds','SafeLabs',NULL,'OTC medications',1,'2025-08-04 07:34:52','2025-08-04 07:34:52'),(5,'CureX','CureX Inc.',NULL,'Prescription medications',1,'2025-08-04 07:34:52','2025-08-04 07:34:52'),(6,'ronir','pvc 123',NULL,'delete',0,'2025-08-06 11:42:16','2025-08-06 12:11:20'),(7,'ronir ','ron',NULL,'nice',1,'2025-08-19 10:31:32','2025-09-02 08:11:38');

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `description` text,
  `code` varchar(50) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `subtitle` text,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` VALUES (1,'Pain Relief','Medicines for pain management all','CAT001',NULL,NULL,1,'2025-08-04 07:35:06','2025-08-06 11:53:02'),(2,'Antibiotics','Bacterial infection treatments','CAT002',NULL,NULL,1,'2025-08-04 07:35:06','2025-08-04 07:35:06'),(3,'Supplements','Vitamins and minerals','CAT003',NULL,NULL,1,'2025-08-04 07:35:06','2025-08-04 07:35:06'),(4,'Skincare','Dermatological products','CAT004',NULL,NULL,1,'2025-08-04 07:35:06','2025-08-04 07:35:06'),(5,'Cough & Cold','Syrups, tablets for flu','CAT005',NULL,NULL,1,'2025-08-04 07:35:06','2025-08-04 07:35:06'),(6,'ronir','delete it','CROSS',NULL,NULL,0,'2025-08-06 11:54:34','2025-08-06 12:11:53');

--
-- Table structure for table `category_order`
--

CREATE TABLE `category_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_id` int DEFAULT NULL,
  `display_order` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `category_order_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `category_order`
--

INSERT INTO `category_order` VALUES (1,2,1,'2025-09-03 08:37:55','2025-09-03 08:37:55'),(2,1,2,'2025-09-03 08:37:55','2025-09-03 08:37:55'),(3,3,3,'2025-09-03 08:37:55','2025-09-03 08:37:55'),(4,4,4,'2025-09-03 08:37:55','2025-09-03 08:37:55'),(5,5,5,'2025-09-03 08:37:55','2025-09-03 08:37:55');

--
-- Table structure for table `delivery_boy_cash`
--

CREATE TABLE `delivery_boy_cash` (
  `id` int NOT NULL AUTO_INCREMENT,
  `delivery_boy_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `final_total` decimal(10,2) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `type` enum('collection','adjustment') DEFAULT NULL,
  `bonus_id` int DEFAULT NULL,
  `message` text,
  `collected_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `delivery_boy_id` (`delivery_boy_id`),
  KEY `order_id` (`order_id`),
  KEY `bonus_id` (`bonus_id`),
  CONSTRAINT `delivery_boy_cash_ibfk_1` FOREIGN KEY (`delivery_boy_id`) REFERENCES `delivery_boys` (`id`) ON DELETE CASCADE,
  CONSTRAINT `delivery_boy_cash_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `delivery_boy_cash_ibfk_3` FOREIGN KEY (`bonus_id`) REFERENCES `bonuses` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `delivery_boy_cash`
--

INSERT INTO `delivery_boy_cash` VALUES (1,1,1,190.00,190.00,'collection',1,'Cash collected from user for ORD1001','2025-08-18 10:54:40'),(2,2,2,475.00,450.00,'collection',2,'Partial cash collected for ORD1002','2025-08-18 10:54:40'),(3,3,3,157.50,160.00,'collection',3,'Rounded up collection for ORD1003','2025-08-18 10:54:40');

--
-- Table structure for table `delivery_boy_funds`
--

CREATE TABLE `delivery_boy_funds` (
  `id` int NOT NULL AUTO_INCREMENT,
  `delivery_boy_id` int DEFAULT NULL,
  `opening_balance` decimal(10,2) DEFAULT NULL,
  `closing_balance` decimal(10,2) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `type` enum('credit','debit') DEFAULT NULL,
  `message` text,
  `status` enum('pending','completed','failed') DEFAULT 'pending',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `delivery_boy_id` (`delivery_boy_id`),
  CONSTRAINT `delivery_boy_funds_ibfk_1` FOREIGN KEY (`delivery_boy_id`) REFERENCES `delivery_boys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `delivery_boy_funds`
--

INSERT INTO `delivery_boy_funds` VALUES (1,1,0.00,500.00,500.00,'credit','Initial fund allocation','completed','2025-08-18 10:54:24'),(2,2,200.00,150.00,50.00,'debit','Penalty for late delivery','completed','2025-08-18 10:54:24'),(3,3,0.00,300.00,300.00,'credit','Festival advance','pending','2025-08-18 10:54:24');

--
-- Table structure for table `discounts`
--

CREATE TABLE `discounts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `type` enum('percentage','fixed') DEFAULT NULL,
  `value` decimal(10,2) DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `discounts`
--

INSERT INTO `discounts` VALUES (1,'New User Discount','percentage',10.00,1,'2025-08-04 07:41:31','2025-08-04 07:41:31'),(2,'Seasonal Sale','percentage',15.00,1,'2025-08-04 07:41:31','2025-08-04 07:41:31'),(3,'Flat ₹50 Off','fixed',50.00,1,'2025-08-04 07:41:31','2025-08-04 07:41:31'),(4,'Loyalty Offer','percentage',5.00,1,'2025-08-04 07:41:31','2025-08-04 07:41:31'),(5,'No Discount','fixed',0.00,1,'2025-08-04 07:41:31','2025-08-04 07:41:31'),(6,'ronir','percentage',1234.00,0,'2025-08-06 12:03:17','2025-08-06 12:12:24'),(7,'ERTR','fixed',100.00,0,'2025-08-19 11:14:51','2025-08-19 11:15:28'),(8,'100 UP ','fixed',100.00,0,'2025-08-19 11:15:54','2025-08-19 11:16:18');

--
-- Table structure for table `item_order`
--

CREATE TABLE `item_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_id` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `display_order` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `item_order_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `item_order_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `item_order`
--

INSERT INTO `item_order` VALUES (3,1,17,1,'2025-09-03 08:39:33','2025-09-03 08:39:33'),(4,1,11,2,'2025-09-03 08:39:33','2025-09-03 08:39:33');

--
-- Table structure for table `items`
--

CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `brand_id` int DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  `unit_id` int DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `hsn` varchar(100) DEFAULT NULL,
  `minimum_qty` int DEFAULT NULL,
  `expire_date` date DEFAULT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `description` text,
  `image` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `tax_id` int DEFAULT NULL,
  `tax_type` enum('inclusive','exclusive') DEFAULT 'exclusive',
  `sales_price` decimal(10,2) DEFAULT NULL,
  `discount_id` int DEFAULT NULL,
  `store_id` int DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `brand_id` (`brand_id`),
  KEY `category_id` (`category_id`),
  KEY `unit_id` (`unit_id`),
  KEY `tax_id` (`tax_id`),
  KEY `discount_id` (`discount_id`),
  KEY `store_id` (`store_id`),
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`),
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`),
  CONSTRAINT `items_ibfk_4` FOREIGN KEY (`tax_id`) REFERENCES `taxes` (`id`),
  CONSTRAINT `items_ibfk_5` FOREIGN KEY (`discount_id`) REFERENCES `discounts` (`id`),
  CONSTRAINT `items_ibfk_6` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `items`
--

INSERT INTO `items` VALUES (11,'Sanir 500mg',1,1,1,'SKU001','HSN001',10,'2026-12-31','BAR001','Pain reliever','image1.jpg',50.00,1,'exclusive',55.00,1,1,1,'2025-08-04 07:41:43','2025-09-02 14:52:40'),(12,'Amoxicillin 250mg',2,2,1,'SKU002','HSN002',20,'2025-11-30','BAR002','Antibiotic capsule','image2.jpg',80.00,2,'inclusive',80.00,2,2,1,'2025-08-04 07:41:43','2025-09-02 14:52:41'),(13,'Vitamin WEXX',3,3,2,'SKU003','HSN003',15,'2026-06-30','BAR003','Bone strength','image3.jpg',120.00,3,'exclusive',135.00,3,3,1,'2025-08-04 07:41:43','2025-09-02 14:52:41'),(14,'Cough Syrup',4,5,3,'SKU004','HSN004',5,'2025-10-15','BAR004','Cold relief syrup','image4.jpg',60.00,1,'inclusive',60.00,5,4,1,'2025-08-04 07:41:43','2025-09-02 14:52:41'),(15,'Aloe Vera Gel',5,4,4,'SKU005','HSN005',12,'2027-01-01','BAR005','Skincare gel','image5.jpg',90.00,1,'exclusive',95.00,4,5,1,'2025-08-04 07:41:43','2025-09-02 14:52:42'),(16,'SK Ronir Rahaman',1,5,1,'12345','1111111',12,'2000-01-11','12345678','text you','https://ronirrahaman.github.io/Myportfolio/girl.png',120.00,1,'exclusive',150.00,1,1,0,'2025-08-06 12:14:53','2025-08-06 12:15:32'),(17,'Ronir Rahaman',1,1,1,'12345','1111111',12,'2026-09-11','12345678','This is for testing','https://ronirrahaman.github.io/Myportfolio/girl.png',100.00,1,'exclusive',120.00,3,1,1,'2025-08-06 12:17:12','2025-09-02 14:52:39'),(18,'sanir',7,4,7,'WE/787/QQ','1111111',12,'2027-10-12','12345678','rprrpr','',900.00,1,'inclusive',1000.00,1,3,0,'2025-08-19 10:35:01','2025-08-19 10:38:00');

--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `total_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `order_items`
--

INSERT INTO `order_items` VALUES (1,1,11,2,20.00,40.00),(2,1,12,1,150.00,150.00),(3,2,13,3,100.00,300.00),(4,2,14,2,75.00,150.00),(5,3,15,1,120.00,120.00),(6,3,12,2,15.00,30.00),(7,4,11,5,20.00,100.00),(8,4,14,2,100.00,200.00);

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_number` varchar(50) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `store_id` int DEFAULT NULL,
  `delivery_boy_id` int DEFAULT NULL,
  `prescription_photo` varchar(255) DEFAULT NULL,
  `total_amount` decimal(10,2) DEFAULT NULL,
  `discount_amount` decimal(10,2) DEFAULT '0.00',
  `tax_amount` decimal(10,2) DEFAULT '0.00',
  `final_total` decimal(10,2) DEFAULT NULL,
  `payment_status` enum('pending','paid','failed','refunded') DEFAULT 'pending',
  `payment_method` enum('cash','online','card','wallet') DEFAULT NULL,
  `order_status` enum('pending','processing','shipped','delivered','cancelled','returned') DEFAULT 'pending',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`),
  KEY `user_id` (`user_id`),
  KEY `store_id` (`store_id`),
  KEY `delivery_boy_id` (`delivery_boy_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`delivery_boy_id`) REFERENCES `delivery_boys` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` VALUES (1,'ORD1001',1,1,1,'presc_1001.jpg',200.00,20.00,10.00,190.00,'paid','cash','delivered','2025-08-18 10:53:19','2025-08-18 10:53:19'),(2,'ORD1002',2,2,2,'presc_1002.jpg',500.00,50.00,25.00,475.00,'paid','online','shipped','2025-08-18 10:53:19','2025-08-18 10:53:19'),(3,'ORD1003',3,3,3,'presc_1003.jpg',150.00,0.00,7.50,157.50,'pending','card','processing','2025-08-18 10:53:19','2025-08-18 10:53:19'),(4,'ORD1004',4,4,1,'presc_1004.jpg',300.00,30.00,15.00,285.00,'failed','wallet','cancelled','2025-08-18 10:53:19','2025-08-18 10:53:19');

--
-- Table structure for table `store_users`
--

CREATE TABLE `store_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `store_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `role` enum('picker','putter','pharmacist','auditor','delivery') DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `store_id` (`store_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `store_users_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE,
  CONSTRAINT `store_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `store_users`
--

INSERT INTO `store_users` VALUES (1,1,3,'picker',1,'2025-08-04 07:36:42','2025-08-04 07:36:42'),(2,2,4,'putter',1,'2025-08-04 07:36:42','2025-08-04 07:36:42'),(3,3,5,'pharmacist',1,'2025-08-04 07:36:42','2025-08-04 07:36:42'),(4,4,6,'auditor',1,'2025-08-04 07:36:42','2025-08-04 07:36:42'),(5,5,7,'delivery',1,'2025-08-04 07:36:42','2025-08-04 07:36:42');

--
-- Table structure for table `stores`
--

CREATE TABLE `stores` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `location` text,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `stores`
--

INSERT INTO `stores` VALUES (1,'Store A','Kolkata Center',22.57260000,88.36390000,1,'2025-08-04 07:35:39','2025-08-04 07:35:39'),(2,'Store B','Mumbai Downtown',18.93880000,72.83550000,1,'2025-08-04 07:35:39','2025-08-04 07:35:39'),(3,'Store C','Delhi Central',28.63150000,77.21670000,1,'2025-08-04 07:35:39','2025-08-04 07:35:39'),(4,'Store D','Bangalore North',12.97160000,77.59460000,1,'2025-08-04 07:35:39','2025-08-04 07:35:39'),(5,'Store E','Chennai South',13.08270000,80.27070000,1,'2025-08-04 07:35:39','2025-08-04 07:35:39'),(11,'ronir','hatiara 21',22.45650000,-75.55500000,0,'2025-08-06 12:07:07','2025-08-06 12:12:33'),(12,'ronir','Washington, DC',NULL,NULL,1,'2025-08-19 11:21:05','2025-08-19 11:21:05');

--
-- Table structure for table `taxes`
--

CREATE TABLE `taxes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `percentage` decimal(5,2) DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `taxes`
--

INSERT INTO `taxes` VALUES (1,'GST 5%',5.00,1,'2025-08-04 07:35:28','2025-08-06 12:01:08'),(2,'GST 12%',12.00,1,'2025-08-04 07:35:28','2025-08-04 07:35:28'),(3,'GST 18%',18.00,1,'2025-08-04 07:35:28','2025-08-04 07:35:28'),(4,'VAT 10%',10.00,1,'2025-08-04 07:35:28','2025-08-04 07:35:28'),(5,'GST 0%',0.00,1,'2025-08-04 07:35:28','2025-08-04 07:35:28'),(6,'ronir',66.00,0,'2025-08-06 12:01:18','2025-08-06 12:12:14');

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `transaction_type` enum('credit','debit') DEFAULT NULL,
  `transaction_id` varchar(100) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `message` text,
  `transaction_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` enum('pending','completed','failed') DEFAULT 'pending',
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_id` (`transaction_id`),
  KEY `user_id` (`user_id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `transactions`
--

INSERT INTO `transactions` VALUES (1,8,1,'credit','TXN1001',250.00,'Order 1 payment received','2025-09-03 10:21:31','completed'),(2,8,2,'debit','TXN1002',120.50,'Refund for Order 2','2025-09-03 10:21:31','completed'),(3,8,3,'credit','TXN1003',450.75,'Order 3 payment received','2025-09-03 10:21:31','pending'),(4,8,4,'credit','TXN1004',99.99,'Order 4 payment received','2025-09-03 10:21:31','failed');

--
-- Table structure for table `units`
--

CREATE TABLE `units` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `short_code` varchar(10) DEFAULT NULL,
  `remark` text,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `units`
--

INSERT INTO `units` VALUES (1,'Tablet','TAB','Used for oral tablets',1,'2025-08-04 07:35:18','2025-08-04 07:35:18'),(2,'Capsule','CAP','Used for capsules',1,'2025-08-04 07:35:18','2025-08-04 07:35:18'),(3,'Millilitre','ML','Liquid medicine',1,'2025-08-04 07:35:18','2025-08-04 07:35:18'),(4,'Gram','GM','Powder or solid',1,'2025-08-04 07:35:18','2025-08-04 07:35:18'),(5,'Bottle','BTL','For bottled liquids',1,'2025-08-04 07:35:18','2025-08-04 07:35:18'),(6,'ronir','KGW 22','delete it',0,'2025-08-06 11:57:17','2025-08-06 12:12:03'),(7,'ronir-1','KG','nonon',1,'2025-08-19 10:32:03','2025-08-19 10:32:03');

--
-- Table structure for table `user_addresses`
--

CREATE TABLE `user_addresses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `address_id` int DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `address_id` (`address_id`),
  CONSTRAINT `user_addresses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `user_addresses_ibfk_2` FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user_addresses`
--

INSERT INTO `user_addresses` VALUES (1,1,1,1,'2025-08-04 07:34:41','2025-08-04 07:34:41'),(2,2,2,1,'2025-08-04 07:34:41','2025-08-04 07:34:41'),(3,3,3,1,'2025-08-04 07:34:41','2025-08-04 07:34:41'),(4,4,4,1,'2025-08-04 07:34:41','2025-08-04 07:34:41'),(5,5,5,1,'2025-08-04 07:34:41','2025-08-04 07:34:41');

--
-- Table structure for table `wishlists`
--

CREATE TABLE `wishlists` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `quantity` int DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `wishlists_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `wishlists_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `wishlists`
--

INSERT INTO `wishlists` VALUES (1,8,11,1,'2025-09-03 10:18:58','2025-09-03 10:18:58',1),(2,8,12,2,'2025-09-03 10:18:58','2025-09-03 10:18:58',1),(3,8,13,1,'2025-09-03 10:18:58','2025-09-03 10:18:58',1),(4,8,14,3,'2025-09-03 10:18:58','2025-09-03 10:18:58',1),(5,8,15,1,'2025-09-03 10:18:58','2025-09-03 10:18:58',1),(6,8,16,2,'2025-09-03 10:18:58','2025-09-03 10:18:58',1),(7,8,17,1,'2025-09-03 10:18:58','2025-09-03 10:18:58',1),(8,8,18,4,'2025-09-03 10:18:58','2025-09-03 10:18:58',1);


