-- Analytics and Commands Tables

CREATE TABLE IF NOT EXISTS `user_app_analytics` (
    `tester_id` INT(11) NOT NULL,
    `app_id` INT(11) NOT NULL,
    `is_installed` TINYINT(1) DEFAULT 0,
    `last_checked` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`tester_id`, `app_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS `tester_commands` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `tester_id` INT(11) NOT NULL,
    `app_id` INT(11) NOT NULL,
    `command` VARCHAR(50) NOT NULL, -- e.g., "POST_FEEDBACK"
    `feedback_text` TEXT,
    `status` ENUM(
        'PENDING',
        'SENT',
        'COMPLETED',
        'FAILED'
    ) DEFAULT 'PENDING',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- Add updated columns to tester_users if missing
ALTER TABLE `tester_users`
ADD COLUMN IF NOT EXISTS `device_model` VARCHAR(100) DEFAULT NULL;

ALTER TABLE `tester_users`
ADD COLUMN IF NOT EXISTS `email` VARCHAR(100) DEFAULT NULL;