-- ============================================================
--  schema.sql — Run once in cPanel phpMyAdmin
--  or via: mysql -u user -p dbname < schema.sql
-- ============================================================

-- All file uploads log
CREATE TABLE IF NOT EXISTS uploads_log (
    id            INT          UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id     VARCHAR(100) NOT NULL,
    filename      VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_path     VARCHAR(500) NOT NULL,
    file_size     BIGINT       UNSIGNED NOT NULL DEFAULT 0,
    mime_type     VARCHAR(100)          DEFAULT NULL,
    md5_hash      CHAR(32)              DEFAULT NULL,
    android_ver   VARCHAR(20)           DEFAULT NULL,
    device_model  VARCHAR(100)          DEFAULT NULL,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device   (device_id),
    INDEX idx_created  (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Structured JSON payloads (no file)
CREATE TABLE IF NOT EXISTS structured_data (
    id         INT      UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id  VARCHAR(100) NOT NULL,
    payload    JSON              DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device  (device_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;