-- Work Orders Management System
-- Canonical MySQL 8+ schema for cPanel deployments.
-- This file matches backend/prisma/schema.prisma.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS blueprints;
DROP TABLE IF EXISTS order_history;
DROP TABLE IF EXISTS work_order_machinery;
DROP TABLE IF EXISTS work_order_operators;
DROP TABLE IF EXISTS work_order_details;
DROP TABLE IF EXISTS work_orders;
DROP TABLE IF EXISTS machinery;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `users` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` VARCHAR(20) NOT NULL DEFAULT 'viewer',
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `users_username_key`(`username`),
    UNIQUE INDEX `users_email_key`(`email`),
    INDEX `users_email_idx`(`email`),
    INDEX `users_role_idx`(`role`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `clients` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) NULL,
    `phone` VARCHAR(20) NULL,
    `address` TEXT NULL,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `clients_name_idx`(`name`),
    INDEX `clients_email_idx`(`email`),
    INDEX `clients_is_active_idx`(`is_active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `machinery` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'available',
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `machinery_code_key`(`code`),
    INDEX `machinery_code_idx`(`code`),
    INDEX `machinery_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `work_orders` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `order_number` VARCHAR(50) NOT NULL,
    `quotation_number` VARCHAR(50) NULL,
    `title` VARCHAR(200) NOT NULL,
    `description` TEXT NULL,
    `performed_work` TEXT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
    `priority` VARCHAR(20) NOT NULL DEFAULT 'medium',
    `start_date` DATETIME(3) NULL,
    `end_date` DATETIME(3) NULL,
    `client_id` INTEGER NOT NULL,
    `created_by` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `work_orders_order_number_key`(`order_number`),
    INDEX `work_orders_order_number_idx`(`order_number`),
    INDEX `work_orders_status_idx`(`status`),
    INDEX `work_orders_priority_idx`(`priority`),
    INDEX `work_orders_client_id_idx`(`client_id`),
    INDEX `work_orders_created_at_idx`(`created_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `work_order_details` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `work_order_id` INTEGER NOT NULL,
    `description` TEXT NOT NULL,
    `quantity` DECIMAL(10, 2) NOT NULL DEFAULT 1.00,
    `operator_id` INTEGER NULL,
    `machinery_id` INTEGER NULL,
    `start_datetime` DATETIME(3) NULL,
    `end_datetime` DATETIME(3) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `work_order_details_work_order_id_idx`(`work_order_id`),
    INDEX `work_order_details_operator_id_idx`(`operator_id`),
    INDEX `work_order_details_machinery_id_idx`(`machinery_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `work_order_operators` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `work_order_id` INTEGER NOT NULL,
    `operator_id` INTEGER NOT NULL,
    `assigned_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `work_order_operators_work_order_id_idx`(`work_order_id`),
    INDEX `work_order_operators_operator_id_idx`(`operator_id`),
    UNIQUE INDEX `work_order_operators_work_order_id_operator_id_key`(`work_order_id`, `operator_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `work_order_machinery` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `work_order_id` INTEGER NOT NULL,
    `machinery_id` INTEGER NOT NULL,
    `assigned_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `work_order_machinery_work_order_id_idx`(`work_order_id`),
    INDEX `work_order_machinery_machinery_id_idx`(`machinery_id`),
    UNIQUE INDEX `work_order_machinery_work_order_id_machinery_id_key`(`work_order_id`, `machinery_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `order_history` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `work_order_id` INTEGER NOT NULL,
    `changed_by` INTEGER NOT NULL,
    `change_type` VARCHAR(30) NOT NULL,
    `old_value` TEXT NULL,
    `new_value` TEXT NULL,
    `notes` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `order_history_work_order_id_idx`(`work_order_id`),
    INDEX `order_history_created_at_idx`(`created_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `blueprints` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `work_order_id` INTEGER NOT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `storage_key` VARCHAR(255) NOT NULL,
    `file_size` INTEGER NOT NULL,
    `mime_type` VARCHAR(100) NOT NULL,
    `description` TEXT NULL,
    `uploaded_by` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `blueprints_storage_key_key`(`storage_key`),
    INDEX `blueprints_work_order_id_idx`(`work_order_id`),
    INDEX `blueprints_uploaded_by_idx`(`uploaded_by`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `work_orders`
    ADD CONSTRAINT `work_orders_client_id_fkey`
        FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    ADD CONSTRAINT `work_orders_created_by_fkey`
        FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `work_order_details`
    ADD CONSTRAINT `work_order_details_work_order_id_fkey`
        FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `work_order_details_operator_id_fkey`
        FOREIGN KEY (`operator_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    ADD CONSTRAINT `work_order_details_machinery_id_fkey`
        FOREIGN KEY (`machinery_id`) REFERENCES `machinery`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `work_order_operators`
    ADD CONSTRAINT `work_order_operators_work_order_id_fkey`
        FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `work_order_operators_operator_id_fkey`
        FOREIGN KEY (`operator_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `work_order_machinery`
    ADD CONSTRAINT `work_order_machinery_work_order_id_fkey`
        FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `work_order_machinery_machinery_id_fkey`
        FOREIGN KEY (`machinery_id`) REFERENCES `machinery`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `order_history`
    ADD CONSTRAINT `order_history_work_order_id_fkey`
        FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `order_history_changed_by_fkey`
        FOREIGN KEY (`changed_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `blueprints`
    ADD CONSTRAINT `blueprints_work_order_id_fkey`
        FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `blueprints_uploaded_by_fkey`
        FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;