-- ══════════════════════════════════════════════════════════
-- Import into redsom_shop1 — adds ISP (Router/Hotspot/PPPoE)
-- + Package speed fields that were only added to redsom_saas1
-- ══════════════════════════════════════════════════════════

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

ALTER TABLE `admin_users`
  ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED DEFAULT NULL AFTER `id`,
  ADD COLUMN IF NOT EXISTS `role` VARCHAR(20) DEFAULT 'admin' AFTER `company_id`;

UPDATE `admin_users` SET `company_id` = 1, `role` = 'owner' WHERE `company_id` IS NULL;

-- ── 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;

-- ── net_packages (create fresh if missing) ─────────────────────
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,
  `download_speed` varchar(10) DEFAULT NULL,
  `upload_speed` varchar(10) DEFAULT NULL,
  `router_id` bigint UNSIGNED DEFAULT NULL,
  `mikrotik_profile` varchar(100) DEFAULT 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;

-- If net_packages already existed without the speed columns, add them:
ALTER TABLE `net_packages`
  ADD COLUMN IF NOT EXISTS `download_speed` VARCHAR(10) DEFAULT NULL AFTER `validity`,
  ADD COLUMN IF NOT EXISTS `upload_speed`   VARCHAR(10) DEFAULT NULL AFTER `download_speed`,
  ADD COLUMN IF NOT EXISTS `router_id`      BIGINT UNSIGNED DEFAULT NULL AFTER `upload_speed`,
  ADD COLUMN IF NOT EXISTS `mikrotik_profile` VARCHAR(100) DEFAULT NULL AFTER `router_id`;

-- Seed default packages if table is empty
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());