--
-- Host: localhost    Database: inventory_system
-- ------------------------------------------------------


--
-- Table structure for table `audit_logs`
--

CREATE TABLE `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_id` bigint(20) unsigned NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `audit_logs_user_id_foreign` (`user_id`),
  CONSTRAINT `audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_logs`
--

INSERT INTO `audit_logs` VALUES (1,1,'auth.login','User',1,'User logged in','2026-06-28 13:28:50'),(2,1,'user.update','User',2,'Updated user \"Manager User\" ΓÇö role: [manager]','2026-06-28 13:29:42'),(3,1,'user.password_reset','User',2,'Reset password for user \"Manager User\"','2026-06-28 13:30:10');

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

CREATE TABLE `failed_jobs` (
  `id` bigint(20) 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 `inventory_transactions`
--

CREATE TABLE `inventory_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `type` enum('purchase_order','sales_receipt','adjustment') COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int(11) NOT NULL,
  `reference_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reason_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `note` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inventory_transactions_item_id_foreign` (`item_id`),
  KEY `inventory_transactions_user_id_foreign` (`user_id`),
  CONSTRAINT `inventory_transactions_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_transactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `inventory_transactions`
--

INSERT INTO `inventory_transactions` VALUES (1,1,1,'purchase_order',5,'PO-TEST-001',NULL,NULL,'2026-06-25 15:07:30','2026-06-25 15:07:30'),(2,2,1,'sales_receipt',-18,'SO-TEST-001',NULL,NULL,'2026-06-25 15:09:10','2026-06-25 15:09:10'),(3,1,1,'purchase_order',12,'2',NULL,NULL,'2026-06-25 15:51:23','2026-06-25 15:51:23'),(4,3,1,'purchase_order',25,'OPENING-STOCK',NULL,'Opening stock on item creation.','2026-06-28 05:43:34','2026-06-28 05:43:34'),(5,4,1,'purchase_order',50,'OPENING-STOCK',NULL,'Opening stock on item creation.','2026-06-28 06:08:27',NULL),(6,5,1,'purchase_order',50,'OPENING-STOCK',NULL,'Opening stock on item creation.','2026-06-28 06:09:31',NULL),(7,4,1,'purchase_order',51,'1',NULL,'TO MR. BUCKETS','2026-06-28 06:15:36',NULL),(8,4,1,'sales_receipt',-100,'2',NULL,'SALES TO MR. JULIUS','2026-06-28 06:16:56',NULL);

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

CREATE TABLE `items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sku` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reorder_point` int(11) NOT NULL DEFAULT 0,
  `cost_price` decimal(15,2) NOT NULL DEFAULT 0.00,
  `sale_price` decimal(15,2) NOT NULL DEFAULT 0.00,
  `current_stock` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `items_sku_unique` (`sku`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `items` VALUES (1,'LAPTOP-001','MacBook Pro 16','M3 Max, 64GB RAM, 1TB SSD',5,3000.00,3500.00,27,'2026-06-25 12:23:39','2026-06-25 15:51:23'),(2,'MONITOR-001','Dell UltraSharp 32\"','4K USB-C Hub Monitor',3,800.00,1000.00,2,'2026-06-25 12:23:39','2026-06-28 06:11:14'),(3,'HEADPHONE-001','Sony WH-1000XM5','Noise-cancelling wireless headphones',10,250.00,350.00,25,'2026-06-28 05:43:34','2026-06-28 05:43:34'),(4,'BUCKET-01','10 LITTERS BUCKET','A 10 LITTERS PLASTIC BUCKET',5,5000.00,6000.00,1,'2026-06-28 06:08:27','2026-06-28 06:16:56'),(5,'BUCKET-02','20 LITTERS','A 20 LITTERS PLASTIC BUCKET',5,6000.00,7000.00,50,'2026-06-28 06:09:31','2026-06-28 06:09:31');

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

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

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

INSERT INTO `migrations` VALUES (1,'2014_10_12_000000_create_users_table',1),(2,'2014_10_12_100000_create_password_resets_table',1),(3,'2019_08_19_000000_create_failed_jobs_table',1),(4,'2019_12_14_000001_create_personal_access_tokens_table',1),(5,'2026_06_25_132204_add_role_to_users_table',1),(6,'2026_06_25_132204_create_items_table',1),(7,'2026_06_25_132205_create_inventory_transactions_table',1),(8,'2026_06_25_164349_add_phone_to_users_table',2),(9,'2026_06_28_123229_create_audit_logs_table',3),(10,'2026_06_28_125200_create_settings_table',4);


