CREATE TABLE buyer_seasons (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    buyer_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    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_season_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id)
) ENGINE=InnoDB;

CREATE TABLE buyer_order_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    buyer_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    narration TEXT NULL,
    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_order_type_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id)
) ENGINE=InnoDB;

CREATE TABLE buyer_ship_countries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    buyer_id BIGINT UNSIGNED NOT NULL,
    buyer_order_type_id BIGINT UNSIGNED NOT NULL,
    country_id BIGINT UNSIGNED NULL,
    country_code VARCHAR(10) NULL,
    cutoff_day VARCHAR(10) NULL,
    cutoff_day_name VARCHAR(20) NULL,
    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_ship_country_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_buyer_ship_country_order_type FOREIGN KEY (buyer_order_type_id) REFERENCES buyer_order_types (id),
    CONSTRAINT fk_buyer_ship_country_country FOREIGN KEY (country_id) REFERENCES countries (id)
) ENGINE=InnoDB;