-- ══════════════════════════════════════════════════════════
-- REDSOM SAAS — FULL FRESH INSTALL
-- Import this ONE file into a brand new empty database.
-- ══════════════════════════════════════════════════════════

-- ── admin_users ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint UNSIGNED DEFAULT NULL,
  `role` varchar(20) DEFAULT 'admin',
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default admin: password = "password"
INSERT IGNORE INTO `admin_users` (`name`,`email`,`password`,`company_id`,`role`,`created_at`,`updated_at`) VALUES
('Admin','admin@saas.redsom.so','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',1,'owner',NOW(),NOW());

-- ── companies (multi-tenant) ─────────────────────────────────
CREATE TABLE IF NOT EXISTS `companies` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `logo_url` varchar(500) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `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 `companies_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `companies` (`id`,`name`,`slug`,`phone`,`is_active`,`created_at`,`updated_at`)
VALUES (1,'Redsom Group','redsom','0645555',1,NOW(),NOW());

-- ── routers ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `routers` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `ip_address` varchar(50) NOT NULL,
  `api_port` int NOT NULL DEFAULT 8728,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `use_ssl` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('online','offline','unknown') DEFAULT 'unknown',
  `last_checked_at` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── ip_pools ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ip_pools` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `router_id` bigint UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `range_start` varchar(50) NOT NULL,
  `range_end` varchar(50) NOT NULL,
  `type` enum('hotspot','pppoe') NOT NULL DEFAULT 'pppoe',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_router` (`router_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── tv_channels ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `tv_channels` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(120) NOT NULL,
  `category` enum('news','sport','somali','movie','other') NOT NULL DEFAULT 'other',
  `logo_emoji` varchar(10) NOT NULL DEFAULT '📺',
  `bg_color` varchar(10) NOT NULL DEFAULT '#1a0a2e',
  `stream_url` text DEFAULT NULL,
  `is_free` tinyint(1) 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`),
  UNIQUE KEY `tv_channels_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `tv_channels` (`name`,`slug`,`category`,`logo_emoji`,`bg_color`,`stream_url`,`is_free`,`is_active`,`sort_order`,`created_at`,`updated_at`) VALUES
('Al Jazeera',   'aljazeera',   'news',   '🌍','#0d2240','https://www.youtube.com/embed/live_stream?channel=UCNye-wNBqNL5ZzHSJdpkDEA',0,1,1,NOW(),NOW()),
('Universal TV', 'universal-tv','somali', '📡','#1a0a2e',NULL,1,1,2,NOW(),NOW()),
('beIN Sports 1','bein-sport1', 'sport',  '⚽','#003018','http://197.220.92.166:91/Plus/beIN_Sports1_HD-ar/tracks-v1a1/mono.ts.m3u8',1,1,3,NOW(),NOW()),
('MBC 1',        'mbc1',        'movie',  '🎬','#1a0840',NULL,0,1,4,NOW(),NOW()),
('Horn Cable TV','horn-cable',  'somali', '🦁','#1a1000',NULL,1,1,5,NOW(),NOW()),
('BBC Arabic',   'bbc-arabic',  'news',   '🌐','#00172e',NULL,0,1,6,NOW(),NOW()),
('Somalia 1 TV', 'somalia1',    'somali', '🌙','#0a1a0a',NULL,1,1,7,NOW(),NOW()),
('Al Arabiya',   'alarabiya',   'news',   '📰','#1a0000',NULL,0,1,8,NOW(),NOW()),
('AFC Sport',    'afc-sport',   'sport',  '🏆','#001a0a',NULL,0,1,9,NOW(),NOW()),
('MBC 2 Movies', 'mbc2',        'movie',  '🎥','#0d0030',NULL,0,1,10,NOW(),NOW());

-- ── 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`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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());

-- ── shop_orders ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `shop_orders` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_ref` varchar(30) NOT NULL,
  `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','shipped','delivered','failed','cancelled') DEFAULT 'pending',
  `waafi_ref` varchar(100) DEFAULT NULL,
  `sms_sent` tinyint(1) DEFAULT 0,
  `refund_note` varchar(255) DEFAULT NULL,
  `refunded_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shop_orders_ref_unique` (`order_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── shop_categories ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `shop_categories` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `emoji` varchar(10) NOT NULL DEFAULT '📦',
  `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`),
  UNIQUE KEY `shop_categories_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `shop_categories` (`name`,`emoji`,`is_active`,`sort_order`,`created_at`,`updated_at`) VALUES
('Networking',  '📡', 1, 1, NOW(), NOW()),
('Cables',      '🔌', 1, 2, NOW(), NOW()),
('Accessories', '🎧', 1, 3, NOW(), NOW()),
('Security',    '📷', 1, 4, NOW(), NOW());

-- ── net_packages ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `net_packages` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `validity` varchar(50) NOT NULL,
  `plan_id` int NOT NULL DEFAULT 1,
  `is_hot` tinyint(1) 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;

INSERT IGNORE INTO `net_packages` (`id`,`name`,`price`,`validity`,`plan_id`,`is_hot`,`is_active`,`sort_order`,`created_at`,`updated_at`) VALUES
(1,'30 Daqiiqo (Welcome)',0.01,'30 Min',1,1,1,1,NOW(),NOW()),
(2,'3 Saac',              0.05,'3 Hours',2,0,1,2,NOW(),NOW()),
(3,'1 Maalin',            0.25,'1 Day',3,0,1,3,NOW(),NOW()),
(4,'3 Maalmood',          0.50,'3 Days',4,0,1,4,NOW(),NOW()),
(5,'1 Usbuuc',            1.00,'7 Days',5,0,1,5,NOW(),NOW()),
(6,'1 Bil',               3.00,'30 Days',6,0,1,6,NOW(),NOW());

-- ── app_settings (Integrations: SMS, EVC, Backup) ────────────
CREATE TABLE IF NOT EXISTS `app_settings` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `app_settings_key_unique` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `app_settings` (`setting_key`,`setting_value`,`updated_at`) VALUES
('sms_api_url',   'https://smsapi.hormuud.com/api/SendSMS', NOW()),
('sms_user',      '', NOW()),
('sms_pass',      '', NOW()),
('sms_sender_id', '', NOW()),
('evc_api_url',   'https://api.waafipay.net/asm', NOW()),
('evc_merchant_uid', 'M0913862', NOW()),
('evc_api_user_id',  '1007968', NOW()),
('evc_api_key',      'API-1629712214AHX', NOW()),
('backup_email',     '', NOW()),
('backup_cron_key',  '', NOW());

-- ══════════════════════════════════════════════════════════
-- DONE — Login with:
--   Email:    admin@saas.redsom.so
--   Password: password
-- (beddel marka hore login gareysid!)
-- ══════════════════════════════════════════════════════════