ALTER TABLE fit_masters
    ADD COLUMN description VARCHAR(255) NULL AFTER name;

ALTER TABLE end_users
    ADD COLUMN narration VARCHAR(255) NULL AFTER name;

ALTER TABLE metal_finishes
    ADD COLUMN description VARCHAR(255) NULL AFTER name;

ALTER TABLE thread_details
    DROP COLUMN code,
    DROP COLUMN name,
    ADD COLUMN brand VARCHAR(120) NOT NULL AFTER id,
    ADD COLUMN tex VARCHAR(50) NULL AFTER brand,
    ADD COLUMN tkt VARCHAR(50) NULL AFTER tex,
    ADD COLUMN yarn_count VARCHAR(50) NULL AFTER tkt,
    ADD COLUMN length_m VARCHAR(50) NULL AFTER yarn_count;

ALTER TABLE product_types
    ADD COLUMN product_subcategory_id BIGINT UNSIGNED NULL AFTER product_category_id,
    ADD CONSTRAINT fk_product_type_subcategory FOREIGN KEY (product_subcategory_id) REFERENCES product_subcategories (id);

ALTER TABLE brands
    ADD COLUMN buyer_id BIGINT UNSIGNED NULL AFTER id,
    ADD CONSTRAINT fk_brand_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id);

ALTER TABLE buyers
    ADD COLUMN commercial_address TEXT NULL AFTER address,
    ADD COLUMN commercial_country_id BIGINT UNSIGNED NULL AFTER commercial_address,
    ADD COLUMN commercial_phone VARCHAR(50) NULL AFTER commercial_country_id,
    ADD COLUMN commercial_contact_person VARCHAR(120) NULL AFTER commercial_phone,
    ADD COLUMN commercial_email VARCHAR(150) NULL AFTER commercial_contact_person,
    ADD COLUMN liaison_address TEXT NULL AFTER commercial_email,
    ADD COLUMN liaison_country_id BIGINT UNSIGNED NULL AFTER liaison_address,
    ADD COLUMN liaison_phone VARCHAR(50) NULL AFTER liaison_country_id,
    ADD COLUMN liaison_contact_person VARCHAR(120) NULL AFTER liaison_phone,
    ADD COLUMN liaison_email VARCHAR(150) NULL AFTER liaison_contact_person,
    ADD COLUMN website VARCHAR(180) NULL AFTER liaison_email,
    ADD CONSTRAINT fk_buyer_commercial_country FOREIGN KEY (commercial_country_id) REFERENCES countries (id),
    ADD CONSTRAINT fk_buyer_liaison_country FOREIGN KEY (liaison_country_id) REFERENCES countries (id);

ALTER TABLE suppliers
    ADD COLUMN short_name VARCHAR(80) NULL AFTER name,
    ADD COLUMN transit_time VARCHAR(50) NULL AFTER phone,
    ADD COLUMN cc_person VARCHAR(120) NULL AFTER contact_person,
    ADD COLUMN cc_email VARCHAR(150) NULL AFTER email,
    ADD COLUMN supply_item VARCHAR(120) NULL AFTER cc_email,
    ADD COLUMN lead_time_days SMALLINT UNSIGNED NULL AFTER supply_item;

CREATE TABLE buyer_sample_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    buyer_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    depends_on_id BIGINT UNSIGNED NULL,
    comments_required ENUM('yes', 'no', 'not_mandatory') NOT NULL DEFAULT 'not_mandatory',
    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_buyer_sample_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_buyer_sample_depends FOREIGN KEY (depends_on_id) REFERENCES buyer_sample_types (id)
) ENGINE=InnoDB;