CREATE TABLE IF NOT EXISTS attendance_transactions (
    id BIGINT PRIMARY KEY,
    verify_type INT NULL,
    source INT NULL,
    punch_time DATETIME NOT NULL,
    temperature VARCHAR(32) NULL,
    terminal_sn VARCHAR(64) NULL,
    terminal_alias VARCHAR(128) NULL,
    att_photo_url TEXT NULL,
    mask_flag VARCHAR(32) NULL,
    upload_time DATETIME NULL,
    timezone VARCHAR(16) NULL,
    gps_location VARCHAR(255) NULL,
    device_verify_type VARCHAR(32) NULL,
    weekday VARCHAR(32) NULL,
    employee_code VARCHAR(64) NOT NULL,
    employee_name VARCHAR(255) NULL,
    dept_name VARCHAR(255) NULL,
    status VARCHAR(32) NULL,
    raw_json JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_attendance_id (id),
    KEY idx_employee_date (employee_code, punch_time),
    KEY idx_punch_time (punch_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sync_runs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    status VARCHAR(32) NOT NULL,
    inserted_count INT DEFAULT 0,
    duplicate_count INT DEFAULT 0,
    fetched_count INT DEFAULT 0,
    error_message TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;