SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------
-- MikroTik (spec section 11)
-- ----------------------------------------------------------

CREATE TABLE IF NOT EXISTS mikrotik_devices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    router_name VARCHAR(100) NOT NULL,
    host VARCHAR(190) NOT NULL,
    port INT UNSIGNED NOT NULL DEFAULT 8728,
    username VARCHAR(100) NOT NULL,
    password_encrypted TEXT NOT NULL COMMENT 'AES-256-GCM encrypted, see App\\Helpers\\Crypto',
    api_type ENUM('api','api-ssl','rest','ssh') NOT NULL DEFAULT 'api',
    ssl TINYINT(1) NOT NULL DEFAULT 0,
    timeout_seconds INT UNSIGNED NOT NULL DEFAULT 5,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    last_status ENUM('online','offline','unknown') NOT NULL DEFAULT 'unknown',
    last_checked_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mikrotik_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mikrotik_device_id INT UNSIGNED NOT NULL,
    profile_name VARCHAR(100) NOT NULL,
    rate_limit VARCHAR(60) NULL,
    is_isolation_profile TINYINT(1) NOT NULL DEFAULT 0,
    package_id INT UNSIGNED NULL,
    CONSTRAINT fk_mp_device FOREIGN KEY (mikrotik_device_id) REFERENCES mikrotik_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mikrotik_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mikrotik_device_id INT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NULL,
    username VARCHAR(100) NULL,
    ip_address VARCHAR(45) NULL,
    mac_address VARCHAR(20) NULL,
    session_start DATETIME NULL,
    session_end DATETIME NULL,
    upload_bytes BIGINT UNSIGNED NULL,
    download_bytes BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------
-- RADIUS (spec section 12) — standard FreeRADIUS-compatible schema
-- ----------------------------------------------------------

CREATE TABLE IF NOT EXISTS radius_servers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    host VARCHAR(190) NOT NULL,
    secret_encrypted TEXT NOT NULL,
    auth_port INT UNSIGNED NOT NULL DEFAULT 1812,
    acct_port INT UNSIGNED NOT NULL DEFAULT 1813,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS radcheck (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT '',
    attribute VARCHAR(64) NOT NULL DEFAULT '',
    op CHAR(2) NOT NULL DEFAULT '==',
    value VARCHAR(253) NOT NULL DEFAULT '',
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS radreply (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT '',
    attribute VARCHAR(64) NOT NULL DEFAULT '',
    op CHAR(2) NOT NULL DEFAULT '=',
    value VARCHAR(253) NOT NULL DEFAULT '',
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS radusergroup (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT '',
    groupname VARCHAR(64) NOT NULL DEFAULT '',
    priority INT NOT NULL DEFAULT 1,
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS radgroupcheck (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    groupname VARCHAR(64) NOT NULL DEFAULT '',
    attribute VARCHAR(64) NOT NULL DEFAULT '',
    op CHAR(2) NOT NULL DEFAULT '==',
    value VARCHAR(253) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS radgroupreply (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    groupname VARCHAR(64) NOT NULL DEFAULT '',
    attribute VARCHAR(64) NOT NULL DEFAULT '',
    op CHAR(2) NOT NULL DEFAULT '=',
    value VARCHAR(253) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS radacct (
    radacctid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    acctsessionid VARCHAR(64) NOT NULL DEFAULT '',
    username VARCHAR(64) NOT NULL DEFAULT '',
    nasipaddress VARCHAR(45) NOT NULL DEFAULT '',
    nasportid VARCHAR(32) NULL,
    acctstarttime DATETIME NULL,
    acctstoptime DATETIME NULL,
    acctsessiontime INT UNSIGNED NULL,
    acctinputoctets BIGINT UNSIGNED NULL,
    acctoutputoctets BIGINT UNSIGNED NULL,
    framedipaddress VARCHAR(45) NULL,
    callingstationid VARCHAR(50) NULL,
    INDEX idx_username (username),
    INDEX idx_session (acctsessionid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------
-- OLT / GPON / EPON / ONU (spec section 16-17)
-- ----------------------------------------------------------

CREATE TABLE IF NOT EXISTS olts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    vendor ENUM('huawei','zte','fiberhome','vsol','generic_snmp') NOT NULL,
    model VARCHAR(100) NULL,
    ip_address VARCHAR(45) NOT NULL,
    port INT UNSIGNED NULL,
    protocol ENUM('snmp','ssh','telnet','http_api','vendor_api') NOT NULL DEFAULT 'snmp',
    username VARCHAR(100) NULL,
    password_encrypted TEXT NULL,
    snmp_community_encrypted TEXT NULL,
    snmp_version VARCHAR(10) NULL COMMENT 'v1|v2c|v3',
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    last_status ENUM('online','offline','not_configured') NOT NULL DEFAULT 'not_configured',
    last_checked_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS olt_ports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    olt_id INT UNSIGNED NOT NULL,
    board INT UNSIGNED NULL,
    pon_port INT UNSIGNED NOT NULL,
    label VARCHAR(60) NULL,
    status ENUM('up','down','unknown') NOT NULL DEFAULT 'unknown',
    CONSTRAINT fk_op_olt FOREIGN KEY (olt_id) REFERENCES olts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS onus (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    olt_id INT UNSIGNED NOT NULL,
    olt_port_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NULL,
    serial_number VARCHAR(60) NOT NULL,
    loid VARCHAR(60) NULL,
    onu_id_on_port INT UNSIGNED NULL,
    profile_id INT UNSIGNED NULL,
    status ENUM('online','offline','los','not_registered') NOT NULL DEFAULT 'not_registered',
    rx_power_dbm DECIMAL(6,2) NULL,
    tx_power_dbm DECIMAL(6,2) NULL,
    distance_meters INT UNSIGNED NULL,
    last_seen_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_onu_olt FOREIGN KEY (olt_id) REFERENCES olts(id),
    UNIQUE KEY uniq_serial (serial_number),
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS onu_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    olt_id INT UNSIGNED NOT NULL,
    profile_name VARCHAR(100) NOT NULL,
    line_profile VARCHAR(100) NULL,
    service_profile VARCHAR(100) NULL,
    CONSTRAINT fk_onup_olt FOREIGN KEY (olt_id) REFERENCES olts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------
-- PPPoE / Static IP / DHCP (spec sections 13-15)
-- ----------------------------------------------------------

CREATE TABLE IF NOT EXISTS pppoe_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    mikrotik_device_id INT UNSIGNED NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_encrypted TEXT NOT NULL,
    service_name VARCHAR(100) NULL,
    profile VARCHAR(100) NULL,
    nas_identifier VARCHAR(100) NULL,
    status ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pppoe_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS static_ips (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    ip_address VARCHAR(45) NOT NULL UNIQUE,
    subnet VARCHAR(45) NULL,
    gateway VARCHAR(45) NULL,
    dns_primary VARCHAR(45) NULL,
    dns_secondary VARCHAR(45) NULL,
    vlan INT UNSIGNED NULL,
    mikrotik_device_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_static_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_ip (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dhcp_leases (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NULL,
    mikrotik_device_id INT UNSIGNED NULL,
    ip_address VARCHAR(45) NOT NULL,
    mac_address VARCHAR(20) NOT NULL,
    hostname VARCHAR(100) NULL,
    status ENUM('active','expired','static') NOT NULL DEFAULT 'active',
    expires_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mac (mac_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;