-- Database Backup: alishaattvs_mobos
-- Generated on: 2026-04-09 13:07:19



CREATE TABLE `brands` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` enum('mobile','accessory') NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO brands VALUES("1","Earbuds","accessory");
INSERT INTO brands VALUES("2","Screen","accessory");
INSERT INTO brands VALUES("3","Camera","accessory");
INSERT INTO brands VALUES("4","Repairing Tools","accessory");
INSERT INTO brands VALUES("9","Vivp","accessory");
INSERT INTO brands VALUES("10","VIVO","accessory");





CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` enum('mobile','accessory') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;






CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `total_credit` decimal(10,2) DEFAULT 0.00,
  `total_paid` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `email` varchar(100) DEFAULT NULL,
  `opening_balance` decimal(10,2) DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO customers VALUES("1","Walk-In","","","-4900.00","23800.00","2026-03-10 22:54:19","","0.00");
INSERT INTO customers VALUES("4","demp","","","0.00","0.00","2026-03-11 20:23:21","","0.00");





CREATE TABLE `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `expense_date` date DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;






CREATE TABLE `mobiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `imei` varchar(20) NOT NULL,
  `color` varchar(50) DEFAULT NULL,
  `storage` varchar(50) DEFAULT NULL,
  `status` enum('instock','sold','returned','damaged') DEFAULT 'instock',
  `purchase_id` int(11) DEFAULT NULL,
  `sale_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `imei` (`imei`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `mobiles_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;






