CREATE TABLE IF NOT EXISTS locations (
    cdz_id INT NOT NULL PRIMARY KEY,
    name VARCHAR(256) NULL,
    parent_cdz_id INT NULL,
    position VARCHAR(64) NULL,
    note TEXT NULL,
    cdz_synced_at INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_locations_parent (parent_cdz_id),
    KEY idx_locations_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;

CREATE TABLE IF NOT EXISTS category_settings (
    category_id INT NOT NULL PRIMARY KEY,
    category_name VARCHAR(64) NOT NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    interval_days INT NOT NULL DEFAULT 1,
    window_start TIME NOT NULL DEFAULT '02:00:00',
    window_end TIME NOT NULL DEFAULT '05:00:00',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;

INSERT INTO category_settings (category_id, category_name, enabled, interval_days, window_start, window_end) VALUES
    (1, 'router', 1, 1, '02:00:00', '05:00:00'),
    (2, 'spoj', 1, 3, '02:00:00', '05:00:00'),
    (3, 'ap', 1, 3, '02:00:00', '05:00:00'),
    (4, 'ups', 1, 7, '02:00:00', '05:00:00'),
    (6, 'switch/rozbočení', 1, 7, '02:00:00', '05:00:00'),
    (7, 'switch', 1, 7, '02:00:00', '05:00:00'),
    (999999, 'jiné', 1, 7, '02:00:00', '05:00:00')
ON DUPLICATE KEY UPDATE category_name = VALUES(category_name);

CREATE TABLE IF NOT EXISTS devices (
    cdz_id INT NOT NULL PRIMARY KEY,
    name VARCHAR(256) NULL,
    location_cdz_id INT NULL,
    location_name VARCHAR(256) NULL,
    parent_cdz_id INT NULL,
    ipv4 VARCHAR(256) NULL,
    ipv6 VARCHAR(256) NULL,
    device_kind INT NULL COMMENT 'CDZ druh: 1=Mikrotik, 2=UBNT',
    category_id INT NULL COMMENT 'CDZ druh2',
    ssh_user VARCHAR(256) NULL,
    encrypted_password VARCHAR(256) NULL,
    note TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    cdz_synced_at INT NULL,
    last_attempt_at DATETIME NULL,
    last_success_at DATETIME NULL,
    next_backup_at DATETIME NULL,
    consecutive_failures INT NOT NULL DEFAULT 0,
    last_error TEXT NULL,
    last_backup_sha256 CHAR(64) NULL,
    last_backup_size BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_devices_location (location_cdz_id),
    KEY idx_devices_kind (device_kind),
    KEY idx_devices_category (category_id),
    KEY idx_devices_due (is_active, next_backup_at),
    KEY idx_devices_name (name),
    CONSTRAINT fk_devices_location FOREIGN KEY (location_cdz_id) REFERENCES locations(cdz_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;

CREATE TABLE IF NOT EXISTS backup_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    device_cdz_id INT NOT NULL,
    trigger_type ENUM('automatic','manual','cli') NOT NULL DEFAULT 'automatic',
    status ENUM('running','success','failed') NOT NULL,
    storage_path VARCHAR(1024) NULL,
    sha256 CHAR(64) NULL,
    size_bytes BIGINT UNSIGNED NULL,
    changed_from_previous TINYINT(1) NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_history_device_started (device_cdz_id, started_at),
    KEY idx_history_status_started (status, started_at),
    CONSTRAINT fk_history_device FOREIGN KEY (device_cdz_id) REFERENCES devices(cdz_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;

CREATE TABLE IF NOT EXISTS sync_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('location','device') NOT NULL,
    entity_cdz_id INT NOT NULL,
    status ENUM('success','failed','skipped') NOT NULL,
    message VARCHAR(1024) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_sync_created (created_at),
    KEY idx_sync_entity (entity_type, entity_cdz_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;
