CREATE DATABASE IF NOT EXISTS safekid CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE safekid;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE devices (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,name VARCHAR(120) NOT NULL,device_token VARCHAR(128) NOT NULL UNIQUE,parent_token VARCHAR(128) NOT NULL UNIQUE,quota_bytes BIGINT UNSIGNED NOT NULL DEFAULT 2147483648,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,last_seen_at TIMESTAMP NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE media (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,device_id BIGINT UNSIGNED NOT NULL,client_id VARCHAR(128) NOT NULL,file_name VARCHAR(255) NOT NULL,mime_type VARCHAR(100) NOT NULL,size_bytes BIGINT UNSIGNED NOT NULL,storage_path VARCHAR(500) NOT NULL,sha256 CHAR(64) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,deleted_at TIMESTAMP NULL,UNIQUE KEY uq_media_client(device_id,client_id),FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE CASCADE);
CREATE TABLE commands (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,device_id BIGINT UNSIGNED NOT NULL,command_type ENUM('delete_media') NOT NULL,payload JSON NOT NULL,status ENUM('pending','done','failed') DEFAULT 'pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE CASCADE);
INSERT INTO users(email,password_hash) VALUES ('demo@safekid.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa9wXqzZ8Y2LhYf6p8J4kqQ2e3K');