-- ══════════════════════════════════════════════════════════
-- ISP SaaS — PHASE 1: Multi-Company + Routers + IP Pools
-- ══════════════════════════════════════════════════════════

-- ── 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 UNIQUE,
  `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Link admin_users to a company (multi-tenant scoping)
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`;

-- ── routers (MikroTik devices per company) ───────────────────
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 (per router) ─────────────────────────────────────
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;

-- ── Seed: default company (link existing admin to it) ─────────
INSERT IGNORE INTO `companies` (`id`,`name`,`slug`,`phone`,`is_active`,`created_at`,`updated_at`)
VALUES (1,'Redsom Group','redsom','0645555',1,NOW(),NOW());

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