-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             11.2.0.6213
-- --------------------------------------------------------



-- Dumping database structure for posai
DROP DATABASE IF EXISTS `posai`;
CREATE DATABASE IF NOT EXISTS `posai` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `posai`;

-- Dumping structure for table posai.cache
CREATE TABLE IF NOT EXISTS `cache` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.cache: ~0 rows (approximately)

-- Dumping structure for table posai.categories
CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_slug_unique` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.categories: ~8 rows (approximately)
REPLACE INTO `categories` (`id`, `name`, `slug`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
	(1, 'Electronics', 'electronics', NULL, 1, '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(2, 'Clothing', 'clothing', NULL, 1, '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(3, 'Food & Beverages', 'food-beverages', NULL, 1, '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(4, 'Home & Garden', 'home-garden', NULL, 1, '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(5, 'Sports & Outdoors', 'sports-outdoors', NULL, 1, '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(6, 'Health & Beauty', 'health-beauty', NULL, 1, '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(7, 'Toys & Games', 'toys-games', NULL, 1, '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(8, 'Office Supplies', 'office-supplies', NULL, 1, '2026-03-28 06:17:24', '2026-03-28 06:17:24');

-- Dumping structure for table posai.credit_payments
CREATE TABLE IF NOT EXISTS `credit_payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `balance_before` decimal(12,2) DEFAULT '0.00',
  `balance_after` decimal(12,2) DEFAULT '0.00',
  `payment_method` varchar(255) DEFAULT 'cash',
  `reference_number` varchar(255) DEFAULT NULL,
  `notes` text,
  `user_id` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `customer_id` (`customer_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `credit_payments_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `credit_payments_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `credit_payments_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table posai.credit_payments: ~0 rows (approximately)

-- Dumping structure for table posai.customers
CREATE TABLE IF NOT EXISTS `customers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `loyalty_points` int(11) NOT NULL DEFAULT '0',
  `total_points_earned` int(11) NOT NULL DEFAULT '0',
  `is_active` 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=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.customers: ~1 rows (approximately)
REPLACE INTO `customers` (`id`, `name`, `phone`, `email`, `address`, `balance`, `loyalty_points`, `total_points_earned`, `is_active`, `created_at`, `updated_at`) VALUES
	(11, 'kelvin', '701980200', '1moonger@gmail.com', NULL, 110.90, 0, 0, 1, '2026-03-28 09:34:20', '2026-03-28 12:45:11');

-- Dumping structure for table posai.expenses
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `category` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `description` text,
  `expense_date` date NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table posai.expenses: ~0 rows (approximately)

-- Dumping structure for table posai.failed_jobs
CREATE TABLE IF NOT EXISTS `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 posai.failed_jobs: ~0 rows (approximately)

-- Dumping structure for table posai.inventory_logs
CREATE TABLE IF NOT EXISTS `inventory_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `type` enum('stock_in','stock_out','adjustment','opening_balance') NOT NULL,
  `quantity` decimal(12,2) NOT NULL,
  `previous_stock` decimal(12,2) NOT NULL,
  `new_stock` decimal(12,2) NOT NULL,
  `unit_cost` decimal(10,2) DEFAULT NULL,
  `reference_number` text,
  `notes` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `user_id` (`user_id`),
  KEY `supplier_id` (`supplier_id`),
  CONSTRAINT `inventory_logs_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_logs_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_logs_ibfk_3` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- Dumping data for table posai.inventory_logs: ~9 rows (approximately)
REPLACE INTO `inventory_logs` (`id`, `product_id`, `user_id`, `supplier_id`, `type`, `quantity`, `previous_stock`, `new_stock`, `unit_cost`, `reference_number`, `notes`, `created_at`, `updated_at`) VALUES
	(1, 24, 2, NULL, 'stock_out', 1.00, -7.00, -8.00, NULL, 'INV-20260328-000001', 'Sale', '2026-03-28 12:45:11', '2026-03-28 12:45:11'),
	(2, 8, 2, NULL, 'stock_out', 1.00, 290.00, 289.00, NULL, 'INV-20260328-000001', 'Sale', '2026-03-28 12:45:11', '2026-03-28 12:45:11'),
	(3, 20, 2, NULL, 'stock_out', 1.00, 23.00, 22.00, NULL, 'INV-20260328-000002', 'Sale', '2026-03-28 12:59:39', '2026-03-28 12:59:39'),
	(4, 8, 2, NULL, 'stock_out', 2.00, 289.00, 287.00, NULL, 'INV-20260328-000002', 'Sale', '2026-03-28 12:59:39', '2026-03-28 12:59:39'),
	(5, 10, 2, NULL, 'stock_out', 1.00, 40.00, 39.00, NULL, 'INV-20260328-000002', 'Sale', '2026-03-28 12:59:39', '2026-03-28 12:59:39'),
	(6, 20, 2, NULL, 'stock_out', 1.00, 22.00, 21.00, NULL, 'INV-20260328-000003', 'Sale', '2026-03-28 13:03:50', '2026-03-28 13:03:50'),
	(7, 9, 2, NULL, 'stock_out', 1.00, 197.00, 196.00, NULL, 'INV-20260328-000003', 'Sale', '2026-03-28 13:03:50', '2026-03-28 13:03:50'),
	(8, 4, 2, NULL, 'stock_out', 1.00, 197.00, 196.00, NULL, 'INV-20260328-000003', 'Sale', '2026-03-28 13:03:50', '2026-03-28 13:03:50'),
	(9, 1, 2, NULL, 'stock_out', 1.00, 50.00, 49.00, NULL, 'INV-20260328-000003', 'Sale', '2026-03-28 13:03:50', '2026-03-28 13:03:50');

-- Dumping structure for table posai.jobs
CREATE TABLE IF NOT EXISTS `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.jobs: ~0 rows (approximately)

-- Dumping structure for table posai.job_batches
CREATE TABLE IF NOT EXISTS `job_batches` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` mediumtext COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.job_batches: ~0 rows (approximately)

-- Dumping structure for table posai.migrations
CREATE TABLE IF NOT EXISTS `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=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.migrations: ~44 rows (approximately)
REPLACE INTO `migrations` (`id`, `migration`, `batch`) VALUES
	(1, '0001_01_01_000001_create_cache_table', 1),
	(2, '0001_01_01_000002_create_jobs_table', 1),
	(3, '2024_01_01_000001_create_users_table', 1),
	(4, '2024_01_01_000002_create_categories_table', 1),
	(5, '2024_01_01_000003_create_products_table', 1),
	(6, '2024_01_01_000004_create_customers_table', 1),
	(7, '2024_01_01_000005_create_suppliers_table', 1),
	(8, '2024_01_01_000006_create_orders_table', 1),
	(9, '2024_01_01_000007_create_inventory_logs_table', 1),
	(10, '2024_01_01_000008_create_sessions_table', 1),
	(11, '2024_01_01_000009_create_permissions_table', 1),
	(12, '2026_03_24_164454_update_orders_table_for_multiple_payments', 1),
	(13, '2026_03_25_130701_add_total_paid_to_suppliers_table', 1),
	(14, '2026_03_25_131400_create_expenses_table', 1),
	(15, '2026_03_25_153423_create_opening_balances_table', 1),
	(16, '2026_03_25_160000_create_supplier_payments_table', 1),
	(17, '2026_03_25_164732_add_opening_balance_to_inventory_logs_type', 1),
	(18, '2026_03_25_170000_create_subscriptions_tables', 1),
	(19, '2026_03_25_181947_update_products_stock_quantity_to_decimal', 1),
	(20, '2026_03_25_182031_update_inventory_logs_quantity_to_decimal', 1),
	(21, '2026_03_25_190000_create_tax_configs_table', 1),
	(22, '2026_03_25_191000_add_tax_details_to_orders_table', 1),
	(23, '2026_03_25_200000_create_mpesa_configs_table', 1),
	(24, '2026_03_25_add_theme_to_users', 1),
	(25, '2026_03_26_000000_add_expiry_date_to_products_table', 1),
	(26, '2026_03_26_100000_add_loyalty_points_to_customers_table', 1),
	(27, '2026_03_26_110000_create_settings_table', 1),
	(28, '2026_03_26_120000_add_super_admin_role_to_users', 1),
	(29, '2026_03_26_130000_add_multiple_prices_to_products', 1),
	(30, '2026_03_27_000000_add_price_type_to_order_items_table', 1),
	(31, '2026_03_27_100000_create_price_adjustment_logs_table', 1),
	(44, '2026_03_27_110000_create_price_adjustment_details_table', 2),
	(45, '2026_03_27_120000_create_product_price_history_table', 2),
	(46, '2026_03_27_124603_add_credit_fields_to_orders_table', 2),
	(47, '2026_03_27_133309_add_sms_settings', 2),
	(48, '2026_03_27_135240_add_nenasasa_sms_settings', 2),
	(49, '2026_03_27_143945_add_nenasasa_oauth_settings', 2),
	(50, '2026_03_27_150000_create_user_activity_logs_table', 2),
	(51, '2026_03_27_181132_add_client_time_to_user_activity_logs_table', 2),
	(54, '2026_03_27_185832_add_client_time_to_orders_table', 3),
	(55, '2026_03_28_000001_add_manual_recording_to_mpesa_configs', 3),
	(56, '2026_03_28_083712_add_price2_price3_to_price_adjustment_details', 4),
	(57, '2026_03_28_091420_add_notes_to_opening_balances_table', 5),
	(58, '2026_03_28_120000_add_receipt_report_settings', 6);

-- Dumping structure for table posai.mpesa_transactions
CREATE TABLE IF NOT EXISTS `mpesa_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned DEFAULT NULL,
  `transaction_type` varchar(255) DEFAULT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `transaction_time` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) DEFAULT '0.00',
  `msisdn` varchar(255) DEFAULT NULL,
  `sender` varchar(255) DEFAULT NULL,
  `receiver` varchar(255) DEFAULT NULL,
  `account_reference` varchar(255) DEFAULT NULL,
  `invoice_number` varchar(255) DEFAULT NULL,
  `external_reference` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT 'pending',
  `raw_request` text,
  `raw_response` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mpesa_transactions_transaction_id_unique` (`transaction_id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `mpesa_transactions_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- Dumping data for table posai.mpesa_transactions: ~1 rows (approximately)
REPLACE INTO `mpesa_transactions` (`id`, `order_id`, `transaction_type`, `transaction_id`, `transaction_time`, `amount`, `msisdn`, `sender`, `receiver`, `account_reference`, `invoice_number`, `external_reference`, `status`, `raw_request`, `raw_response`, `created_at`, `updated_at`) VALUES
	(1, NULL, 'CustomerPayBillOnline', 'MANUAL-1774703028-6462', '2026-03-28 13:03:48', 75.40, NULL, NULL, '123', 'POS-MANUAL-1774703028', NULL, NULL, 'completed', '{"_token":"1mxeMy7RFF8PGiQUdGDAGYBQjdGrNi5HtBoqjzdQ","amount":"75.4","transaction_id":null,"phone":null}', '{"manual":true}', '2026-03-28 13:03:48', '2026-03-28 13:03:48');

-- Dumping structure for table posai.opening_balances
CREATE TABLE IF NOT EXISTS `opening_balances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date` date NOT NULL,
  `amount` decimal(12,2) DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `notes` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `opening_balances_user_id_date_unique` (`user_id`,`date`),
  CONSTRAINT `opening_balances_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- Dumping data for table posai.opening_balances: ~1 rows (approximately)
REPLACE INTO `opening_balances` (`id`, `user_id`, `date`, `amount`, `created_at`, `updated_at`, `notes`) VALUES
	(1, 2, '2026-03-28', 1000.00, '2026-03-28 13:06:15', '2026-03-28 13:06:15', '');

-- Dumping structure for table posai.orders
CREATE TABLE IF NOT EXISTS `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(255) NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `client_time` timestamp NULL DEFAULT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `discount` decimal(10,2) DEFAULT '0.00',
  `discount_type` varchar(255) DEFAULT 'percentage',
  `tax` decimal(10,2) DEFAULT '0.00',
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `tax_details` json DEFAULT NULL,
  `total` decimal(10,2) NOT NULL,
  `paid_amount` decimal(12,2) DEFAULT '0.00',
  `change_amount` decimal(10,2) DEFAULT '0.00',
  `payment_method` varchar(255) DEFAULT NULL,
  `payment_methods` json DEFAULT NULL,
  `payment_type` enum('cash','credit','partial') DEFAULT 'cash',
  `credit_amount` decimal(12,2) DEFAULT '0.00',
  `due_date` date DEFAULT NULL,
  `credit_status` enum('pending','partial','paid','overdue') DEFAULT NULL,
  `status` enum('completed','pending','cancelled') DEFAULT 'completed',
  `notes` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `orders_invoice_number_unique` (`invoice_number`),
  KEY `user_id` (`user_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- Dumping data for table posai.orders: ~3 rows (approximately)
REPLACE INTO `orders` (`id`, `invoice_number`, `user_id`, `customer_id`, `client_time`, `subtotal`, `discount`, `discount_type`, `tax`, `tax_rate`, `tax_details`, `total`, `paid_amount`, `change_amount`, `payment_method`, `payment_methods`, `payment_type`, `credit_amount`, `due_date`, `credit_status`, `status`, `notes`, `created_at`, `updated_at`) VALUES
	(5, 'INV-20260328-000001', 2, 11, '2026-03-28 15:43:43', 52.50, 0.00, '0', 8.40, 16.00, '[{"tax_id": 1, "tax_code": "vat", "tax_name": "vat 16%", "tax_rate": "16.00", "tax_amount": 8.4}]', 60.90, 0.00, 0.00, 'cash', '[]', 'credit', 60.90, NULL, 'pending', 'completed', NULL, '2026-03-28 12:45:11', '2026-03-28 12:45:11'),
	(6, 'INV-20260328-000002', 2, 11, '2026-03-28 15:58:40', 50.00, 0.00, '0', 8.00, 16.00, '[{"tax_id": 1, "tax_code": "vat", "tax_name": "vat 16%", "tax_rate": "16.00", "tax_amount": 8}]', 58.00, 100.00, 42.00, 'cash', '[{"amount": 100, "method": "cash"}]', 'cash', 0.00, NULL, NULL, 'completed', NULL, '2026-03-28 12:59:39', '2026-03-28 12:59:39'),
	(7, 'INV-20260328-000003', 2, 11, '2026-03-28 16:01:46', 65.00, 0.00, '0', 10.40, 16.00, '[{"tax_id": 1, "tax_code": "vat", "tax_name": "vat 16%", "tax_rate": "16.00", "tax_amount": 10.4}]', 75.40, 75.40, 0.00, 'cash', '[{"amount": 0, "method": "cash"}, {"amount": 75.4, "method": "mpesa", "checkout_id": null}]', 'cash', 0.00, NULL, NULL, 'completed', NULL, '2026-03-28 13:03:50', '2026-03-28 13:03:50');

-- Dumping structure for table posai.order_items
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_sku` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `price_type` varchar(255) DEFAULT NULL,
  `unit_cost` decimal(12,2) DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- Dumping data for table posai.order_items: ~9 rows (approximately)
REPLACE INTO `order_items` (`id`, `order_id`, `product_id`, `product_name`, `product_sku`, `price`, `price_type`, `unit_cost`, `quantity`, `subtotal`, `created_at`, `updated_at`) VALUES
	(4, 5, 24, 'orange', 'orange', 50.00, 'default', 20.00, 1, 50.00, '2026-03-28 12:45:11', '2026-03-28 12:45:11'),
	(5, 5, 8, 'Energy Bar', 'FOOD-002', 2.50, 'default', 1.00, 1, 2.50, '2026-03-28 12:45:11', '2026-03-28 12:45:11'),
	(6, 6, 20, 'Action Figure', 'TOYS-002', 20.00, 'default', 10.00, 1, 20.00, '2026-03-28 12:59:39', '2026-03-28 12:59:39'),
	(7, 6, 8, 'Energy Bar', 'FOOD-002', 2.50, 'default', 1.00, 2, 5.00, '2026-03-28 12:59:39', '2026-03-28 12:59:39'),
	(8, 6, 10, 'Garden Hose', 'HOME-001', 25.00, 'default', 15.00, 1, 25.00, '2026-03-28 12:59:39', '2026-03-28 12:59:39'),
	(9, 7, 20, 'Action Figure', 'TOYS-002', 20.00, 'default', 10.00, 1, 20.00, '2026-03-28 13:03:50', '2026-03-28 13:03:50'),
	(10, 7, 9, 'Coffee Cup', 'FOOD-003', 5.00, 'default', 2.00, 1, 5.00, '2026-03-28 13:03:50', '2026-03-28 13:03:50'),
	(11, 7, 4, 'Cotton T-Shirt', 'CLTH-001', 15.00, 'default', 8.00, 1, 15.00, '2026-03-28 13:03:50', '2026-03-28 13:03:50'),
	(12, 7, 1, 'Wireless Mouse', 'ELEC-001', 25.00, 'default', 15.00, 1, 25.00, '2026-03-28 13:03:50', '2026-03-28 13:03:50');

-- Dumping structure for table posai.permissions
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_name_unique` (`name`),
  UNIQUE KEY `permissions_slug_unique` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.permissions: ~49 rows (approximately)
REPLACE INTO `permissions` (`id`, `name`, `slug`, `description`, `created_at`, `updated_at`) VALUES
	(1, 'View Dashboard', 'dashboard.view', 'Can view dashboard', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(2, 'View Products', 'products.view', 'Can view products list', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(3, 'Create Products', 'products.create', 'Can create products', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(4, 'Edit Products', 'products.edit', 'Can edit products', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(5, 'Delete Products', 'products.delete', 'Can delete products', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(6, 'View Categories', 'categories.view', 'Can view categories', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(7, 'Create Categories', 'categories.create', 'Can create categories', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(8, 'Edit Categories', 'categories.edit', 'Can edit categories', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(9, 'Delete Categories', 'categories.delete', 'Can delete categories', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(10, 'View Customers', 'customers.view', 'Can view customers', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(11, 'Create Customers', 'customers.create', 'Can create customers', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(12, 'Edit Customers', 'customers.edit', 'Can edit customers', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(13, 'Delete Customers', 'customers.delete', 'Can delete customers', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(14, 'View Suppliers', 'suppliers.view', 'Can view suppliers', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(15, 'Create Suppliers', 'suppliers.create', 'Can create suppliers', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(16, 'Edit Suppliers', 'suppliers.edit', 'Can edit suppliers', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(17, 'Delete Suppliers', 'suppliers.delete', 'Can delete suppliers', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(18, 'Supplier Finance', 'suppliers.finance', 'Can view supplier finance', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(19, 'POS Sales', 'pos.sales', 'Can make sales', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(20, 'Opening Balance', 'opening-balance.view', 'Can view opening balance', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(21, 'View Orders', 'orders.view', 'Can view orders', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(22, 'Cancel Orders', 'orders.cancel', 'Can cancel orders', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(23, 'View Inventory', 'inventory.view', 'Can view inventory', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(24, 'Stock In', 'inventory.stock-in', 'Can add stock', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(25, 'Price Adjustment', 'inventory.adjustment', 'Can adjust inventory', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(26, 'View Reports', 'reports.view', 'Can view reports', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(27, 'Sales Report', 'reports.sales', 'Can view sales report', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(28, 'Profit Report', 'reports.profit', 'Can view profit report', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(29, 'Inventory Report', 'reports.inventory', 'Can view inventory report', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(30, 'Supplier Payments Report', 'reports.supplier-payments', 'Can view supplier payments report', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(31, 'Opening Balance Report', 'reports.opening-balance', 'Can view opening balance report', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(32, 'Stock Movement Report', 'reports.stock-movement', 'Can view stock movement report', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(33, 'Cashier Report', 'reports.cashier', 'Can view cashier report', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(34, 'Manage Users', 'users.manage', 'Can manage users', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(35, 'Manage Permissions', 'permissions.manage', 'Can manage user permissions', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(36, 'View Expenses', 'expenses.view', 'Can view expenses', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(37, 'Create Expenses', 'expenses.create', 'Can create expenses', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(38, 'Delete Expenses', 'expenses.delete', 'Can delete expenses', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(39, 'View Cash Flow', 'expenses.cashflow', 'Can view cash flow report', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(40, 'View Tax', 'tax.view', 'Can view tax configuration', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(41, 'Manage Tax', 'tax.manage', 'Can manage tax settings', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(42, 'View e-TIMS', 'tax.etims.view', 'Can view e-TIMS configuration', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(43, 'Manage e-TIMS', 'tax.etims.manage', 'Can manage e-TIMS settings', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(44, 'View M-Pesa', 'mpesa.view', 'Can view M-Pesa configuration', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(45, 'Manage M-Pesa', 'mpesa.manage', 'Can manage M-Pesa settings', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(46, 'View Credit', 'credit.view', 'Can view credit management', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(47, 'Manage Credit', 'credit.manage', 'Can manage credit and receive payments', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(48, 'Send Reminders', 'credit.reminders', 'Can send payment reminders', '2026-03-28 06:17:24', '2026-03-28 06:17:24'),
	(49, 'SMS Settings', 'settings.sms', 'Can manage SMS integration settings', '2026-03-28 06:17:24', '2026-03-28 06:17:24');

-- Dumping structure for table posai.products
CREATE TABLE IF NOT EXISTS `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sku` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `barcode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cost_price` decimal(10,2) NOT NULL,
  `selling_price` decimal(10,2) NOT NULL,
  `price_2` decimal(12,2) DEFAULT NULL,
  `price_3` decimal(12,2) DEFAULT NULL,
  `price_2_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'e.g., Wholesale, VIP',
  `price_3_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'e.g., Bulk, Special',
  `stock_quantity` decimal(12,2) NOT NULL,
  `low_stock_threshold` int(11) NOT NULL DEFAULT '10',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `description` text COLLATE utf8mb4_unicode_ci,
  `expiry_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_sku_unique` (`sku`),
  UNIQUE KEY `products_barcode_unique` (`barcode`),
  KEY `products_category_id_foreign` (`category_id`),
  CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.products: ~7 rows (approximately)
REPLACE INTO `products` (`id`, `category_id`, `name`, `sku`, `barcode`, `image`, `cost_price`, `selling_price`, `price_2`, `price_3`, `price_2_name`, `price_3_name`, `stock_quantity`, `low_stock_threshold`, `is_active`, `description`, `expiry_date`, `created_at`, `updated_at`) VALUES
	(1, 1, 'Wireless Mouse', 'ELEC-001', '123456789001', NULL, 15.00, 25.00, NULL, NULL, NULL, NULL, 49.00, 10, 1, 'Wireless Mouse - High quality product', NULL, '2026-03-28 06:17:24', '2026-03-28 13:03:50'),
	(4, 2, 'Cotton T-Shirt', 'CLTH-001', '223456789001', NULL, 8.00, 15.00, NULL, NULL, NULL, NULL, 196.00, 30, 1, 'Cotton T-Shirt - High quality product', NULL, '2026-03-28 06:17:24', '2026-03-28 13:03:50'),
	(8, 3, 'Energy Bar', 'FOOD-002', '323456789002', NULL, 1.00, 2.50, NULL, NULL, NULL, NULL, 287.00, 50, 1, 'Energy Bar - High quality product', NULL, '2026-03-28 06:17:24', '2026-03-28 12:59:39'),
	(9, 3, 'Coffee Cup', 'FOOD-003', '323456789003', NULL, 2.00, 5.00, NULL, NULL, NULL, NULL, 196.00, 30, 1, 'Coffee Cup - High quality product', NULL, '2026-03-28 06:17:24', '2026-03-28 13:03:50'),
	(10, 4, 'Garden Hose', 'HOME-001', '423456789001', NULL, 15.00, 25.00, NULL, NULL, NULL, NULL, 39.00, 10, 1, 'Garden Hose - High quality product', NULL, '2026-03-28 06:17:25', '2026-03-28 12:59:39'),
	(20, 7, 'Action Figure', 'TOYS-002', '723456789002', NULL, 10.00, 20.00, NULL, NULL, NULL, NULL, 21.00, 10, 1, 'Action Figure - High quality product', NULL, '2026-03-28 06:17:25', '2026-03-28 13:03:50'),
	(24, 2, 'orange', 'orange', 'orange', NULL, 20.00, 50.00, 45.00, 40.00, 'Price 2', 'Price 3', -8.00, 10, 1, NULL, '2026-03-31', '2026-03-28 07:48:34', '2026-03-28 12:45:11');

-- Dumping structure for table posai.product_price_history
CREATE TABLE IF NOT EXISTS `product_price_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `price_adjustment_log_id` bigint(20) unsigned DEFAULT NULL,
  `old_cost_price` decimal(12,2) NOT NULL,
  `new_cost_price` decimal(12,2) NOT NULL,
  `old_selling_price` decimal(12,2) NOT NULL,
  `new_selling_price` decimal(12,2) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `product_price_history_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table posai.product_price_history: ~0 rows (approximately)

-- Dumping structure for table posai.sessions
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.sessions: ~0 rows (approximately)

-- Dumping structure for table posai.settings
CREATE TABLE IF NOT EXISTS `settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.settings: ~26 rows (approximately)
REPLACE INTO `settings` (`id`, `key`, `value`, `created_at`, `updated_at`) VALUES
	(1, 'loyalty_points_ratio', '1', '2026-03-28 06:16:13', '2026-03-28 06:16:13'),
	(2, 'loyalty_points_redemption', '1', '2026-03-28 06:16:13', '2026-03-28 06:16:13'),
	(16, 'sms_provider', 'africastalking', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(17, 'sms_api_key', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(18, 'sms_shortcode', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(19, 'sms_sender_id', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(20, 'sms_enabled', '0', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(21, 'sms_oauth_client_id', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(22, 'sms_oauth_client_secret', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(23, 'sms_oauth_access_token', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(24, 'sms_oauth_refresh_token', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(25, 'sms_oauth_token_expires', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(26, 'sms_oauth_username', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(27, 'sms_oauth_password', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(28, 'sms_channel_id', '', '2026-03-28 06:25:40', '2026-03-28 06:25:40'),
	(29, 'receipt_header', 'TOKIM PHARMACY', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(30, 'receipt_address', 'NAKURU-NDUNDORI', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(31, 'receipt_phone', 'Tel: 0701-980-200', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(32, 'receipt_footer', '*** THANK YOU FOR YOUR BUSINESS ***', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(33, 'report_header', 'POS System Report', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(34, 'report_company', 'POS SYSTEM', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(35, 'report_footer', 'Generated by POS System', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(36, 'receipt_dev_ad', 'Powered by Destech: 0701-980-200 / destechsoftwares.co.ke', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(37, 'report_dev_ad', NULL, '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(38, 'receipt_tagline', 'Your Trusted Shopping Destination Always', '2026-03-28 12:37:22', '2026-03-28 13:01:41'),
	(39, 'system_name', 'TOKIM PHARMACY', NULL, '2026-03-28 11:21:04');

-- Dumping structure for table posai.subscriptions
CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `billing_cycle_days` int(11) NOT NULL DEFAULT '30',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.subscriptions: ~0 rows (approximately)

-- Dumping structure for table posai.suppliers
CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `contact_person` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `total_paid` decimal(12,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.suppliers: ~5 rows (approximately)
REPLACE INTO `suppliers` (`id`, `name`, `phone`, `email`, `address`, `contact_person`, `is_active`, `total_paid`, `created_at`, `updated_at`) VALUES
	(1, 'TechDistro Inc.', '555-1001', 'mike@techdistro.com', '100 Tech Park, Silicon Valley', 'Mike Johnson', 1, 0.00, '2026-03-28 06:17:25', '2026-03-28 09:30:47'),
	(2, 'FashionFirst Ltd.', '555-1002', 'sarah@fashionfirst.com', '200 Fashion Blvd, New York', 'Sarah Williams', 1, 0.00, '2026-03-28 06:17:25', '2026-03-28 09:29:23'),
	(3, 'Global Foods Co.', '555-1003', 'tom@globalfoods.com', '300 Market St, Chicago', 'Tom Garcia', 1, 0.00, '2026-03-28 06:17:25', '2026-03-28 06:17:25'),
	(4, 'HomeStyle Supplies', '555-1004', 'lisa@homesupplies.com', '400 Home Center, Los Angeles', 'Lisa Anderson', 1, 0.00, '2026-03-28 06:17:25', '2026-03-28 06:17:25'),
	(5, 'SportsPro Wholesale', '555-1005', 'james@sportspro.com', '500 Stadium Way, Miami', 'James Thomas', 1, 0.00, '2026-03-28 06:17:25', '2026-03-28 06:17:25');

-- Dumping structure for table posai.supplier_payments
CREATE TABLE IF NOT EXISTS `supplier_payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` varchar(255) DEFAULT 'cash',
  `reference_number` varchar(255) DEFAULT NULL,
  `notes` text,
  `payment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `supplier_id` (`supplier_id`),
  CONSTRAINT `supplier_payments_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table posai.supplier_payments: ~0 rows (approximately)

-- Dumping structure for table posai.tax_configs
CREATE TABLE IF NOT EXISTS `tax_configs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rate` decimal(5,2) NOT NULL DEFAULT '0.00',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'vat',
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tax_configs_code_unique` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table posai.tax_configs: ~1 rows (approximately)
REPLACE INTO `tax_configs` (`id`, `name`, `code`, `rate`, `is_active`, `is_default`, `type`, `description`, `created_at`, `updated_at`) VALUES
	(1, 'vat 16%', 'vat', 16.00, 1, 1, 'vat', NULL, '2026-03-28 11:07:14', '2026-03-28 11:07:16');

-- Dumping structure for table posai.user_activity_logs
CREATE TABLE IF NOT EXISTS `user_activity_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `activity_type` varchar(255) NOT NULL,
  `description` text,
  `ip_address` varchar(255) DEFAULT NULL,
  `user_agent` text,
  `metadata` json DEFAULT NULL,
  `client_time` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_activity_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- Dumping data for table posai.user_activity_logs: ~5 rows (approximately)
REPLACE INTO `user_activity_logs` (`id`, `user_id`, `activity_type`, `description`, `ip_address`, `user_agent`, `metadata`, `client_time`, `created_at`, `updated_at`) VALUES
	(1, 2, 'login', 'User logged in', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:149.0) Gecko/20100101 Firefox/149.0', NULL, '2026-03-28 15:43:37', '2026-03-28 12:43:38', '2026-03-28 12:43:38'),
	(2, 2, 'sale', 'Completed sale - Invoice: INV-20260328-000001, Total: 60.9', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:149.0) Gecko/20100101 Firefox/149.0', '{"total": 60.9, "order_id": 5, "items_count": 2, "invoice_number": "INV-20260328-000001"}', '2026-03-28 15:43:43', '2026-03-28 12:45:11', '2026-03-28 12:45:11'),
	(3, 2, 'login', 'User logged in', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:149.0) Gecko/20100101 Firefox/149.0', NULL, '2026-03-28 15:54:17', '2026-03-28 12:54:19', '2026-03-28 12:54:19'),
	(4, 2, 'sale', 'Completed sale - Invoice: INV-20260328-000002, Total: 58', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:149.0) Gecko/20100101 Firefox/149.0', '{"total": 58, "order_id": 6, "items_count": 3, "invoice_number": "INV-20260328-000002"}', '2026-03-28 15:58:40', '2026-03-28 12:59:39', '2026-03-28 12:59:39'),
	(5, 2, 'sale', 'Completed sale - Invoice: INV-20260328-000003, Total: 75.4', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:149.0) Gecko/20100101 Firefox/149.0', '{"total": 75.4, "order_id": 7, "items_count": 4, "invoice_number": "INV-20260328-000003"}', '2026-03-28 16:01:46', '2026-03-28 13:03:50', '2026-03-28 13:03:50');

