CREATE DATABASE IF NOT EXISTS repairflow CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE repairflow;

CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_name VARCHAR(80) NOT NULL,
    status ENUM('active','inactive','past_due','cancelled') NOT NULL DEFAULT 'active',
    max_devices INT NOT NULL DEFAULT 3,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id VARCHAR(128) NOT NULL,
    device_name VARCHAR(190) NOT NULL,
    device_type VARCHAR(50) NOT NULL,
    app_version VARCHAR(40) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    first_seen_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    UNIQUE KEY uq_user_device (user_id, device_id),
    CONSTRAINT fk_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO subscriptions (user_id, plan_name, status, max_devices, expires_at)
VALUES (1, 'Pro Shop', 'active', 3, '2030-12-31 23:59:59');

-- Demo password for the seeded account is: Demo123!
