<?php
// Script para criar tabelas e dados iniciais
require_once __DIR__ . '/db.php';

$pdo = db();

// USERS
$pdo->exec("
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','user') NOT NULL DEFAULT 'user',
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

// PLANS
$pdo->exec("
CREATE TABLE IF NOT EXISTS plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    max_ips INT NOT NULL,
    min_interval_seconds INT NOT NULL,
    allow_email TINYINT(1) NOT NULL DEFAULT 1,
    allow_webhook TINYINT(1) NOT NULL DEFAULT 0,
    allow_sms TINYINT(1) NOT NULL DEFAULT 0,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

// SUBSCRIPTIONS
$pdo->exec("
CREATE TABLE IF NOT EXISTS subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

// MONITORED IPS
$pdo->exec("
CREATE TABLE IF NOT EXISTS monitored_ips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    label VARCHAR(150) NOT NULL,
    host VARCHAR(255) NOT NULL,
    check_method ENUM('auto','icmp','tcp') NOT NULL DEFAULT 'auto',
    interval_seconds INT NOT NULL,
    timeout_ms INT NOT NULL DEFAULT 1000,
    alert_email VARCHAR(150) DEFAULT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    last_status ENUM('online','offline','unknown') NOT NULL DEFAULT 'unknown',
    last_latency_ms INT DEFAULT NULL,
    last_check_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

// PING LOGS
$pdo->exec("
CREATE TABLE IF NOT EXISTS ping_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    monitored_ip_id INT NOT NULL,
    status ENUM('online','offline') NOT NULL,
    latency_ms INT DEFAULT NULL,
    checked_at DATETIME NOT NULL,
    FOREIGN KEY (monitored_ip_id) REFERENCES monitored_ips(id) ON DELETE CASCADE,
    INDEX idx_monitored_ip (monitored_ip_id),
    INDEX idx_checked_at (checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

// ALERT CHANNELS
  $pdo->exec("
  CREATE TABLE IF NOT EXISTS alert_channels (
      id INT AUTO_INCREMENT PRIMARY KEY,
      monitored_ip_id INT NOT NULL,
      type ENUM('email','webhook','sms','whatsapp') NOT NULL,
      target VARCHAR(255) NOT NULL,
      min_failures INT NOT NULL DEFAULT 1,
      cooldown_minutes INT NOT NULL DEFAULT 0,
      silence_start TIME NULL,
      silence_end TIME NULL,
      active TINYINT(1) NOT NULL DEFAULT 1,
      consecutive_failures INT NOT NULL DEFAULT 0,
      last_sent_at DATETIME NULL,
      FOREIGN KEY (monitored_ip_id) REFERENCES monitored_ips(id) ON DELETE CASCADE,
      INDEX idx_monitored_ip (monitored_ip_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("ALTER TABLE alert_channels MODIFY COLUMN type ENUM('email','webhook','sms','whatsapp') NOT NULL");

// ALERTS
  $pdo->exec("
  CREATE TABLE IF NOT EXISTS alerts_sent (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      monitored_ip_id INT NOT NULL,
      channel_id INT NULL,
      type ENUM('email','webhook','sms','whatsapp') NOT NULL,
      status ENUM('success','failed') NOT NULL DEFAULT 'success',
      error_message TEXT NULL,
      message TEXT NOT NULL,
      sent_at DATETIME NOT NULL,
      severity ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
      FOREIGN KEY (monitored_ip_id) REFERENCES monitored_ips(id) ON DELETE CASCADE,
      FOREIGN KEY (channel_id) REFERENCES alert_channels(id) ON DELETE SET NULL,
      INDEX idx_monitored_ip (monitored_ip_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("ALTER TABLE alerts_sent MODIFY COLUMN type ENUM('email','webhook','sms','whatsapp') NOT NULL");

// SYSTEM SETTINGS
$pdo->exec("
CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL UNIQUE,
    `value` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

// STATUS PAGES
$pdo->exec("
CREATE TABLE IF NOT EXISTS status_pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    slug VARCHAR(64) NOT NULL UNIQUE,
    title VARCHAR(150) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

$pdo->exec("
CREATE TABLE IF NOT EXISTS status_pages_ips (
    status_page_id INT NOT NULL,
    monitored_ip_id INT NOT NULL,
    PRIMARY KEY (status_page_id, monitored_ip_id),
    FOREIGN KEY (status_page_id) REFERENCES status_pages(id) ON DELETE CASCADE,
    FOREIGN KEY (monitored_ip_id) REFERENCES monitored_ips(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

// Criar planos padrão se não existirem
$countPlans = $pdo->query("SELECT COUNT(*) FROM plans")->fetchColumn();
if ($countPlans == 0) {
    $stmt = $pdo->prepare("
        INSERT INTO plans (name, max_ips, min_interval_seconds, allow_email, allow_webhook, allow_sms, price)
        VALUES
        ('Free', 3, 300, 1, 0, 0, 0.00),
        ('Basic', 20, 60, 1, 1, 0, 49.90),
        ('Pro', 100, 30, 1, 1, 1, 149.90)
    ");
    $stmt->execute();
}

// Criar admin inicial se não existir
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email = ?");
$stmt->execute([INITIAL_ADMIN_EMAIL]);
if ($stmt->fetchColumn() == 0) {
    $hash = password_hash(INITIAL_ADMIN_PASS, PASSWORD_DEFAULT);
    $stmt = $pdo->prepare("INSERT INTO users (name, email, password_hash, role) VALUES (?, ?, ?, 'admin')");
    $stmt->execute([INITIAL_ADMIN_NAME, INITIAL_ADMIN_EMAIL, $hash]);
    echo "Administrador inicial criado: " . INITIAL_ADMIN_EMAIL . " / " . INITIAL_ADMIN_PASS . "\n";
} else {
    echo "Administrador inicial já existe.\n";
}

echo "Tabelas verificadas e dados iniciais criados.\n";
