

-- Table untuk paths metadata
CREATE TABLE IF NOT EXISTS paths (
    id INT AUTO_INCREMENT PRIMARY KEY,
    place_id BIGINT NOT NULL,
    parent_place_id BIGINT DEFAULT NULL,
    map_version INT NOT NULL DEFAULT 1,
    is_checkpoint BOOLEAN DEFAULT FALSE,
    checkpoint_index INT DEFAULT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT DEFAULT 0,
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    downloads INT DEFAULT 0,
    uploader_id BIGINT DEFAULT NULL,
    uploader_name VARCHAR(100),
    notes TEXT,
    INDEX idx_place_id (place_id),
    INDEX idx_map_version (map_version),
    INDEX idx_checkpoint (is_checkpoint, checkpoint_index),
    INDEX idx_upload_date (upload_date)
) ENGINE=InnoDB;

-- Table untuk tracking downloads
CREATE TABLE IF NOT EXISTS download_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    path_id INT NOT NULL,
    user_id BIGINT,
    username VARCHAR(100),
    download_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    FOREIGN KEY (path_id) REFERENCES paths(id) ON DELETE CASCADE,
    INDEX idx_path_id (path_id),
    INDEX idx_download_date (download_date)
) ENGINE=InnoDB;
