ALTER TABLE devices
    ADD COLUMN device_token_hash CHAR(64) NULL AFTER mac,
    ADD INDEX idx_devices_status (status),
    ADD INDEX idx_devices_last_seen (last_seen_at);

ALTER TABLE playlists
    MODIFY COLUMN password TEXT NULL,
    ADD COLUMN epg_url TEXT NULL AFTER output_format,
    ADD COLUMN catchup_mode VARCHAR(30) NULL AFTER epg_url;

ALTER TABLE payments
    ADD COLUMN stripe_event_id VARCHAR(255) NULL AFTER device_id,
    ADD UNIQUE INDEX uniq_stripe_event (stripe_event_id),
    ADD INDEX idx_payments_created (created_at);

CREATE TABLE device_data (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id {{DEVICE_ID_TYPE}} NOT NULL,
    data_key VARCHAR(40) NOT NULL,
    payload MEDIUMTEXT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_device_data (device_id, data_key),
    CONSTRAINT fk_device_data_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id {{DEVICE_ID_TYPE}} NULL,
    actor VARCHAR(100) NOT NULL,
    action VARCHAR(100) NOT NULL,
    ip_address VARCHAR(64) NULL,
    details TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_created (created_at),
    KEY idx_audit_device (device_id),
    CONSTRAINT fk_audit_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
