CREATE TABLE companies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  email VARCHAR(255) NULL,
  phone VARCHAR(255) NULL,
  country VARCHAR(255) NULL,
  timezone VARCHAR(100) NOT NULL DEFAULT 'Africa/Dar_es_Salaam',
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  logo_path VARCHAR(255) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  phone VARCHAR(255) NULL,
  password VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  last_login_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
);

CREATE TABLE plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(100) NOT NULL UNIQUE,
  router_limit INT UNSIGNED NOT NULL DEFAULT 5,
  price_monthly DECIMAL(12,2) NOT NULL DEFAULT 0,
  price_yearly DECIMAL(12,2) NOT NULL DEFAULT 0,
  features_json JSON NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

CREATE TABLE subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  starts_at TIMESTAMP NOT NULL,
  ends_at TIMESTAMP NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'trial',
  billing_cycle VARCHAR(50) NOT NULL DEFAULT 'monthly',
  router_limit INT UNSIGNED NOT NULL DEFAULT 5,
  grace_ends_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_subscriptions_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_subscriptions_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE
);

CREATE TABLE router_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_router_groups_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE routers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  group_id BIGINT UNSIGNED NULL,
  name VARCHAR(255) NOT NULL,
  site_name VARCHAR(255) NULL,
  location VARCHAR(255) NULL,
  model VARCHAR(255) NULL,
  serial_number VARCHAR(255) NULL,
  ros_version VARCHAR(100) NULL,
  lan_ip VARCHAR(45) NULL,
  api_port INT UNSIGNED NOT NULL DEFAULT 8728,
  webfig_port INT UNSIGNED NOT NULL DEFAULT 80,
  winbox_port INT UNSIGNED NOT NULL DEFAULT 8291,
  username VARCHAR(255) NOT NULL,
  password TEXT NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  last_seen_at TIMESTAMP NULL,
  onboard_token VARCHAR(80) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_routers_onboard_token (onboard_token),
  CONSTRAINT fk_routers_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_routers_group FOREIGN KEY (group_id) REFERENCES router_groups(id) ON DELETE SET NULL
);

CREATE TABLE router_tunnels (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  router_id BIGINT UNSIGNED NOT NULL,
  tunnel_type VARCHAR(50) NOT NULL DEFAULT 'wireguard',
  tunnel_ip VARCHAR(45) NULL,
  public_key TEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  connected_at TIMESTAMP NULL,
  last_handshake_at TIMESTAMP NULL,
  server_node VARCHAR(100) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_router_tunnels_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE
);

CREATE TABLE router_metrics (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  router_id BIGINT UNSIGNED NOT NULL,
  cpu_usage TINYINT UNSIGNED NULL,
  memory_usage TINYINT UNSIGNED NULL,
  disk_usage TINYINT UNSIGNED NULL,
  uptime_seconds BIGINT UNSIGNED NULL,
  temperature TINYINT UNSIGNED NULL,
  rx_bps BIGINT UNSIGNED NULL,
  tx_bps BIGINT UNSIGNED NULL,
  hotspot_users INT UNSIGNED NULL,
  pppoe_users INT UNSIGNED NULL,
  latency_ms INT UNSIGNED NULL,
  packet_loss TINYINT UNSIGNED NULL,
  recorded_at TIMESTAMP NOT NULL,
  INDEX idx_router_metrics_recorded_at (recorded_at),
  CONSTRAINT fk_router_metrics_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE
);

CREATE TABLE router_backups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  router_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(50) NOT NULL DEFAULT 'binary',
  filename VARCHAR(255) NOT NULL,
  storage_path VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'queued',
  size_bytes BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_router_backups_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE,
  CONSTRAINT fk_router_backups_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE router_alerts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  router_id BIGINT UNSIGNED NOT NULL,
  severity VARCHAR(50) NOT NULL DEFAULT 'low',
  type VARCHAR(100) NOT NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'open',
  triggered_at TIMESTAMP NULL,
  resolved_at TIMESTAMP NULL,
  acknowledged_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_router_alerts_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_router_alerts_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE,
  CONSTRAINT fk_router_alerts_ack_user FOREIGN KEY (acknowledged_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE config_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  category VARCHAR(100) NOT NULL,
  content LONGTEXT NOT NULL,
  is_global TINYINT(1) NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_config_templates_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_config_templates_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE config_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  router_id BIGINT UNSIGNED NOT NULL,
  template_id BIGINT UNSIGNED NULL,
  command_text LONGTEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'queued',
  result_log LONGTEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  executed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_config_jobs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_config_jobs_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE,
  CONSTRAINT fk_config_jobs_template FOREIGN KEY (template_id) REFERENCES config_templates(id) ON DELETE SET NULL,
  CONSTRAINT fk_config_jobs_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  router_id BIGINT UNSIGNED NULL,
  channel VARCHAR(50) NOT NULL,
  recipient VARCHAR(255) NULL,
  subject VARCHAR(255) NULL,
  message LONGTEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'queued',
  sent_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_notifications_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_notifications_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
);

CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NULL,
  amount DECIMAL(12,2) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'TZS',
  method VARCHAR(100) NULL,
  reference VARCHAR(255) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  paid_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_payments_reference (reference),
  CONSTRAINT fk_payments_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
);

CREATE TABLE invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NULL,
  invoice_no VARCHAR(100) NOT NULL UNIQUE,
  amount DECIMAL(12,2) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'TZS',
  due_date DATE NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'unpaid',
  pdf_path VARCHAR(255) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_invoices_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_invoices_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
);

CREATE TABLE activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  router_id BIGINT UNSIGNED NULL,
  action VARCHAR(255) NOT NULL,
  description TEXT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_activity_logs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  CONSTRAINT fk_activity_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_activity_logs_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
);
