-- ══════════════════════════════════════════════════════════
-- Phase 4: Subscriptions (customer ↔ package ↔ router link)
-- ══════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint UNSIGNED NOT NULL DEFAULT 1,
  `customer_name` varchar(150) NOT NULL,
  `customer_phone` varchar(30) NOT NULL,
  `package_id` bigint UNSIGNED NOT NULL,
  `router_id` bigint UNSIGNED NOT NULL,
  `type` enum('hotspot','pppoe') NOT NULL DEFAULT 'hotspot',
  `mt_username` varchar(100) NOT NULL,
  `mt_password` varchar(100) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL DEFAULT 0.00,
  `starts_at` timestamp NULL DEFAULT NULL,
  `ends_at` timestamp NULL DEFAULT NULL,
  `status` enum('active','expired','cancelled') NOT NULL DEFAULT 'active',
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_phone` (`customer_phone`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;