--
-- Host: 127.0.0.1    Database: myfairlady_new
-- ------------------------------------------------------


--
-- Table structure for table `bill_counters`
--

CREATE TABLE `bill_counters` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `financial_year` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Format: YY-YY (e.g., 24-25)',
  `last_bill_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Last generated bill number',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bill_counters_financial_year_unique` (`financial_year`),
  KEY `bill_counters_financial_year_index` (`financial_year`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bill_counters`
--

INSERT INTO `bill_counters` VALUES (1,'25-26','MFL/25-26/00105','2025-10-27 03:39:59','2025-10-27 03:40:48');

--
-- Table structure for table `bill_items`
--

CREATE TABLE `bill_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `bill_id` bigint unsigned NOT NULL,
  `product_id` int unsigned NOT NULL,
  `product_description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `our_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `qty` int NOT NULL DEFAULT '1',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bill_items_bill_id_foreign` (`bill_id`),
  KEY `bill_items_product_id_foreign` (`product_id`),
  CONSTRAINT `bill_items_bill_id_foreign` FOREIGN KEY (`bill_id`) REFERENCES `bills` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bill_items_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bill_items`
--

INSERT INTO `bill_items` VALUES (1,94,1,'MFL00001 - N/A',250.00,210.00,5,1050.00,'2025-08-27 05:07:31','2025-08-27 05:07:31'),(2,95,1,'MFL00001 - N/A',250.00,210.00,1,210.00,'2025-08-27 05:21:16','2025-08-27 05:21:16'),(3,95,6,'25JUL00006 - N/A',600.00,550.00,5,2750.00,'2025-08-27 05:21:16','2025-08-27 05:21:16'),(4,96,1,'MFL00001 - N/A',250.00,210.00,2,420.00,'2025-08-27 06:22:54','2025-08-27 06:22:54'),(5,96,6,'25JUL00006 - N/A',600.00,550.00,5,2750.00,'2025-08-27 06:22:54','2025-08-27 06:22:54'),(6,97,1,'MFL00001 - N/A',250.00,210.00,1,210.00,'2025-09-08 06:44:06','2025-09-08 06:44:06'),(7,98,1,'MFL00001 - N/A',250.00,210.00,1,210.00,'2025-09-15 04:13:42','2025-09-15 04:13:42'),(8,99,1,'MFL00001 - N/A',250.00,210.00,1,210.00,'2025-09-15 05:58:47','2025-09-15 05:58:47'),(9,99,6,'25JUL00006',600.00,550.00,-1,-550.00,'2025-09-15 05:58:47','2025-09-15 05:58:47'),(10,100,1,'MFL00001 - N/A',250.00,210.00,1,210.00,'2025-10-27 03:26:02','2025-10-27 03:26:02'),(11,101,2,'MFL00002 - N/A',250.00,200.00,1,200.00,'2025-10-27 03:26:27','2025-10-27 03:26:27');

--
-- Table structure for table `bills`
--

CREATE TABLE `bills` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint unsigned NOT NULL DEFAULT '0',
  `bill_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bill_date` date NOT NULL,
  `total_qty` int NOT NULL DEFAULT '0',
  `total_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `discount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tax_percentage` decimal(5,2) NOT NULL DEFAULT '0.00',
  `final_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bills_bill_no_unique` (`bill_no`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bills`
--

INSERT INTO `bills` VALUES (94,0,'MFL/25-26/00001','2025-08-27',5,1050.00,0.00,5.00,210.00,'2025-08-27 05:07:31','2025-08-27 05:07:31'),(95,0,'MFL/25-26/00095','2025-08-27',6,2960.00,0.00,5.00,760.00,'2025-08-27 05:21:16','2025-08-27 05:21:16'),(96,0,'MFL/25-26/00096','2025-08-27',7,3170.00,50.00,5.00,3165.00,'2025-08-27 06:22:54','2025-08-27 06:22:54'),(97,0,'MFL/25-26/00097','2025-09-08',1,210.00,0.00,5.00,0.00,'2025-09-08 06:44:06','2025-09-08 06:44:06'),(98,0,'MFL/25-26/00098','2025-09-15',1,210.00,0.00,5.00,210.00,'2025-09-15 04:13:42','2025-09-15 04:13:42'),(99,0,'MFL/25-26/00099','2025-09-15',0,-340.00,0.00,5.00,210.00,'2025-09-15 05:58:47','2025-09-15 05:58:47'),(100,0,'MFL/25-26/00100','2025-10-27',1,210.00,0.00,5.00,0.00,'2025-10-27 03:26:02','2025-10-27 03:26:02'),(101,0,'MFL/25-26/00101','2025-10-27',1,200.00,0.00,5.00,0.00,'2025-10-27 03:26:27','2025-10-27 03:26:27');

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

CREATE TABLE `brands` (
  `brand_id` int unsigned NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active_status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `brands` VALUES (1,'Max',1,'2025-07-17 04:15:56','2025-07-17 04:19:10'),(2,'Pepe Jeans',1,'2022-09-02 07:26:10','2022-09-06 12:49:23'),(3,'Prisma',1,'2022-09-02 07:26:10','2022-09-06 12:49:23'),(4,'Jack',1,'2022-09-02 07:26:10','2022-09-06 12:49:23'),(5,'Louis Philippe',1,'2022-09-02 07:26:15','2022-09-06 12:49:11'),(6,'Blackberrys',1,'2022-09-02 07:26:20','2022-09-06 12:48:56'),(7,'Arrow',1,'2022-09-02 07:26:25','2023-01-22 19:35:11'),(8,'Levi\'s',1,'2022-09-02 07:26:30','2022-09-06 12:48:38'),(9,'Nike',0,'2022-09-07 05:19:33','2022-10-13 18:35:07'),(10,'SONARI',1,'2024-11-29 14:02:49','2024-11-29 08:32:49'),(11,'TRYLO',1,'2024-11-29 14:02:59','2024-11-29 08:32:59'),(12,'RIZA',1,'2024-11-29 14:03:07','2024-11-29 08:33:07'),(13,'LOVABLE',1,'2024-11-29 14:07:14','2024-11-29 08:37:14'),(14,'ANGELFORM',1,'2024-11-29 14:07:21','2024-11-29 08:37:21');

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

CREATE TABLE `categories` (
  `category_id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active_status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `categories` VALUES (1,'Bra',1,'2025-07-17 03:58:39','2025-07-18 03:52:26'),(2,'T-shirt',1,'2025-07-17 06:41:03','2025-07-17 06:41:03'),(3,'Panties',1,'2025-07-18 04:00:17','2025-07-18 04:00:17');

--
-- Table structure for table `colors`
--

CREATE TABLE `colors` (
  `color_id` int unsigned NOT NULL AUTO_INCREMENT,
  `color_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active_status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`color_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `colors`
--

INSERT INTO `colors` VALUES (1,'Red',1,'2025-07-17 04:23:42','2025-07-17 04:24:00'),(2,'Orange',1,'2025-07-22 00:48:27','2025-07-22 00:48:27');

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `customer_id` int unsigned NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile_number` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` VALUES (1,'suresh','9687542100',NULL,NULL),(2,'sureshgobi','9999999997','2025-07-22 05:14:12','2025-07-22 05:14:12'),(3,'salman','8888888887','2025-07-22 05:21:09','2025-07-22 05:21:09');

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `failed_jobs`
--


--
-- Table structure for table `job_batches`
--

CREATE TABLE `job_batches` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` mediumtext COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `job_batches`
--


--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `jobs`
--


--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` VALUES (1,'0001_01_01_000001_create_cache_table',1),(2,'0001_01_01_000002_create_jobs_table',1),(3,'2023_01_01_000000_create_roles_table',2),(4,'2014_10_12_000000_create_users_table',3),(5,'2023_01_01_000001_create_categories_table',3),(6,'2023_01_01_000002_create_brands_table',3),(7,'2023_01_01_000003_create_colors_table',3),(8,'2023_01_01_000004_create_styles_table',3),(9,'2023_01_01_000005_create_sizes_table',3),(10,'2023_01_01_000006_create_products_table',3),(11,'2023_01_01_000007_create_bills_table',4),(12,'2023_01_01_000008_create_bill_items_table',4),(13,'2023_01_01_000009_create_customers_table',5),(14,'2025_07_18_154105_create_personal_access_tokens_table',6),(15,'2025_07_22_074514_add_tax_to_products_table',7),(16,'2025_07_22_095144_add_customer_id_to_bills_table',8),(17,'2025_10_27_084546_create_bill_counters_table',9),(18,'2025_10_27_084547_create_bill_counters_table',10);

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `product_id` int unsigned NOT NULL AUTO_INCREMENT,
  `product_code` longtext COLLATE utf8mb4_unicode_ci,
  `category_id` int unsigned DEFAULT NULL,
  `brand_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `size_id` int unsigned DEFAULT NULL,
  `color_id` int unsigned DEFAULT NULL,
  `threshold` int NOT NULL DEFAULT '2',
  `product_quantity` int NOT NULL DEFAULT '0',
  `product_manual_quantity` int NOT NULL DEFAULT '0',
  `product_mrp` decimal(10,2) NOT NULL DEFAULT '0.00',
  `product_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tax` decimal(5,2) NOT NULL DEFAULT '0.00',
  `active_status` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  KEY `products_category_id_foreign` (`category_id`),
  KEY `products_brand_id_foreign` (`brand_id`),
  KEY `products_style_id_foreign` (`style_id`),
  KEY `products_size_id_foreign` (`size_id`),
  KEY `products_color_id_foreign` (`color_id`),
  CONSTRAINT `products_brand_id_foreign` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`brand_id`) ON DELETE SET NULL,
  CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE SET NULL,
  CONSTRAINT `products_color_id_foreign` FOREIGN KEY (`color_id`) REFERENCES `colors` (`color_id`) ON DELETE SET NULL,
  CONSTRAINT `products_size_id_foreign` FOREIGN KEY (`size_id`) REFERENCES `sizes` (`size_id`) ON DELETE SET NULL,
  CONSTRAINT `products_style_id_foreign` FOREIGN KEY (`style_id`) REFERENCES `styles` (`style_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` VALUES (1,'MFL00001',1,1,1,1,1,10,19,0,250.00,210.00,5.00,1,'2025-07-17 06:22:37','2025-10-27 03:26:02',NULL),(2,'MFL00002',1,1,1,1,2,10,1,0,250.00,200.00,5.00,1,'2025-07-17 07:24:26','2025-10-27 03:26:27',NULL),(3,'MFL00003',3,7,1,1,1,2,10,0,300.00,250.00,12.00,1,'2025-07-18 05:36:23','2025-09-08 03:47:55','2025-09-08 03:47:55'),(4,'MFL00004',2,14,2,2,2,2,0,0,350.00,300.00,12.00,1,'2025-07-22 00:48:44','2025-09-08 03:38:21','2025-09-08 03:38:21'),(5,'MFL00005',2,14,2,2,2,2,0,0,500.00,450.00,5.00,1,'2025-07-22 00:52:37','2025-09-08 03:47:55','2025-09-08 03:47:55'),(6,'25JUL00006',1,6,1,2,2,2,37,0,600.00,550.00,12.00,1,'2025-07-22 00:57:34','2025-09-15 05:58:49',NULL),(7,'25JUL00007',1,14,1,2,1,2,0,0,600.00,550.00,5.00,1,'2025-07-22 02:24:36','2025-07-22 02:24:36',NULL),(8,'25JUL00008',1,14,1,1,1,2,0,0,600.00,550.00,5.00,1,'2025-07-22 02:29:03','2025-07-22 02:29:03',NULL),(9,'25JUL00009',1,14,1,2,1,2,49,0,600.00,550.00,12.00,1,'2025-07-22 02:34:21','2025-07-23 00:12:51',NULL),(10,'25JUL00010',1,14,2,2,2,2,0,0,200.00,200.00,12.00,1,'2025-07-23 00:30:40','2025-07-23 00:30:40',NULL),(11,'25JUL00011',1,1,1,2,2,6,4,0,200.00,200.00,5.00,1,'2025-07-23 04:07:17','2025-07-23 04:09:04',NULL),(12,'250800012',1,4,2,2,2,2,0,0,250.00,250.00,5.00,1,'2025-08-30 05:01:44','2025-08-30 05:01:44',NULL),(13,'250900013',1,14,2,2,1,2,0,0,199.00,199.00,12.00,1,'2025-09-08 03:52:00','2025-09-08 04:01:58',NULL),(14,'250900014',1,1,1,1,2,2,0,0,250.00,250.00,5.00,1,'2025-09-15 06:31:03','2025-09-15 06:31:03',NULL);

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `active_status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` VALUES (1,'Admin',1,NULL,NULL);

--
-- Table structure for table `sizes`
--

CREATE TABLE `sizes` (
  `size_id` int unsigned NOT NULL AUTO_INCREMENT,
  `size_name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active_status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`size_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sizes`
--

INSERT INTO `sizes` VALUES (1,'44',1,'2025-07-17 04:49:51','2025-07-17 04:50:11'),(2,'24',1,'2025-07-22 00:48:13','2025-07-22 00:48:13');

--
-- Table structure for table `styles`
--

CREATE TABLE `styles` (
  `style_id` int unsigned NOT NULL AUTO_INCREMENT,
  `style_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active_status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`style_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `styles`
--

INSERT INTO `styles` VALUES (1,'Cap sleeves Cap sleeves  Cap sleeves',1,'2025-07-17 04:31:11','2025-08-19 00:34:36'),(2,'Tesy',1,'2025-07-17 06:41:29','2025-07-17 06:41:29');


