-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: May 15, 2026 at 01:17 PM
-- Server version: 10.11.15-MariaDB
-- PHP Version: 8.4.21

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `yourusername_bubblestechs`
--

-- --------------------------------------------------------

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
  `id` int(11) NOT NULL,
  `username` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('superadmin','admin') DEFAULT 'admin',
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `admins`
--

INSERT INTO `admins` (`id`, `username`, `email`, `password`, `role`, `last_login`, `created_at`) VALUES
(1, 'Admin', 'admin@nextech.com', '$2y$10$OV.38ugVKMM.kXH0N4Sst.r0ZeWXkK4jQW1jwPQ.kNQehaFh99B2K', 'superadmin', '2026-05-15 11:07:58', '2026-05-14 15:58:06');

-- --------------------------------------------------------

--
-- Table structure for table `carousel`
--

CREATE TABLE `carousel` (
  `id` int(11) NOT NULL,
  `title` varchar(200) DEFAULT NULL,
  `subtitle` varchar(300) DEFAULT NULL,
  `image` varchar(255) NOT NULL,
  `button_text` varchar(100) DEFAULT NULL,
  `button_url` varchar(500) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

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

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(110) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `recipient_type` enum('user','admin') NOT NULL DEFAULT 'user',
  `recipient_id` int(11) NOT NULL,
  `type` varchar(100) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `data` longtext DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `recipient_type`, `recipient_id`, `type`, `title`, `message`, `data`, `is_read`, `read_at`, `created_at`) VALUES
(1, 'user', 1, 'welcome', 'Welcome to Bubblestechs!', 'Your account has been created. Start exploring our products.', NULL, 1, '2026-05-15 05:31:50', '2026-05-14 18:43:27'),
(2, 'admin', 1, 'order_placed', 'New Order', 'Order #BT-19B3A62DDD — K2,499.00', '{\"order_id\":1}', 0, NULL, '2026-05-14 18:44:06'),
(3, 'admin', 1, 'order_placed', 'New Order', 'Order #BT-4075D60E6A — K3,720.00', '{\"order_id\":2}', 0, NULL, '2026-05-14 18:44:27'),
(4, 'user', 1, 'payment_confirmed', 'Payment Confirmed', 'Your payment for order BT-4075D60E6A has been manually confirmed. Your order is now being processed.', '{\"order_id\":2}', 1, '2026-05-15 05:31:50', '2026-05-14 18:44:58'),
(5, 'user', 1, 'order_completed', 'Order Completed', 'Your order BT-4075D60E6A has been completed. Thank you!', '{\"order_id\":2}', 1, '2026-05-15 05:31:50', '2026-05-14 18:56:00'),
(6, 'user', 1, 'payment_confirmed', 'Payment Confirmed', 'Your payment for order BT-19B3A62DDD has been manually confirmed. Your order is now being processed.', '{\"order_id\":1}', 1, '2026-05-15 05:31:50', '2026-05-14 18:57:57'),
(7, 'user', 1, 'order_completed', 'Order Completed', 'Your order BT-19B3A62DDD has been completed. Thank you!', '{\"order_id\":1}', 1, '2026-05-15 05:31:50', '2026-05-14 19:15:16'),
(8, 'admin', 1, 'order_placed', 'New Order', 'Order #BT-79FC2B3434 — K5.00', '{\"order_id\":3}', 0, NULL, '2026-05-14 19:16:00'),
(9, 'admin', 1, 'order_placed', 'New Order', 'Order #BT-00B88C8706 — K5.00', '{\"order_id\":4}', 0, NULL, '2026-05-14 19:16:31'),
(10, 'user', 1, 'payment_confirmed', 'Payment Confirmed', 'Your payment for order BT-00B88C8706 has been manually confirmed. Your order is now being processed.', '{\"order_id\":4}', 1, '2026-05-15 05:31:50', '2026-05-14 19:17:21'),
(11, 'user', 1, 'order_completed', 'Order Completed', 'Your order BT-79FC2B3434 has been completed. Thank you!', '{\"order_id\":3}', 1, '2026-05-15 05:31:50', '2026-05-14 19:17:59'),
(12, 'user', 1, 'payment_confirmed', 'Payment Confirmed', 'Your payment for order BT-79FC2B3434 has been manually confirmed. Your order is now being processed.', '{\"order_id\":3}', 1, '2026-05-15 05:31:50', '2026-05-14 19:18:14'),
(13, 'user', 1, 'order_completed', 'Order Completed', 'Your order BT-00B88C8706 has been completed. Thank you!', '{\"order_id\":4}', 1, '2026-05-15 05:31:50', '2026-05-14 19:19:08'),
(14, 'user', 2, 'welcome', 'Welcome to Bubblestech!', 'Your account has been created. Start exploring our products.', NULL, 0, NULL, '2026-05-15 03:14:17'),
(15, 'admin', 1, 'order_placed', 'New Order', 'Order #BT-DDFE36D898 — K300.00', '{\"order_id\":5}', 0, NULL, '2026-05-15 03:15:14'),
(16, 'admin', 1, 'order_placed', 'New Order', 'Order #BT-B6A83819E2 — K5.00', '{\"order_id\":6}', 0, NULL, '2026-05-15 04:51:26'),
(17, 'admin', 1, 'order_placed', 'New Order', 'Order #BT-C8B5D77813 — K5.00', '{\"order_id\":7}', 0, NULL, '2026-05-15 05:25:12'),
(18, 'user', 1, 'payment_confirmed', 'Payment Confirmed', 'Your payment for order BT-B6A83819E2 has been manually confirmed. Your order is now being processed.', '{\"order_id\":6}', 1, '2026-05-15 05:31:50', '2026-05-15 05:27:44'),
(19, 'user', 1, 'order_completed', 'Order Completed', 'Your order BT-B6A83819E2 has been completed. Thank you!', '{\"order_id\":6}', 1, '2026-05-15 05:31:50', '2026-05-15 05:27:53'),
(20, 'user', 1, 'order_completed', 'Order Completed', 'Your order BT-C8B5D77813 has been completed. Thank you!', '{\"order_id\":7}', 1, '2026-05-15 05:31:50', '2026-05-15 05:28:24'),
(21, 'user', 1, 'order_completed', 'Order Completed', 'Your order BT-C8B5D77813 has been completed. Thank you!', '{\"order_id\":7}', 1, '2026-05-15 05:31:50', '2026-05-15 05:28:31');

