INSERT INTO companies (code, name, legal_name, status)
SELECT 'GPCM', 'GPCM Company', 'GPCM Company', 'active'
WHERE NOT EXISTS (SELECT 1 FROM companies WHERE code = 'GPCM');

CREATE TABLE product_subcategories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_category_id BIGINT UNSIGNED NOT NULL,
    product_type_id BIGINT UNSIGNED NULL,
    code VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_product_subcategory_category FOREIGN KEY (product_category_id) REFERENCES product_categories (id),
    CONSTRAINT fk_product_subcategory_type FOREIGN KEY (product_type_id) REFERENCES product_types (id)
) ENGINE=InnoDB;

CREATE TABLE style_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE buyers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    buyer_category_id BIGINT UNSIGNED NULL,
    country_id BIGINT UNSIGNED NULL,
    currency_id BIGINT UNSIGNED NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    contact_person VARCHAR(120) NULL,
    address TEXT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_buyer_code (company_id, code),
    CONSTRAINT fk_buyer_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_buyer_category FOREIGN KEY (buyer_category_id) REFERENCES buyer_categories (id),
    CONSTRAINT fk_buyer_country FOREIGN KEY (country_id) REFERENCES countries (id),
    CONSTRAINT fk_buyer_currency FOREIGN KEY (currency_id) REFERENCES currencies (id)
) ENGINE=InnoDB;

CREATE TABLE suppliers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    supplier_category_id BIGINT UNSIGNED NULL,
    country_id BIGINT UNSIGNED NULL,
    currency_id BIGINT UNSIGNED NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    contact_person VARCHAR(120) NULL,
    address TEXT NULL,
    tax_number VARCHAR(80) NULL,
    payment_terms_days SMALLINT UNSIGNED NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_supplier_code (company_id, code),
    CONSTRAINT fk_supplier_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_supplier_category FOREIGN KEY (supplier_category_id) REFERENCES supplier_categories (id),
    CONSTRAINT fk_supplier_country FOREIGN KEY (country_id) REFERENCES countries (id),
    CONSTRAINT fk_supplier_currency FOREIGN KEY (currency_id) REFERENCES currencies (id)
) ENGINE=InnoDB;

CREATE TABLE warehouse_locations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(120) NOT NULL,
    location_type ENUM('zone', 'rack', 'shelf', 'bin') NOT NULL DEFAULT 'bin',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_warehouse_location_code (warehouse_id, code),
    CONSTRAINT fk_location_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_location_parent FOREIGN KEY (parent_id) REFERENCES warehouse_locations (id)
) ENGINE=InnoDB;

CREATE TABLE machine_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    description VARCHAR(500) NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE machines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    factory_id BIGINT UNSIGNED NULL,
    machine_type_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(120) NOT NULL,
    brand VARCHAR(100) NULL,
    model VARCHAR(100) NULL,
    serial_number VARCHAR(100) NULL,
    purchase_date DATE NULL,
    status ENUM('active', 'inactive', 'maintenance') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_machine_code (company_id, code),
    CONSTRAINT fk_machine_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_machine_factory FOREIGN KEY (factory_id) REFERENCES factories (id),
    CONSTRAINT fk_machine_type FOREIGN KEY (machine_type_id) REFERENCES machine_types (id)
) ENGINE=InnoDB;

CREATE TABLE designations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(120) NOT NULL,
    grade VARCHAR(30) NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_designation_code (company_id, code),
    CONSTRAINT fk_designation_company FOREIGN KEY (company_id) REFERENCES companies (id)
) ENGINE=InnoDB;

CREATE TABLE teams (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(120) NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_team_code (company_id, code),
    CONSTRAINT fk_team_company FOREIGN KEY (company_id) REFERENCES companies (id)
) ENGINE=InnoDB;

CREATE TABLE employees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    designation_id BIGINT UNSIGNED NULL,
    team_id BIGINT UNSIGNED NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    joining_date DATE NULL,
    employment_type ENUM('permanent', 'contract', 'temporary') NOT NULL DEFAULT 'permanent',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_employee_code (company_id, code),
    CONSTRAINT fk_employee_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_employee_designation FOREIGN KEY (designation_id) REFERENCES designations (id),
    CONSTRAINT fk_employee_team FOREIGN KEY (team_id) REFERENCES teams (id)
) ENGINE=InnoDB;

CREATE TABLE operations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    machine_type_id BIGINT UNSIGNED NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(150) NOT NULL,
    operation_group VARCHAR(80) NULL,
    default_smv DECIMAL(10,4) NOT NULL DEFAULT 0,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_operation_code (company_id, code),
    CONSTRAINT fk_operation_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_operation_machine_type FOREIGN KEY (machine_type_id) REFERENCES machine_types (id)
) ENGINE=InnoDB;

