-- ============================================================
-- Redsom Shop Database
-- Import: select redsom_tvpanel DB → import this file
-- ============================================================

-- ── shop_products ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `shop_products` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `category` varchar(100) DEFAULT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `stock` int NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── shop_orders ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `shop_orders` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_ref` varchar(30) NOT NULL UNIQUE,
  `product_id` bigint UNSIGNED NOT NULL,
  `product_name` varchar(200) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `quantity` int NOT NULL DEFAULT 1,
  `total` decimal(10,2) NOT NULL,
  `status` enum('pending','paid','failed','cancelled') DEFAULT 'pending',
  `waafi_ref` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Sample products ──────────────────────────────────────────
INSERT IGNORE INTO `shop_products` (`name`,`description`,`price`,`category`,`image_url`,`stock`,`is_active`,`sort_order`,`created_at`,`updated_at`) VALUES
('WiFi Router TP-Link',  'TP-Link AC750 Dual Band WiFi Router',          45.00, 'Networking',    '', 10, 1, 1, NOW(), NOW()),
('Network Switch 8-Port','8-Port Fast Ethernet Unmanaged Switch',         25.00, 'Networking',    '', 15, 1, 2, NOW(), NOW()),
('UTP Cable (1m)',       'Cat6 UTP Ethernet Cable 1 Meter',               1.50,  'Cables',        '', 100,1, 3, NOW(), NOW()),
('Power Bank 20000mAh', 'High Capacity Portable Charger',                 18.00, 'Accessories',   '', 20, 1, 4, NOW(), NOW()),
('USB Hub 4-Port',       '4-Port USB 3.0 Hub with Power Supply',          8.00,  'Accessories',   '', 30, 1, 5, NOW(), NOW()),
('CCTV Camera',          '2MP HD Indoor/Outdoor Security Camera',         35.00, 'Security',      '', 8,  1, 6, NOW(), NOW());