-- Users table
CREATE TABLE IF NOT EXISTS `users` (
  `emp_id` int NOT NULL AUTO_INCREMENT,
  `email_id` varchar(255) NOT NULL,
  `pwd` varchar(255) NOT NULL,
  `role` varchar(20) NOT NULL,
  `dept` varchar(20) NOT NULL,
  `emp_name` varchar(255) NOT NULL,
  PRIMARY KEY (`emp_id`),
  UNIQUE KEY `email_id` (`email_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Enterprise info (pre-requisition / site survey)
CREATE TABLE IF NOT EXISTS `enterprise_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `enterprise_name` varchar(255) NOT NULL,
  `isp_of_enterprise` varchar(255) DEFAULT NULL,
  `isp_used_for_service` varchar(255) DEFAULT NULL,
  `bandwidth` varchar(100) DEFAULT NULL,
  `internet_service_type` enum('ILL','Broadband') DEFAULT NULL,
  `static_ip` varchar(50) DEFAULT NULL,
  `natting_status` enum('Natting','No Natting') DEFAULT NULL,
  `firewall_name` varchar(255) DEFAULT NULL,
  `firewall_ip` varchar(50) DEFAULT NULL,
  `it_person_name` varchar(255) DEFAULT NULL,
  `contact_number` varchar(20) DEFAULT NULL,
  `communication_email` varchar(255) DEFAULT NULL,
  `device_type` varchar(100) DEFAULT NULL,
  `number_of_devices` int DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Customer Application Forms (CAFs)
CREATE TABLE IF NOT EXISTS `customer_cafs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `enterprise_name` varchar(255) NOT NULL,
  `place` varchar(255) DEFAULT NULL,
  `constitution_of_firm` varchar(255) DEFAULT NULL,
  `sectors` varchar(255) DEFAULT NULL,
  `pan_no` varchar(50) DEFAULT NULL,
  `gst_no` varchar(50) DEFAULT NULL,
  `postal_address` text DEFAULT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `mobile_no` varchar(15) DEFAULT NULL,
  `email_address` varchar(255) DEFAULT NULL,
  `customer_type` varchar(100) DEFAULT NULL,
  `plan` varchar(100) DEFAULT NULL,
  `concurrent_channels` int DEFAULT NULL,
  `digital_extensions` int DEFAULT NULL,
  `sourcing_date` date DEFAULT NULL,
  `sourcing_month` varchar(100) DEFAULT NULL,
  `activation_date` date DEFAULT NULL,
  `activated_month` varchar(100) DEFAULT NULL,
  `activation_status` varchar(100) DEFAULT NULL,
  `billing_cycle_status` varchar(100) DEFAULT NULL,
  `pilot_no` varchar(100) DEFAULT NULL,
  `billing_account_no` varchar(100) DEFAULT NULL,
  `did_numbers` varchar(255) DEFAULT NULL,
  `sip_no` varchar(100) DEFAULT NULL,
  `account_name` varchar(255) DEFAULT NULL,
  `hardware_devices` varchar(255) DEFAULT NULL,
  `hardware_model` varchar(255) DEFAULT NULL,
  `firewall` varchar(100) DEFAULT NULL,
  `stun` varchar(100) DEFAULT NULL,
  `primary_sbc` varchar(100) DEFAULT NULL,
  `secondary_sbc` varchar(100) DEFAULT NULL,
  `industry_vertical` varchar(100) DEFAULT NULL,
  `nature_of_account` varchar(100) DEFAULT NULL,
  `extn_series` varchar(100) DEFAULT NULL,
  `app_in_use` varchar(255) DEFAULT NULL,
  `support_plan` varchar(100) DEFAULT NULL,
  `hardware_qty` int DEFAULT NULL,
  `isp_data` varchar(255) DEFAULT NULL,
  `ip_data` varchar(255) DEFAULT NULL,
  `static_ip` varchar(50) DEFAULT NULL,
  `bandwidth_capacity` varchar(100) DEFAULT NULL,
  `broadband_type` varchar(100) DEFAULT NULL,
  `leased_line_type` varchar(100) DEFAULT NULL,
  `comm_protocol` varchar(100) DEFAULT NULL,
  `device_type` varchar(100) DEFAULT NULL,
  `additional_remarks` text DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Customer accounts (billing-focused)
CREATE TABLE IF NOT EXISTS `customer_accounts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `billing_account_no` varchar(20) NOT NULL,
  `enterprise_name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `mobile_no` varchar(40) DEFAULT NULL,
  `account_name` varchar(100) DEFAULT NULL,
  `plan` decimal(10,2) DEFAULT NULL,
  `concurrent_channels` int DEFAULT NULL,
  `digital_extensions` int DEFAULT NULL,
  `activation_status` varchar(20) DEFAULT NULL,
  `billing_cycle` varchar(50) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `pilot_no` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pilot_no` (`pilot_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Device configuration details
CREATE TABLE IF NOT EXISTS `devices_details` (
  `id` int NOT NULL AUTO_INCREMENT,
  `account_name` varchar(255) DEFAULT NULL,
  `extensions` varchar(255) DEFAULT NULL,
  `end_devices_used` varchar(255) DEFAULT NULL,
  `provisioner` varchar(255) DEFAULT NULL,
  `audio_codec_cpbx` varchar(255) DEFAULT NULL,
  `video_codec_cpbx` varchar(255) DEFAULT NULL,
  `hardware_model` varchar(255) DEFAULT NULL,
  `transport_protocol` varchar(255) DEFAULT NULL,
  `audio_codecs_hardware` varchar(255) DEFAULT NULL,
  `stun_server` varchar(255) DEFAULT NULL,
  `primary_sbc` varchar(255) DEFAULT NULL,
  `secondary_sbc1` varchar(255) DEFAULT NULL,
  `acc_id` varchar(255) DEFAULT NULL,
  `heartbeat` varchar(255) DEFAULT NULL,
  `send_sip_unregistration_request` varchar(255) DEFAULT NULL,
  `re_register_before_expiration` varchar(255) DEFAULT NULL,
  `add_auth_header_on_initial_register` varchar(255) DEFAULT NULL,
  `registration_retry_wait_time` varchar(255) DEFAULT NULL,
  `registration_refresh_interval` varchar(255) DEFAULT NULL,
  `initial_registration_with_auth` varchar(255) DEFAULT NULL,
  `reply_182_on_call_waiting` varchar(255) DEFAULT NULL,
  `proxy_dns_type` varchar(255) DEFAULT NULL,
  `use_ob_in_dialog` varchar(255) DEFAULT NULL,
  `enable_registration_subscription` varchar(255) DEFAULT NULL,
  `registration_limit` varchar(255) DEFAULT NULL,
  `registration_expires` varchar(255) DEFAULT NULL,
  `device_added` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Monthly billing records per customer account
CREATE TABLE IF NOT EXISTS `billing_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_account_id` int NOT NULL,
  `month` tinyint NOT NULL,
  `year` smallint NOT NULL DEFAULT (YEAR(CURDATE())),
  `amount_paid` decimal(10,2) DEFAULT 0.00,
  `payment_mode` varchar(50) DEFAULT NULL,
  `payment_status` varchar(50) DEFAULT 'Unpaid',
  `due_amount` decimal(10,2) DEFAULT 0.00,
  `balance_amount` varchar(20) DEFAULT NULL,
  `month_invoice_amount` decimal(10,2) DEFAULT 0.00,
  `month_invoice_no` varchar(50) DEFAULT NULL,
  `invoice_generated_date` date DEFAULT NULL,
  `payment_due_date` date DEFAULT NULL,
  `bill_generated` varchar(20) DEFAULT 'No',
  `eligible_for_payment` varchar(20) DEFAULT 'No',
  `bills_status` varchar(50) DEFAULT 'Pending',
  `billing_issue` varchar(255) DEFAULT NULL,
  `mail_status` varchar(50) DEFAULT 'Not Sent',
  `mail_info` varchar(255) DEFAULT NULL,
  `whatsapp_status` varchar(50) DEFAULT 'Not Sent',
  `whatsapp_info` varchar(255) DEFAULT NULL,
  `previous_amount` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customer_account_id` (`customer_account_id`, `month`, `year`),
  CONSTRAINT `billing_info_ibfk_1` FOREIGN KEY (`customer_account_id`) REFERENCES `customer_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Email/WhatsApp send tracking per billing period
CREATE TABLE IF NOT EXISTS `billing_communications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_account_id` int NOT NULL,
  `billing_account_no` varchar(20) NOT NULL,
  `month` varchar(20) NOT NULL,
  `billing_period` char(7) DEFAULT NULL,
  `email_sent` tinyint(1) DEFAULT 0,
  `email_sent_at` datetime DEFAULT NULL,
  `email_error` text DEFAULT NULL,
  `whatsapp_sent` tinyint(1) DEFAULT 0,
  `whatsapp_sent_at` datetime DEFAULT NULL,
  `whatsapp_error` text DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_account_month` (`billing_account_no`, `month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tiered pricing for concurrent channels
CREATE TABLE IF NOT EXISTS `concurrent_channels_pricing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `min_channels` int NOT NULL,
  `max_channels` int DEFAULT NULL,
  `cost_per_channel` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tiered pricing for digital extensions
CREATE TABLE IF NOT EXISTS `digital_extension_pricing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `min_extensions` int NOT NULL,
  `max_extensions` int DEFAULT NULL,
  `cost_per_extension` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
