<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateSampleMasterDataTables extends AbstractMigration
{
    public function up(): void
    {
        /*
         * Business partners:
         * suppliers, producers, manufacturers, vendors, factories, invoice/report entities.
         */
        if (!$this->hasTable('business_partners')) {
            $this->execute("
                CREATE TABLE `business_partners` (
                    `idpartner` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idcompany` INT(10) UNSIGNED NOT NULL,
                    `partner_type` ENUM(
                        'producer',
                        'manufacturer',
                        'supplier',
                        'vendor',
                        'factory',
                        'agent',
                        'invoice_to',
                        'report_to',
                        'laboratory',
                        'other'
                    ) NOT NULL DEFAULT 'supplier',
                    `partner_name` VARCHAR(255) NOT NULL,
                    `legal_name` VARCHAR(255) DEFAULT NULL,
                    `external_code` VARCHAR(100) DEFAULT NULL,
                    `vat_number` VARCHAR(80) DEFAULT NULL,
                    `tax_code` VARCHAR(80) DEFAULT NULL,
                    `address` VARCHAR(255) DEFAULT NULL,
                    `city` VARCHAR(120) DEFAULT NULL,
                    `zip` VARCHAR(50) DEFAULT NULL,
                    `country_id` INT(10) UNSIGNED DEFAULT NULL,
                    `email` VARCHAR(191) DEFAULT NULL,
                    `phone` VARCHAR(80) DEFAULT NULL,
                    `website` VARCHAR(255) DEFAULT NULL,
                    `notes` TEXT DEFAULT NULL,
                    `status` ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
                    `created_by` INT(10) UNSIGNED DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idpartner`),
                    KEY `idx_partner_company` (`idcompany`),
                    KEY `idx_partner_type` (`partner_type`),
                    KEY `idx_partner_name` (`partner_name`),
                    KEY `idx_partner_external_code` (`external_code`),
                    KEY `idx_partner_status` (`status`),
                    KEY `idx_partner_country` (`country_id`),
                    KEY `idx_partner_created_by` (`created_by`),
                    CONSTRAINT `fk_partner_company`
                        FOREIGN KEY (`idcompany`) REFERENCES `companies` (`idcompany`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_partner_country`
                        FOREIGN KEY (`country_id`) REFERENCES `auth_countries` (`id`)
                        ON DELETE SET NULL ON UPDATE CASCADE,
                    CONSTRAINT `fk_partner_created_by`
                        FOREIGN KEY (`created_by`) REFERENCES `auth_users` (`id`)
                        ON DELETE SET NULL ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Contacts linked to business partners.
         */
        if (!$this->hasTable('business_partner_contacts')) {
            $this->execute("
                CREATE TABLE `business_partner_contacts` (
                    `idcontact` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idpartner` INT(10) UNSIGNED NOT NULL,
                    `contact_name` VARCHAR(255) NOT NULL,
                    `role` VARCHAR(120) DEFAULT NULL,
                    `email` VARCHAR(191) DEFAULT NULL,
                    `phone` VARCHAR(80) DEFAULT NULL,
                    `mobile` VARCHAR(80) DEFAULT NULL,
                    `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
                    `notes` TEXT DEFAULT NULL,
                    `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idcontact`),
                    KEY `idx_contact_partner` (`idpartner`),
                    KEY `idx_contact_email` (`email`),
                    KEY `idx_contact_primary` (`is_primary`),
                    CONSTRAINT `fk_contact_partner`
                        FOREIGN KEY (`idpartner`) REFERENCES `business_partners` (`idpartner`)
                        ON DELETE CASCADE ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Samples / products master data.
         */
        if (!$this->hasTable('samples')) {
            $this->execute("
                CREATE TABLE `samples` (
                    `idsample` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idcompany` INT(10) UNSIGNED NOT NULL,
                    `idbrand` INT(10) UNSIGNED DEFAULT NULL,
                    `iddepartment` INT(10) UNSIGNED DEFAULT NULL,
                    `idproducer` INT(10) UNSIGNED DEFAULT NULL,
                    `idsupplier` INT(10) UNSIGNED DEFAULT NULL,
                    `sample_code` VARCHAR(120) NOT NULL,
                    `external_sample_id` VARCHAR(120) DEFAULT NULL,
                    `article_no` VARCHAR(150) DEFAULT NULL,
                    `po_no` VARCHAR(255) DEFAULT NULL,
                    `season` VARCHAR(120) DEFAULT NULL,
                    `style_no` VARCHAR(150) DEFAULT NULL,
                    `style_name` VARCHAR(255) DEFAULT NULL,
                    `model` VARCHAR(255) DEFAULT NULL,
                    `sample_description` VARCHAR(255) NOT NULL,
                    `product_category` VARCHAR(150) DEFAULT NULL,
                    `product_type` VARCHAR(150) DEFAULT NULL,
                    `color` VARCHAR(120) DEFAULT NULL,
                    `size` VARCHAR(120) DEFAULT NULL,
                    `gender` VARCHAR(80) DEFAULT NULL,
                    `age_group` VARCHAR(80) DEFAULT NULL,
                    `fiber_content` TEXT DEFAULT NULL,
                    `material_description` TEXT DEFAULT NULL,
                    `claimed_weight` VARCHAR(120) DEFAULT NULL,
                    `product_standard` VARCHAR(255) DEFAULT NULL,
                    `production_stage` VARCHAR(120) DEFAULT NULL,
                    `country_of_origin` INT(10) UNSIGNED DEFAULT NULL,
                    `status` ENUM(
                        'draft',
                        'active',
                        'archived',
                        'submitted',
                        'under_testing',
                        'completed',
                        'cancelled'
                    ) NOT NULL DEFAULT 'draft',
                    `source` ENUM('manual','xls_import','json_import','api','smarttrf') NOT NULL DEFAULT 'manual',
                    `raw_json` LONGTEXT DEFAULT NULL,
                    `created_by` INT(10) UNSIGNED DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idsample`),
                    UNIQUE KEY `uq_sample_company_code` (`idcompany`, `sample_code`),
                    KEY `idx_sample_company` (`idcompany`),
                    KEY `idx_sample_brand` (`idbrand`),
                    KEY `idx_sample_department` (`iddepartment`),
                    KEY `idx_sample_producer` (`idproducer`),
                    KEY `idx_sample_supplier` (`idsupplier`),
                    KEY `idx_sample_article` (`article_no`),
                    KEY `idx_sample_external` (`external_sample_id`),
                    KEY `idx_sample_status` (`status`),
                    KEY `idx_sample_created_by` (`created_by`),
                    CONSTRAINT `fk_sample_company`
                        FOREIGN KEY (`idcompany`) REFERENCES `companies` (`idcompany`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_brand`
                        FOREIGN KEY (`idbrand`) REFERENCES `brands` (`idbrand`)
                        ON DELETE SET NULL ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_department`
                        FOREIGN KEY (`iddepartment`) REFERENCES `departments` (`iddepartment`)
                        ON DELETE SET NULL ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_producer`
                        FOREIGN KEY (`idproducer`) REFERENCES `business_partners` (`idpartner`)
                        ON DELETE SET NULL ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_supplier`
                        FOREIGN KEY (`idsupplier`) REFERENCES `business_partners` (`idpartner`)
                        ON DELETE SET NULL ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_origin_country`
                        FOREIGN KEY (`country_of_origin`) REFERENCES `auth_countries` (`id`)
                        ON DELETE SET NULL ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_created_by`
                        FOREIGN KEY (`created_by`) REFERENCES `auth_users` (`id`)
                        ON DELETE SET NULL ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Sample status history.
         */
        if (!$this->hasTable('sample_status_history')) {
            $this->execute("
                CREATE TABLE `sample_status_history` (
                    `idhistory` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idsample` INT(10) UNSIGNED NOT NULL,
                    `old_status` VARCHAR(80) DEFAULT NULL,
                    `new_status` VARCHAR(80) NOT NULL,
                    `note` TEXT DEFAULT NULL,
                    `changed_by` INT(10) UNSIGNED DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idhistory`),
                    KEY `idx_history_sample` (`idsample`),
                    KEY `idx_history_changed_by` (`changed_by`),
                    CONSTRAINT `fk_sample_history_sample`
                        FOREIGN KEY (`idsample`) REFERENCES `samples` (`idsample`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_history_user`
                        FOREIGN KEY (`changed_by`) REFERENCES `auth_users` (`id`)
                        ON DELETE SET NULL ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Sample photos.
         */
        if (!$this->hasTable('sample_photos')) {
            $this->execute("
                CREATE TABLE `sample_photos` (
                    `idsamplephoto` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idsample` INT(10) UNSIGNED NOT NULL,
                    `photo_type` ENUM('main','product','label','packaging','warning','detail','other') NOT NULL DEFAULT 'product',
                    `filename` VARCHAR(255) NOT NULL,
                    `original_filename` VARCHAR(255) DEFAULT NULL,
                    `description` TEXT DEFAULT NULL,
                    `is_main` TINYINT(1) NOT NULL DEFAULT 0,
                    `sort_order` INT(11) NOT NULL DEFAULT 0,
                    `uploaded_by` INT(10) UNSIGNED DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idsamplephoto`),
                    KEY `idx_sample_photo_sample` (`idsample`),
                    KEY `idx_sample_photo_type` (`photo_type`),
                    KEY `idx_sample_photo_uploaded_by` (`uploaded_by`),
                    CONSTRAINT `fk_sample_photo_sample`
                        FOREIGN KEY (`idsample`) REFERENCES `samples` (`idsample`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_photo_user`
                        FOREIGN KEY (`uploaded_by`) REFERENCES `auth_users` (`id`)
                        ON DELETE SET NULL ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Sample parts / BOM.
         */
        if (!$this->hasTable('sample_parts')) {
            $this->execute("
                CREATE TABLE `sample_parts` (
                    `idpart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idsample` INT(10) UNSIGNED NOT NULL,
                    `parent_idpart` INT(10) UNSIGNED DEFAULT NULL,
                    `part_code` VARCHAR(120) DEFAULT NULL,
                    `part_name` VARCHAR(255) NOT NULL,
                    `part_description` TEXT DEFAULT NULL,
                    `material` VARCHAR(255) DEFAULT NULL,
                    `color` VARCHAR(120) DEFAULT NULL,
                    `quantity` DECIMAL(12,4) DEFAULT NULL,
                    `unit` VARCHAR(50) DEFAULT NULL,
                    `supplier_id` INT(10) UNSIGNED DEFAULT NULL,
                    `producer_id` INT(10) UNSIGNED DEFAULT NULL,
                    `position` VARCHAR(120) DEFAULT NULL,
                    `risk_level` ENUM('low','medium','high','critical','unknown') NOT NULL DEFAULT 'unknown',
                    `notes` TEXT DEFAULT NULL,
                    `sort_order` INT(11) NOT NULL DEFAULT 0,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idpart`),
                    KEY `idx_part_sample` (`idsample`),
                    KEY `idx_part_parent` (`parent_idpart`),
                    KEY `idx_part_supplier` (`supplier_id`),
                    KEY `idx_part_producer` (`producer_id`),
                    KEY `idx_part_risk` (`risk_level`),
                    CONSTRAINT `fk_part_sample`
                        FOREIGN KEY (`idsample`) REFERENCES `samples` (`idsample`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_part_parent`
                        FOREIGN KEY (`parent_idpart`) REFERENCES `sample_parts` (`idpart`)
                        ON DELETE SET NULL ON UPDATE CASCADE,
                    CONSTRAINT `fk_part_supplier`
                        FOREIGN KEY (`supplier_id`) REFERENCES `business_partners` (`idpartner`)
                        ON DELETE SET NULL ON UPDATE CASCADE,
                    CONSTRAINT `fk_part_producer`
                        FOREIGN KEY (`producer_id`) REFERENCES `business_partners` (`idpartner`)
                        ON DELETE SET NULL ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Sample part photos.
         */
        if (!$this->hasTable('sample_part_photos')) {
            $this->execute("
                CREATE TABLE `sample_part_photos` (
                    `idpartphoto` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idpart` INT(10) UNSIGNED NOT NULL,
                    `filename` VARCHAR(255) NOT NULL,
                    `original_filename` VARCHAR(255) DEFAULT NULL,
                    `description` TEXT DEFAULT NULL,
                    `is_main` TINYINT(1) NOT NULL DEFAULT 0,
                    `sort_order` INT(11) NOT NULL DEFAULT 0,
                    `uploaded_by` INT(10) UNSIGNED DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idpartphoto`),
                    KEY `idx_part_photo_part` (`idpart`),
                    KEY `idx_part_photo_uploaded_by` (`uploaded_by`),
                    CONSTRAINT `fk_part_photo_part`
                        FOREIGN KEY (`idpart`) REFERENCES `sample_parts` (`idpart`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_part_photo_user`
                        FOREIGN KEY (`uploaded_by`) REFERENCES `auth_users` (`id`)
                        ON DELETE SET NULL ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Generic documents.
         */
        if (!$this->hasTable('documents')) {
            $this->execute("
                CREATE TABLE `documents` (
                    `iddocument` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idcompany` INT(10) UNSIGNED NOT NULL,
                    `document_type` ENUM(
                        'technical_sheet',
                        'declaration',
                        'bom',
                        'photo',
                        'certificate',
                        'test_report',
                        'supplier_document',
                        'invoice',
                        'manual',
                        'other'
                    ) NOT NULL DEFAULT 'other',
                    `title` VARCHAR(255) NOT NULL,
                    `filename` VARCHAR(255) NOT NULL,
                    `original_filename` VARCHAR(255) DEFAULT NULL,
                    `mime_type` VARCHAR(120) DEFAULT NULL,
                    `file_size` BIGINT(20) UNSIGNED DEFAULT NULL,
                    `expiry_date` DATE DEFAULT NULL,
                    `status` ENUM('active','expired','archived') NOT NULL DEFAULT 'active',
                    `notes` TEXT DEFAULT NULL,
                    `uploaded_by` INT(10) UNSIGNED DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`iddocument`),
                    KEY `idx_document_company` (`idcompany`),
                    KEY `idx_document_type` (`document_type`),
                    KEY `idx_document_status` (`status`),
                    KEY `idx_document_expiry` (`expiry_date`),
                    KEY `idx_document_uploaded_by` (`uploaded_by`),
                    CONSTRAINT `fk_document_company`
                        FOREIGN KEY (`idcompany`) REFERENCES `companies` (`idcompany`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_document_uploaded_by`
                        FOREIGN KEY (`uploaded_by`) REFERENCES `auth_users` (`id`)
                        ON DELETE SET NULL ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Documents linked to samples.
         */
        if (!$this->hasTable('sample_documents')) {
            $this->execute("
                CREATE TABLE `sample_documents` (
                    `idsampledocument` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idsample` INT(10) UNSIGNED NOT NULL,
                    `iddocument` INT(10) UNSIGNED NOT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idsampledocument`),
                    UNIQUE KEY `uq_sample_document` (`idsample`, `iddocument`),
                    KEY `idx_sample_document_document` (`iddocument`),
                    CONSTRAINT `fk_sample_document_sample`
                        FOREIGN KEY (`idsample`) REFERENCES `samples` (`idsample`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_sample_document_document`
                        FOREIGN KEY (`iddocument`) REFERENCES `documents` (`iddocument`)
                        ON DELETE CASCADE ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }

        /*
         * Documents linked to sample parts.
         */
        if (!$this->hasTable('sample_part_documents')) {
            $this->execute("
                CREATE TABLE `sample_part_documents` (
                    `idpartdocument` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    `idpart` INT(10) UNSIGNED NOT NULL,
                    `iddocument` INT(10) UNSIGNED NOT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`idpartdocument`),
                    UNIQUE KEY `uq_part_document` (`idpart`, `iddocument`),
                    KEY `idx_part_document_document` (`iddocument`),
                    CONSTRAINT `fk_part_document_part`
                        FOREIGN KEY (`idpart`) REFERENCES `sample_parts` (`idpart`)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `fk_part_document_document`
                        FOREIGN KEY (`iddocument`) REFERENCES `documents` (`iddocument`)
                        ON DELETE CASCADE ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }
    }

    public function down(): void
    {
        $tables = [
            'sample_part_documents',
            'sample_documents',
            'documents',
            'sample_part_photos',
            'sample_parts',
            'sample_photos',
            'sample_status_history',
            'samples',
            'business_partner_contacts',
            'business_partners',
        ];

        foreach ($tables as $table) {
            if ($this->hasTable($table)) {
                $this->table($table)->drop()->save();
            }
        }
    }
}