-- --------------------------------------------------------

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

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `status` enum('Pending','Processing','Completed','Cancelled','Shipped','Refunded') DEFAULT 'Pending',
  `payment_status` enum('unpaid','paid','partially_paid','refunded') DEFAULT 'unpaid',
  `payment_method` varchar(50) DEFAULT NULL COMMENT 'momo | bank | card',
  `payment_reference` varchar(200) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT 0.00,
  `discount` decimal(10,2) DEFAULT 0.00,
  `total` decimal(10,2) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `address` text DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `orders` (`id`, `user_id`, `reference`, `status`, `payment_status`, `payment_method`, `payment_reference`, `subtotal`, `discount`, `total`, `notes`, `address`, `phone`, `created_at`, `updated_at`) VALUES
(1, 1, 'BT-19B3A62DDD', 'Completed', 'paid', 'mobile_money', NULL, 2499.00, 0.00, 2499.00, '', '', '', '2026-05-14 18:44:06', '2026-05-14 19:15:16'),
(2, 1, 'BT-4075D60E6A', 'Completed', 'paid', 'mobile_money', NULL, 3720.00, 0.00, 3720.00, '', '', '', '2026-05-14 18:44:27', '2026-05-14 18:56:00'),
(3, 1, 'BT-79FC2B3434', 'Processing', 'paid', 'mobile_money', NULL, 5.00, 0.00, 5.00, '', '', '', '2026-05-14 19:16:00', '2026-05-14 19:18:14'),
(4, 1, 'BT-00B88C8706', 'Completed', 'paid', 'mobile_money', NULL, 5.00, 0.00, 5.00, '', '8nyati street site and service', '', '2026-05-14 19:16:31', '2026-05-14 19:19:08'),
(5, 2, 'BT-DDFE36D898', 'Pending', 'unpaid', 'lipila', 'f0afa79e-b444-4f4f-a116-91fe022d9875', 300.00, 0.00, 300.00, '', '', '0760531914', '2026-05-15 03:15:14', '2026-05-15 11:11:28'),
(6, 1, 'BT-B6A83819E2', 'Completed', 'paid', 'mobile_money', 'cdb22410-ca04-4b2f-90a9-522db7aeebe4', 5.00, 0.00, 5.00, '', '', '', '2026-05-15 04:51:26', '2026-05-15 05:27:53'),
(7, 1, 'BT-C8B5D77813', 'Completed', 'paid', 'lipila', 'a95acade-51a6-41be-8217-7f9ec9e7f1f6', 5.00, 0.00, 5.00, '', '', '', '2026-05-15 05:25:12', '2026-05-15 05:28:31');