CREATE TABLE styles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    buyer_id BIGINT UNSIGNED NULL,
    style_category_id BIGINT UNSIGNED NULL,
    product_category_id BIGINT UNSIGNED NULL,
    brand_id BIGINT UNSIGNED NULL,
    season_id BIGINT UNSIGNED NULL,
    code VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    gender ENUM('men', 'women', 'unisex', 'kids') NULL,
    description TEXT NULL,
    status ENUM('draft', 'active', 'inactive', 'archived') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_style_code (company_id, code),
    CONSTRAINT fk_style_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_style_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_style_category FOREIGN KEY (style_category_id) REFERENCES style_categories (id),
    CONSTRAINT fk_style_product_category FOREIGN KEY (product_category_id) REFERENCES product_categories (id),
    CONSTRAINT fk_style_brand FOREIGN KEY (brand_id) REFERENCES brands (id),
    CONSTRAINT fk_style_season FOREIGN KEY (season_id) REFERENCES seasons (id)
) ENGINE=InnoDB;

CREATE TABLE style_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    style_id BIGINT UNSIGNED NOT NULL,
    version_number VARCHAR(20) NOT NULL,
    effective_date DATE NULL,
    change_summary VARCHAR(500) NULL,
    status ENUM('draft', 'approved', 'obsolete') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_style_version (style_id, version_number),
    CONSTRAINT fk_style_version_style FOREIGN KEY (style_id) REFERENCES styles (id)
) ENGINE=InnoDB;

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    style_id BIGINT UNSIGNED NULL,
    product_type_id BIGINT UNSIGNED NULL,
    product_subcategory_id BIGINT UNSIGNED NULL,
    code VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    status ENUM('draft', 'active', 'inactive', 'archived') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_product_code (company_id, code),
    CONSTRAINT fk_product_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_product_style FOREIGN KEY (style_id) REFERENCES styles (id),
    CONSTRAINT fk_product_type FOREIGN KEY (product_type_id) REFERENCES product_types (id),
    CONSTRAINT fk_product_subcategory FOREIGN KEY (product_subcategory_id) REFERENCES product_subcategories (id)
) ENGINE=InnoDB;

CREATE TABLE product_variants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    color_id BIGINT UNSIGNED NULL,
    size_id BIGINT UNSIGNED NULL,
    sku VARCHAR(80) NOT NULL UNIQUE,
    barcode VARCHAR(100) NULL UNIQUE,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_product_color_size (product_id, color_id, size_id),
    CONSTRAINT fk_variant_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_variant_color FOREIGN KEY (color_id) REFERENCES colors (id),
    CONSTRAINT fk_variant_size FOREIGN KEY (size_id) REFERENCES sizes (id)
) ENGINE=InnoDB;

CREATE TABLE product_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    product_variant_id BIGINT UNSIGNED NULL,
    code_type ENUM('internal', 'buyer', 'barcode', 'qr') NOT NULL DEFAULT 'internal',
    code_value VARCHAR(150) NOT NULL UNIQUE,
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_product_code_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_product_code_variant FOREIGN KEY (product_variant_id) REFERENCES product_variants (id)
) ENGINE=InnoDB;

CREATE TABLE product_images (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    product_variant_id BIGINT UNSIGNED NULL,
    image_path VARCHAR(500) NOT NULL,
    alt_text VARCHAR(200) NULL,
    image_type ENUM('main', 'front', 'back', 'detail', 'other') NOT NULL DEFAULT 'other',
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_product_image_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_product_image_variant FOREIGN KEY (product_variant_id) REFERENCES product_variants (id)
) ENGINE=InnoDB;

CREATE TABLE product_measurements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    style_version_id BIGINT UNSIGNED NULL,
    size_id BIGINT UNSIGNED NULL,
    uom_id BIGINT UNSIGNED NOT NULL,
    point_name VARCHAR(150) NOT NULL,
    specification DECIMAL(12,4) NOT NULL,
    tolerance_plus DECIMAL(12,4) NOT NULL DEFAULT 0,
    tolerance_minus DECIMAL(12,4) NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_measurement_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_measurement_style_version FOREIGN KEY (style_version_id) REFERENCES style_versions (id),
    CONSTRAINT fk_measurement_size FOREIGN KEY (size_id) REFERENCES sizes (id),
    CONSTRAINT fk_measurement_uom FOREIGN KEY (uom_id) REFERENCES uoms (id)
) ENGINE=InnoDB;

CREATE TABLE tech_packs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    style_id BIGINT UNSIGNED NOT NULL,
    style_version_id BIGINT UNSIGNED NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(180) NOT NULL,
    file_path VARCHAR(500) NULL,
    revision_date DATE NULL,
    status ENUM('draft', 'approved', 'obsolete') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_tech_pack_style FOREIGN KEY (style_id) REFERENCES styles (id),
    CONSTRAINT fk_tech_pack_style_version FOREIGN KEY (style_version_id) REFERENCES style_versions (id)
) ENGINE=InnoDB;