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

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(120) NOT NULL,
    company_name VARCHAR(120) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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
);

CREATE TABLE access_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_row_id INT NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL,
    last_used_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_tokens_device FOREIGN KEY (device_row_id) REFERENCES devices(id) ON DELETE CASCADE
);

INSERT INTO users (email, password_hash, display_name, company_name, is_active)
VALUES ('demo@repairflow.local', '$2y$10$UUdM0AGY4d4WQhIzWhM1Ou6rH9xJ3lM4Gfpv2NwVv9Gxq3JYxS6Si', 'Demo Technician', 'RepairFlow Demo', 1);

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!