-- --------------------------------------------------------

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

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(150) DEFAULT NULL,
  `product_type` enum('physical_product','software_product','physical_service','software_service') DEFAULT NULL,
  `qty` int(11) DEFAULT 1,
  `quantity` int(11) DEFAULT 1 COMMENT 'alias for qty',
  `price` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `order_items` (`id`, `order_id`, `product_id`, `product_name`, `product_type`, `qty`, `quantity`, `price`) VALUES
(1, 1, 1, 'Limited Stock Available Poco F8 Pro', 'physical_service', 1, 1, 2499.00),
(2, 2, 3, 'OnePlus 15R', 'physical_product', 1, 1, 3720.00),
(3, 3, 5, 'Spider charging port', 'physical_product', 1, 1, 5.00),
(4, 4, 5, 'Spider charging port', 'physical_product', 1, 1, 5.00),
(5, 5, 4, 'VIVO/REALME/OPPO LCD', 'physical_service', 1, 1, 300.00),
(6, 6, 5, 'Spider charging port', 'physical_product', 1, 1, 5.00),
(7, 7, 5, 'Spider charging port', 'physical_product', 1, 1, 5.00);

-- --------------------------------------------------------

--
-- Table structure for table `payment_settings`
--

CREATE TABLE `payment_settings` (
  `id` int(11) NOT NULL,
  `gateway_name` varchar(100) NOT NULL DEFAULT 'ZynlePay',
  `api_key` varchar(500) DEFAULT NULL COMMENT 'ZynlePay API Key',
  `api_id` varchar(255) DEFAULT NULL COMMENT 'ZynlePay API ID',
  `merchant_id` varchar(255) DEFAULT NULL COMMENT 'ZynlePay Merchant ID',
  `service_id` varchar(50) NOT NULL DEFAULT '1002',
  `channel` varchar(50) NOT NULL DEFAULT 'momo',
  `base_url` varchar(500) NOT NULL DEFAULT 'https://sandbox.zynlepay.com/api',
  `callback_url` varchar(500) DEFAULT NULL COMMENT 'Public webhook URL',
  `currency` varchar(10) NOT NULL DEFAULT 'ZMW',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `lipila_api_key` varchar(500) DEFAULT NULL COMMENT 'Lipila x-api-key',
  `lipila_base_url` varchar(500) DEFAULT 'https://api.lipila.dev' COMMENT 'Lipila API base URL',
  `lipila_callback_url` varchar(500) DEFAULT NULL COMMENT 'Lipila webhook URL',
  `lipila_redirect_url` varchar(500) DEFAULT NULL COMMENT 'Redirect after payment',
  `lipila_back_url` varchar(500) DEFAULT NULL COMMENT 'Cancel / back URL',
  `active_gateway` varchar(30) DEFAULT 'zynlepay' COMMENT 'zynlepay | lipila',
  `lenco_public_key` varchar(500) DEFAULT NULL,
  `lenco_secret_key` varchar(500) DEFAULT NULL,
  `lenco_base_url` varchar(500) DEFAULT 'https://api.lenco.co'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payment_settings`
--

INSERT INTO `payment_settings` (`id`, `gateway_name`, `api_key`, `api_id`, `merchant_id`, `service_id`, `channel`, `base_url`, `callback_url`, `currency`, `created_at`, `updated_at`, `lipila_api_key`, `lipila_base_url`, `lipila_callback_url`, `lipila_redirect_url`, `lipila_back_url`, `active_gateway`, `lenco_public_key`, `lenco_secret_key`, `lenco_base_url`) VALUES
(1, 'ZynlePay', NULL, NULL, NULL, '1002', 'momo', 'https://sandbox.zynlepay.com/api', NULL, 'ZMW', '2026-05-14 15:58:06', '2026-05-15 11:09:56', 'lsk_019db0a9-218b-7250-9321-7e25c61c687b', 'https://blz.lipila.io', 'https://clientdemo.acbizglobal.com/bubblestechs/api/payments/lipila_webhook.php', '', '', 'lenco', 'febvebve', 'password', 'https://api.lenco.co');

-- --------------------------------------------------------

--
-- Table structure for table `payment_verifications`
--

CREATE TABLE `payment_verifications` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `verified_by` int(11) NOT NULL,
  `method` enum('momo','manual_override') DEFAULT 'manual_override',
  `amount` decimal(10,2) DEFAULT NULL,
  `reference` varchar(200) DEFAULT NULL,
  `proof_image` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `verified_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

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

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `sale_price` decimal(10,2) DEFAULT NULL,
  `is_free` tinyint(1) NOT NULL DEFAULT 0,
  `category` varchar(100) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `slug` varchar(200) DEFAULT NULL,
  `type` enum('physical_product','software_product','physical_service','software_service') NOT NULL DEFAULT 'physical_product',
  `sku` varchar(100) DEFAULT NULL,
  `stock_qty` int(11) DEFAULT NULL,
  `download_url` varchar(500) DEFAULT NULL,
  `license_type` varchar(100) DEFAULT NULL,
  `version` varchar(50) DEFAULT NULL,
  `platform` varchar(100) DEFAULT NULL,
  `is_featured` tinyint(1) DEFAULT 0,
  `status` enum('active','inactive','draft') DEFAULT 'draft',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `products` (`id`, `name`, `description`, `short_description`, `price`, `sale_price`, `is_free`, `category`, `image`, `slug`, `type`, `sku`, `stock_qty`, `download_url`, `license_type`, `version`, `platform`, `is_featured`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'Limited Stock Available Poco F8 Pro', '', 'Limited Stock Available Poco F8 Pro  Get it in same day', 2940.00, 2499.00, 0, '', 'products/17880f3762800f5a2d49.jpg', 'limited-stock-available-poco-f8-pro', 'physical_service', NULL, 4, '', NULL, NULL, NULL, 0, 'active', 1, '2026-05-14 17:47:50', '2026-05-14 17:47:50'),
(2, 'Starsat Sr5090 extreme', '', '', 1299.00, NULL, 0, '', 'products/a2b4de2e10971717bd41.jpg', 'starsat-sr5090-extreme', 'physical_service', NULL, 10, '', NULL, NULL, NULL, 0, 'active', 1, '2026-05-14 17:51:54', '2026-05-14 17:54:12'),
(3, 'OnePlus 15R', '', 'Product Overview OnePlus 15R Price in Nepal The OnePlus 15R is a high-performance OnePlus mobile designed for users who want flagship features at a slightly lower price. As part of the OnePlus R series, this OnePlus 15R is powered by the Snapdragon 8 Gen 5 processor.  The 6.83-inch AMOLED display with 165Hz refresh rate ensures smooth visuals and high brightness. The OnePlus 15R is ideal for gaming and performance-heavy usage.  With a large 7400mAh battery and fast charging, this OnePlus mobile ', 3900.00, 3720.00, 0, '', 'products/d3d70493e26fc932dda4.png', 'oneplus-15r', 'physical_product', NULL, 1, '', NULL, NULL, NULL, 0, 'active', 1, '2026-05-14 18:15:23', '2026-05-14 18:44:27'),
(4, 'VIVO/REALME/OPPO LCD', '', 'Assembly lcd.', 300.00, NULL, 0, 'LCD', 'products/314116618b9886a498d0.jpg', 'vivo-realme-oppo-lcd', 'physical_service', NULL, 4, '', NULL, NULL, NULL, 0, 'active', 1, '2026-05-14 18:18:58', '2026-05-14 18:30:05'),
(5, 'Spider charging port', '', 'Pcba sub', 1.00, NULL, 0, 'Accessories', 'products/3f7c3791e949b8a18ae8.jpg', 'spider-charging-port', 'physical_product', NULL, NULL, '', NULL, NULL, NULL, 0, 'active', 1, '2026-05-14 19:05:18', '2026-05-15 05:31:06');

-- --------------------------------------------------------

--
-- Table structure for table `product_images`
--

CREATE TABLE `product_images` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `image` varchar(255) NOT NULL,
  `alt_text` varchar(255) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `site_settings`
--

CREATE TABLE `site_settings` (
  `id` int(11) NOT NULL,
  `site_name` varchar(150) DEFAULT 'NexTech Store',
  `logo` varchar(255) DEFAULT NULL,
  `favicon` varchar(255) DEFAULT NULL,
  `currency` varchar(10) DEFAULT 'ZMW',
  `currency_symbol` varchar(10) DEFAULT 'K',
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `tagline` varchar(300) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(300) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `primary_color` varchar(20) DEFAULT '#1a56ff'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `site_settings`
--

INSERT INTO `site_settings` (`id`, `site_name`, `logo`, `favicon`, `currency`, `currency_symbol`, `updated_by`, `updated_at`, `tagline`, `email`, `phone`, `address`, `description`, `primary_color`) VALUES
(1, 'Bubblestech', NULL, NULL, 'ZMW', 'K', 1, '2026-05-14 19:14:03', '', '', '+260973594514', 'Chingola,copperbelt Zambia', '', '#1a56ff');

-- --------------------------------------------------------

--
-- Table structure for table `social_links`
--

CREATE TABLE `social_links` (
  `id` int(11) NOT NULL,
  `platform` varchar(50) NOT NULL COMMENT 'Facebook | Instagram | WhatsApp | X | TikTok | LinkedIn | YouTube',
  `url` varchar(500) NOT NULL,
  `icon` varchar(50) DEFAULT NULL COMMENT 'Icon identifier',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

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

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `reference` varchar(100) NOT NULL COMMENT 'Matches orders.reference',
  `order_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `phone` varchar(30) DEFAULT NULL,
  `network` varchar(30) DEFAULT NULL COMMENT 'AIRTEL | MTN | ZAMTEL',
  `status` enum('pending','success','failed') NOT NULL DEFAULT 'pending',
  `provider_transaction_id` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `transactions` (`id`, `reference`, `order_id`, `amount`, `phone`, `network`, `status`, `provider_transaction_id`, `created_at`) VALUES
(1, 'MANUAL-4BA90B03', 2, 3720.00, '', 'MANUAL', 'success', 'manual_by_Admin', '2026-05-14 18:44:58'),
(2, 'MANUAL-C712D66F', 1, 2499.00, '', 'MANUAL', 'success', 'manual_by_Admin', '2026-05-14 18:57:57'),
(3, 'MANUAL-9468B9AD', 4, 5.00, '', 'MANUAL', 'success', 'manual_by_Admin', '2026-05-14 19:17:21'),
(4, 'MANUAL-4268A108', 3, 5.00, '', 'MANUAL', 'success', 'manual_by_Admin', '2026-05-14 19:18:14'),
(5, '4f0cee52-7c1b-43d5-a485-1f7eafded7ab', 5, 300.00, '260760531914', 'MOMO', 'failed', NULL, '2026-05-15 04:30:49'),
(6, '07c75218-76bd-4857-90ed-4b862ef46e32', 5, 300.00, '260760531914', 'MOMO', 'failed', NULL, '2026-05-15 04:37:58'),
(7, '6df147fc-7766-4e34-9cac-f1d06ec9c7d2', 5, 300.00, '260957963819', 'MOMO', 'pending', 'LPLXC-20260515-045209-701-3648', '2026-05-15 04:52:09'),
(8, 'cdb22410-ca04-4b2f-90a9-522db7aeebe4', 6, 5.00, '260973594514', 'MOMO', 'pending', 'LPLXC-20260515-045223-445-5032', '2026-05-15 04:52:22'),
(9, 'a95acade-51a6-41be-8217-7f9ec9e7f1f6', 7, 5.00, '260969856220', 'MOMO', 'pending', 'LPLXC-20260515-052547-663-9208', '2026-05-15 05:25:46'),
(10, 'MANUAL-169F0FCD', 6, 5.00, '', 'MANUAL', 'success', 'manual_by_Admin', '2026-05-15 05:27:44'),
(11, '03046c06-9df0-4246-9176-834b5c7ddba7', 5, 300.00, '260760531914', 'MOMO', 'pending', 'LPLXC-20260515-111107-192-6528', '2026-05-15 11:11:06'),
(12, 'f0afa79e-b444-4f4f-a116-91fe022d9875', 5, 300.00, '260760531914', 'MOMO', 'pending', 'LPLXC-20260515-111128-411-2222', '2026-05-15 11:11:28');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `status` enum('active','suspended') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `email`, `password`, `first_name`, `last_name`, `phone`, `avatar`, `email_verified_at`, `status`, `created_at`) VALUES
(1, 'jamessinkamba123@gmail.com', '$2y$10$5F/.eL.RiCWaT7sU1wqNXeX/e2nIs/hugdX5TlB1ounLV535HSLa6', 'James', 'Sinkamba', '', NULL, NULL, 'active', '2026-05-14 18:43:27'),
(2, 'kamuzusilungwe@gmail.com', '$2y$10$4vDwrjHTgicVq2koVs.unO5njZBL.Hr5qxips/fntks3qT0M8ewrC', 'elisha', 'silungwe', '0760531914', NULL, NULL, 'active', '2026-05-15 03:14:17');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `carousel`
--
ALTER TABLE `carousel`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `recipient` (`recipient_type`,`recipient_id`),
  ADD KEY `is_read` (`is_read`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `reference` (`reference`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `payment_settings`
--
ALTER TABLE `payment_settings`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `payment_verifications`
--
ALTER TABLE `payment_verifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `verified_by` (`verified_by`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `status` (`status`),
  ADD KEY `is_featured` (`is_featured`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `product_images`
--
ALTER TABLE `product_images`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `sort_order` (`sort_order`);

--
-- Indexes for table `site_settings`
--
ALTER TABLE `site_settings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `updated_by` (`updated_by`);

--
-- Indexes for table `social_links`
--
ALTER TABLE `social_links`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `platform` (`platform`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `reference` (`reference`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `status` (`status`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admins`
--
ALTER TABLE `admins`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `carousel`
--
ALTER TABLE `carousel`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `order_items`
--
ALTER TABLE `order_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `payment_settings`
--
ALTER TABLE `payment_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `payment_verifications`
--
ALTER TABLE `payment_verifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `product_images`
--
ALTER TABLE `product_images`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `site_settings`
--
ALTER TABLE `site_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `social_links`
--
ALTER TABLE `social_links`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transactions`
--
ALTER TABLE `transactions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `payment_verifications`
--
ALTER TABLE `payment_verifications`
  ADD CONSTRAINT `pv_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `pv_ibfk_2` FOREIGN KEY (`verified_by`) REFERENCES `admins` (`id`);

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `product_images`
--
ALTER TABLE `product_images`
  ADD CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `site_settings`
--
ALTER TABLE `site_settings`
  ADD CONSTRAINT `site_settings_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `transactions`
--
ALTER TABLE `transactions`
  ADD CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;