CREATE TABLE `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_type` enum('customer','supplier') NOT NULL,
  `person_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `payment_method` varchar(50) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO payments VALUES("1","customer","1","5600.00","2026-03-11 18:27:02","Cash","Payment for sale INV-1",NULL);
INSERT INTO payments VALUES("2","customer","1","-1400.00","2026-03-11 18:28:19","Refund","Cash Refund for INV-1 (Return ID: 1)",NULL);
INSERT INTO payments VALUES("3","customer","1","1400.00","2026-03-11 19:34:48","Credit","Payment for sale INV-2",NULL);
INSERT INTO payments VALUES("4","customer","1","1400.00","2026-03-11 19:34:49","Credit","Payment for sale INV-3",NULL);
INSERT INTO payments VALUES("5","customer","1","1400.00","2026-03-11 19:35:15","Credit","Payment for sale INV-4",NULL);
INSERT INTO payments VALUES("6","customer","1","1400.00","2026-03-11 19:35:16","Credit","Payment for sale INV-5",NULL);
INSERT INTO payments VALUES("7","customer","1","1400.00","2026-03-11 19:36:17","Credit","Payment for sale INV-6",NULL);
INSERT INTO payments VALUES("8","customer","1","1400.00","2026-03-11 20:23:30","Credit","Payment for sale INV-7",NULL);
INSERT INTO payments VALUES("9","customer","1","1400.00","2026-03-11 20:23:35","Credit","Payment for sale INV-8",NULL);
INSERT INTO payments VALUES("10","customer","1","1400.00","2026-03-11 20:23:37","Credit","Payment for sale INV-9",NULL);
INSERT INTO payments VALUES("11","customer","1","1400.00","2026-03-11 20:23:37","Credit","Payment for sale INV-10",NULL);
INSERT INTO payments VALUES("12","customer","1","1400.00","2026-03-11 20:23:47","Credit","Payment for sale INV-3",NULL);
INSERT INTO payments VALUES("13","customer","1","1400.00","2026-03-11 20:23:48","Credit","Payment for sale INV-3",NULL);
INSERT INTO payments VALUES("14","customer","2","1400.00","2026-03-11 20:23:49","Credit","Payment for sale INV-4",NULL);
INSERT INTO payments VALUES("15","customer","2","1400.00","2026-03-11 20:23:50","Credit","Payment for sale INV-4",NULL);
INSERT INTO payments VALUES("16","customer","1","1400.00","2026-03-11 20:24:02","Credit","Payment for sale INV-3",NULL);
INSERT INTO payments VALUES("17","customer","2","1400.00","2026-03-11 20:24:03","Credit","Payment for sale INV-4",NULL);
INSERT INTO payments VALUES("18","customer","1","1400.00","2026-03-11 21:41:54","Credit","Payment for sale INV-3",NULL);
INSERT INTO payments VALUES("19","customer","2","1400.00","2026-03-11 21:41:55","Credit","Payment for sale INV-4",NULL);
INSERT INTO payments VALUES("20","customer","1","1400.00","2026-03-11 21:42:06","Credit","Payment for sale INV-3",NULL);
INSERT INTO payments VALUES("21","customer","2","1400.00","2026-03-11 21:42:07","Credit","Payment for sale INV-4",NULL);





CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT 0.00,
  `sale_price` decimal(10,2) DEFAULT 0.00,
  `quantity` int(11) DEFAULT 0,
  `min_stock` int(11) DEFAULT 5,
  `barcode` varchar(100) DEFAULT NULL,
  `is_mobile` tinyint(1) DEFAULT 0,
  `brand_id` int(11) DEFAULT NULL,
  `warranty` varchar(100) DEFAULT NULL,
  `status` enum('active','deleted') DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `brand_id` (`brand_id`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  CONSTRAINT `products_ibfk_2` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO products VALUES("1","Vivo Y20 Housing",NULL,"550.00","700.00","0","5","BRC-49946835","0","9",NULL,"active");
INSERT INTO products VALUES("2","Vivo Y20 Housing",NULL,"550.00","700.00","50","5","BRC-49946835","0","9",NULL,"deleted");





CREATE TABLE `purchase_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `purchase_id` (`purchase_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `purchase_items_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`),
  CONSTRAINT `purchase_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;






CREATE TABLE `purchase_return_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `return_id` (`return_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `purchase_return_items_ibfk_1` FOREIGN KEY (`return_id`) REFERENCES `purchase_returns` (`id`),
  CONSTRAINT `purchase_return_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;






CREATE TABLE `purchase_returns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_id` int(11) DEFAULT NULL,
  `return_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `total_amount` decimal(10,2) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `purchase_id` (`purchase_id`),
  CONSTRAINT `purchase_returns_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;






CREATE TABLE `purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_id` int(11) DEFAULT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `paid_amount` decimal(10,2) DEFAULT 0.00,
  `payment_method` varchar(50) DEFAULT NULL,
  `purchase_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `supplier_id` (`supplier_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;






CREATE TABLE `remote_scans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(100) NOT NULL,
  `barcode` varchar(100) NOT NULL,
  `is_processed` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;






CREATE TABLE `sale_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `imei` varchar(20) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `unit_price` decimal(10,2) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sale_id` (`sale_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `sale_items_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`),
  CONSTRAINT `sale_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO sale_items VALUES("1","1","1",NULL,"8","700.00","5600.00");
INSERT INTO sale_items VALUES("2","2","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("3","3","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("4","4","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("5","5","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("6","6","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("7","7","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("8","8","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("9","9","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("10","10","1",NULL,"4","350.00","1400.00");
INSERT INTO sale_items VALUES("11","15","1",NULL,"5","350.00","1750.00");
INSERT INTO sale_items VALUES("12","16","1",NULL,"5","350.00","1750.00");





CREATE TABLE `sale_return_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `imei` varchar(20) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `unit_price` decimal(10,2) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `return_id` (`return_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `sale_return_items_ibfk_1` FOREIGN KEY (`return_id`) REFERENCES `sales_returns` (`id`),
  CONSTRAINT `sale_return_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO sale_return_items VALUES("1","1","1",NULL,"2","700.00","1400.00");
INSERT INTO sale_return_items VALUES("2","2","1",NULL,"2","700.00","1400.00");





CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `discount` decimal(10,2) DEFAULT 0.00,
  `final_amount` decimal(10,2) NOT NULL,
  `paid_amount` decimal(10,2) DEFAULT 0.00,
  `payment_method` varchar(50) DEFAULT NULL,
  `sale_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO sales VALUES("1","1","5600.00","0.00","5600.00","4200.00","Cash","2026-03-11 18:27:02",NULL);
INSERT INTO sales VALUES("2","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 19:34:48",NULL);
INSERT INTO sales VALUES("3","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 19:34:49",NULL);
INSERT INTO sales VALUES("4","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 19:35:15",NULL);
INSERT INTO sales VALUES("5","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 19:35:16",NULL);
INSERT INTO sales VALUES("6","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 19:36:17",NULL);
INSERT INTO sales VALUES("7","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 20:23:30",NULL);
INSERT INTO sales VALUES("8","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 20:23:35",NULL);
INSERT INTO sales VALUES("9","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 20:23:37",NULL);
INSERT INTO sales VALUES("10","1","1400.00","0.00","1400.00","1400.00","Credit","2026-03-11 20:23:37",NULL);
INSERT INTO sales VALUES("15","1","1750.00","0.00","1750.00","0.00","Credit","2026-03-11 20:23:43",NULL);
INSERT INTO sales VALUES("16","1","1750.00","0.00","1750.00","0.00","Credit","2026-03-11 20:23:44",NULL);





CREATE TABLE `sales_returns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) DEFAULT NULL,
  `return_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `total_amount` decimal(10,2) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sale_id` (`sale_id`),
  CONSTRAINT `sales_returns_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO sales_returns VALUES("1","1","2026-03-11 18:28:19","1400.00",NULL);
INSERT INTO sales_returns VALUES("2","1","2026-03-11 21:42:49","1400.00",NULL);





CREATE TABLE `settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO settings VALUES("address","");
INSERT INTO settings VALUES("app_name","UM ");
INSERT INTO settings VALUES("currency","Rs.");
INSERT INTO settings VALUES("online_store_url","usmanmobilezone57.alishaattvs.com");
INSERT INTO settings VALUES("phone","");
INSERT INTO settings VALUES("tagline","");
INSERT INTO settings VALUES("tax_rate","0");





CREATE TABLE `suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `company` varchar(100) DEFAULT NULL,
  `total_payable` decimal(10,2) DEFAULT 0.00,
  `total_paid` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `email` varchar(100) DEFAULT NULL,
  `opening_balance` decimal(10,2) DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO suppliers VALUES("1","demo","","","0.00","0.00","2026-03-11 20:23:23","","0.00");





CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `role` enum('admin','staff') DEFAULT 'staff',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO users VALUES("1","admin","admin123","Usman","admin","2026-02-22 20:15:46");