START TRANSACTION;

DROP PROCEDURE IF EXISTS add_column_if_missing;
DROP PROCEDURE IF EXISTS modify_phone_type_width_if_needed;
DROP PROCEDURE IF EXISTS rename_column_if_needed;
DROP PROCEDURE IF EXISTS modify_column_type_if_needed;

DELIMITER $$
CREATE PROCEDURE add_column_if_missing(
    IN p_table VARCHAR(64),
    IN p_column VARCHAR(64),
    IN p_definition TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_column
    ) THEN
        SET @sql = CONCAT(
            'ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

CREATE PROCEDURE modify_phone_type_width_if_needed(
    IN p_table VARCHAR(64),
    IN p_column VARCHAR(64)
)
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_column
          AND (
              DATA_TYPE <> 'varchar'
              OR COALESCE(CHARACTER_MAXIMUM_LENGTH, 0) < 50
          )
    ) THEN
        SET @sql = CONCAT(
            'ALTER TABLE `', p_table, '` MODIFY COLUMN `', p_column, '` VARCHAR(50) NOT NULL DEFAULT ''PHONE'''
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

CREATE PROCEDURE rename_column_if_needed(
    IN p_table VARCHAR(64),
    IN p_old_column VARCHAR(64),
    IN p_new_column VARCHAR(64),
    IN p_definition TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_new_column
    ) AND EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_old_column
    ) THEN
        SET @sql = CONCAT(
            'ALTER TABLE `', p_table, '` CHANGE COLUMN `', p_old_column, '` `', p_new_column, '` ', p_definition
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

CREATE PROCEDURE modify_column_type_if_needed(
    IN p_table VARCHAR(64),
    IN p_column VARCHAR(64),
    IN p_definition TEXT
)
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_column
    ) THEN
        SET @sql = CONCAT(
            'ALTER TABLE `', p_table, '` MODIFY COLUMN `', p_column, '` ', p_definition
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

DELIMITER ;

-- =========================================================
-- Core column additions used by Company / Station / User
-- =========================================================

CALL add_column_if_missing('company', 'logo', 'LONGBLOB NULL');
CALL add_column_if_missing('company', 'phone', 'VARCHAR(255) NULL');
CALL add_column_if_missing('company', 'main_number', 'VARCHAR(50) NULL');
CALL add_column_if_missing('company', 'ext', 'VARCHAR(20) NULL');
CALL add_column_if_missing('company', 'direct_number', 'VARCHAR(50) NULL');
CALL add_column_if_missing('company', 'cellphone', 'VARCHAR(50) NULL');
CALL add_column_if_missing('company', 'whatsapp', 'VARCHAR(50) NULL');

CALL add_column_if_missing('users', 'billing_address_id', 'INT NULL');
CALL add_column_if_missing('users', 'shipping_address_id', 'INT NULL');
CALL add_column_if_missing('users', 'website', 'VARCHAR(255) NULL');
CALL add_column_if_missing('users', 'main_number', 'VARCHAR(50) NULL');
CALL add_column_if_missing('users', 'ext', 'VARCHAR(20) NULL');
CALL add_column_if_missing('users', 'direct_number', 'VARCHAR(50) NULL');
CALL add_column_if_missing('users', 'cellphone', 'VARCHAR(50) NULL');
CALL add_column_if_missing('users', 'whatsapp', 'VARCHAR(50) NULL');
CALL add_column_if_missing('users', 'profile_image', 'LONGBLOB NULL');

CALL add_column_if_missing('station', 'logo', 'LONGBLOB NULL');
CALL add_column_if_missing('station', 'email', 'VARCHAR(255) NULL');
CALL add_column_if_missing('station', 'website', 'VARCHAR(255) NULL');
CALL add_column_if_missing('station', 'billing_address_id', 'INT NULL');
CALL add_column_if_missing('station', 'shipping_address_id', 'INT NULL');
CALL add_column_if_missing('station', 'main_number', 'VARCHAR(50) NULL');
CALL add_column_if_missing('station', 'ext', 'VARCHAR(20) NULL');
CALL add_column_if_missing('station', 'direct_number', 'VARCHAR(50) NULL');
CALL add_column_if_missing('station', 'cellphone', 'VARCHAR(50) NULL');
CALL add_column_if_missing('station', 'whatsapp', 'VARCHAR(50) NULL');
CALL add_column_if_missing('station', 'tax_agency_id', 'INT NULL');
CALL add_column_if_missing('station', 'tax_number', 'VARCHAR(255) NULL');
CALL add_column_if_missing('station', 'hide_logo', 'TINYINT(1) NOT NULL DEFAULT 0');

-- =========================================================
-- Order-related support columns
-- =========================================================

CALL add_column_if_missing('order_status', 'sort_order', 'INT NOT NULL DEFAULT 0');
CALL add_column_if_missing('order_products', 'presentation_id', 'INT NULL');
CALL add_column_if_missing('order_products', 'packaging_id', 'INT NULL');
CALL add_column_if_missing('order_products', 'fulfilled_qty', 'INT NULL DEFAULT 0');
CALL add_column_if_missing('ticket', 'assigned_user_id', 'INT NULL');
CALL add_column_if_missing('order_event_log', 'internal_note', 'VARCHAR(255) NULL');
CALL rename_column_if_needed('parcel', 'id', 'parcel_id', 'INT NOT NULL AUTO_INCREMENT');
CALL rename_column_if_needed('parcel_item', 'id', 'parcel_item_id', 'INT NOT NULL AUTO_INCREMENT');
CALL modify_column_type_if_needed('parcel', 'parcel_id', 'INT NOT NULL AUTO_INCREMENT');
CALL modify_column_type_if_needed('parcel_item', 'parcel_item_id', 'INT NOT NULL AUTO_INCREMENT');
CALL add_column_if_missing('parcel', 'parcel_number', 'VARCHAR(50) NULL');
CALL add_column_if_missing('parcel', 'invoice_number', 'VARCHAR(100) NULL');
CALL add_column_if_missing('parcel', 'parcel_status_id', 'INT NOT NULL DEFAULT 1');
CALL add_column_if_missing('parcel', 'created_by', 'INT NULL');
CALL add_column_if_missing('parcel', 'logistic_provider_id', 'INT NULL');
CALL add_column_if_missing('parcel', 'tracking_number', 'VARCHAR(255) NULL');
CALL add_column_if_missing('parcel', 'updated_at', 'TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');
CALL modify_column_type_if_needed('parcel', 'note', 'TEXT NULL');
CALL modify_column_type_if_needed('parcel', 'invoice_id', 'INT NULL');
CALL modify_column_type_if_needed('parcel', 'logistic_p_id', 'INT NULL');
CALL modify_column_type_if_needed('parcel', 'tracking_number', 'VARCHAR(255) NULL');

-- =========================================================
-- Geographical note fields
-- =========================================================

CALL add_column_if_missing('country', 'note', 'TEXT NULL');
CALL add_column_if_missing('territory', 'note', 'TEXT NULL');
CALL add_column_if_missing('city', 'note', 'TEXT NULL');

-- =========================================================
-- External settings fields
-- =========================================================

CALL add_column_if_missing('logistic_provider', 'account_number', 'VARCHAR(100) NULL');
CALL add_column_if_missing('logistic_provider', 'note', 'TEXT NULL');
CALL add_column_if_missing('logistic_provider', 'provider_company_id', 'INT NULL');
CALL add_column_if_missing('tax_agency', 'note', 'TEXT NULL');

-- =========================================================
-- New tables
-- =========================================================

CREATE TABLE IF NOT EXISTS address_type (
    address_type_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    key_name VARCHAR(100) NOT NULL UNIQUE,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS phone_type (
    phone_type_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    key_name VARCHAR(100) NOT NULL UNIQUE,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS company_address_link (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    address_id INT NOT NULL,
    address_type_id INT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_company_address_link_owner (company_id, status)
);

CREATE TABLE IF NOT EXISTS station_address_link (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id INT NOT NULL,
    address_id INT NOT NULL,
    address_type_id INT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_station_address_link_owner (station_id, status)
);

CREATE TABLE IF NOT EXISTS user_address_link (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    address_id INT NOT NULL,
    address_type_id INT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_address_link_owner (user_id, status)
);

CREATE TABLE IF NOT EXISTS company_phone (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    country_id INT NOT NULL,
    phone_number VARCHAR(50) NOT NULL,
    phone_type VARCHAR(50) NOT NULL DEFAULT 'PHONE',
    extension VARCHAR(20) DEFAULT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_company_phone_company (company_id)
);

CREATE TABLE IF NOT EXISTS station_phone (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id INT NOT NULL,
    country_id INT NOT NULL,
    phone_number VARCHAR(50) NOT NULL,
    phone_type VARCHAR(50) NOT NULL DEFAULT 'PHONE',
    extension VARCHAR(20) DEFAULT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_station_phone_station (station_id)
);

CREATE TABLE IF NOT EXISTS user_phone (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    country_id INT NOT NULL,
    phone_number VARCHAR(50) NOT NULL,
    phone_type VARCHAR(50) NOT NULL DEFAULT 'PHONE',
    extension VARCHAR(20) DEFAULT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_phone_user (user_id)
);

CREATE TABLE IF NOT EXISTS company_product (
    company_id INT NOT NULL,
    product_id INT NOT NULL,
    max_qty INT NOT NULL DEFAULT 5,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (company_id, product_id),
    INDEX idx_company_product_status (company_id, status)
);

CREATE TABLE IF NOT EXISTS product_document (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    file_size INT NOT NULL DEFAULT 0,
    file_data LONGBLOB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product_document_product (product_id)
);

CREATE TABLE IF NOT EXISTS provider_international_corporation (
    provider_company_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country_id INT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS logistic_provider_phone (
    id INT AUTO_INCREMENT PRIMARY KEY,
    provider_id INT NOT NULL,
    country_id INT NOT NULL,
    phone_number VARCHAR(50) NOT NULL,
    phone_type VARCHAR(50) NOT NULL DEFAULT 'PHONE',
    extension VARCHAR(20) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    INDEX idx_provider_phone_provider (provider_id)
);

CREATE TABLE IF NOT EXISTS tax_agency_phone (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NOT NULL,
    country_id INT NOT NULL,
    phone_number VARCHAR(50) NOT NULL,
    phone_type VARCHAR(50) NOT NULL DEFAULT 'PHONE',
    extension VARCHAR(20) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    INDEX idx_agency_phone_agency (agency_id)
);

CREATE TABLE IF NOT EXISTS password_reset_request (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(200) NOT NULL,
    user_id INT NULL,
    request_note TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_password_reset_request_email (email),
    INDEX idx_password_reset_request_status (status)
);

CREATE TABLE IF NOT EXISTS access_request (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NULL,
    company_name VARCHAR(255) NULL,
    email VARCHAR(200) NOT NULL,
    phone VARCHAR(100) NULL,
    request_note TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_access_request_email (email),
    INDEX idx_access_request_status (status)
);

CREATE TABLE IF NOT EXISTS ticket (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    assigned_user_id INT NULL,
    company_id INT NULL,
    station_id INT NULL,
    subject VARCHAR(255) NOT NULL,
    description TEXT NULL,
    priority_id INT NULL,
    status ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ticket_company_station (company_id, station_id),
    INDEX idx_ticket_user_status (user_id, status),
    INDEX idx_ticket_assigned_user (assigned_user_id)
);

CREATE TABLE IF NOT EXISTS ticket_reply (
    ticket_reply_id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ticket_reply_ticket (ticket_id),
    INDEX idx_ticket_reply_user (user_id)
);

CREATE TABLE IF NOT EXISTS parcel_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    color VARCHAR(30) NOT NULL DEFAULT '#FFE45C',
    sort_order INT NOT NULL DEFAULT 100,
    status TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS parcel (
    parcel_id INT AUTO_INCREMENT PRIMARY KEY,
    ord_id INT NOT NULL,
    parcel_number VARCHAR(50) NOT NULL,
    invoice_number VARCHAR(100) NOT NULL,
    logistic_provider_id INT NULL,
    tracking_number VARCHAR(255) NULL,
    parcel_status_id INT NOT NULL,
    created_by INT NULL,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_parcel_order (ord_id),
    INDEX idx_parcel_status (parcel_status_id)
);

CREATE TABLE IF NOT EXISTS parcel_item (
    parcel_item_id INT AUTO_INCREMENT PRIMARY KEY,
    parcel_id INT NOT NULL,
    ord_prod_id INT NOT NULL,
    qty INT NOT NULL DEFAULT 0,
    INDEX idx_parcel_item_parcel (parcel_id),
    INDEX idx_parcel_item_order_product (ord_prod_id)
);

CALL add_column_if_missing('parcel', 'logistic_provider_id', 'INT NULL');
CALL add_column_if_missing('parcel', 'tracking_number', 'VARCHAR(255) NULL');
CALL add_column_if_missing('parcel', 'updated_at', 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');

UPDATE parcel
SET parcel_number = CAST(parcel_id AS CHAR)
WHERE (parcel_number IS NULL OR parcel_number = '')
  AND parcel_id IS NOT NULL;

UPDATE parcel p
LEFT JOIN invoices i ON i.inv_id = p.invoice_id
SET p.invoice_number = COALESCE(i.inv_number, CONCAT('INV-', p.invoice_id), CONCAT('PARCEL-', p.parcel_id))
WHERE (p.invoice_number IS NULL OR p.invoice_number = '');

-- =========================================================
-- Widen phone_type storage where older tables already exist
-- =========================================================

CALL modify_phone_type_width_if_needed('company_phone', 'phone_type');
CALL modify_phone_type_width_if_needed('station_phone', 'phone_type');
CALL modify_phone_type_width_if_needed('user_phone', 'phone_type');
CALL modify_phone_type_width_if_needed('logistic_provider_phone', 'phone_type');
CALL modify_phone_type_width_if_needed('tax_agency_phone', 'phone_type');

-- =========================================================
-- Seed values
-- =========================================================

INSERT INTO address_type (name, key_name, sort_order, status)
SELECT 'Billing', 'BILLING', 10, 1
WHERE NOT EXISTS (SELECT 1 FROM address_type WHERE key_name = 'BILLING');

INSERT INTO address_type (name, key_name, sort_order, status)
SELECT 'Shipping', 'SHIPPING', 20, 1
WHERE NOT EXISTS (SELECT 1 FROM address_type WHERE key_name = 'SHIPPING');

INSERT INTO address_type (name, key_name, sort_order, status)
SELECT 'Mailing', 'MAILING', 30, 1
WHERE NOT EXISTS (SELECT 1 FROM address_type WHERE key_name = 'MAILING');

INSERT INTO phone_type (name, key_name, sort_order, status)
SELECT 'Phone', 'PHONE', 10, 1
WHERE NOT EXISTS (SELECT 1 FROM phone_type WHERE key_name = 'PHONE');

INSERT INTO phone_type (name, key_name, sort_order, status)
SELECT 'FAX', 'FAX', 20, 1
WHERE NOT EXISTS (SELECT 1 FROM phone_type WHERE key_name = 'FAX');

INSERT INTO phone_type (name, key_name, sort_order, status)
SELECT 'WhatsApp', 'WHATSAPP', 30, 1
WHERE NOT EXISTS (SELECT 1 FROM phone_type WHERE key_name = 'WHATSAPP');

INSERT INTO phone_type (name, key_name, sort_order, status)
SELECT 'Telegram', 'TELEGRAM', 40, 1
WHERE NOT EXISTS (SELECT 1 FROM phone_type WHERE key_name = 'TELEGRAM');

INSERT INTO phone_type (name, key_name, sort_order, status)
SELECT 'VOIP', 'VOIP', 50, 1
WHERE NOT EXISTS (SELECT 1 FROM phone_type WHERE key_name = 'VOIP');

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Processing', 'Order is being fulfilled through parcels.', 1, 35
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Processing'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Not Approved', 'Order rejected or needs revision.', 1, 40
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Not Approved'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Edit', 'Order needs edits before moving forward.', 1, 45
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Edit'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Revised', 'Order was revised and is ready for review.', 1, 50
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Revised'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Other', 'Order moved to another review path.', 1, 55
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Other'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Cancel', 'Order was cancelled.', 1, 60
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Cancel'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Reviewed', 'Order review was completed.', 1, 65
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Reviewed'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Request Review', 'Ask for another review before fulfillment.', 1, 70
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Request Review'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Invoice Whole', 'Fulfill the whole order.', 1, 75
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Invoice Whole'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Invoice Partial', 'Fulfill the order using parcels.', 1, 80
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Invoice Partial'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Consolidated', 'All parcels are delivered and the order is ready to complete.', 1, 90
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Consolidated'));

INSERT INTO order_status (name, description, status, sort_order)
SELECT 'Completed', 'Order is completed.', 1, 100
WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE LOWER(name) = LOWER('Completed'));

UPDATE order_status
SET status = 1
WHERE LOWER(name) IN (
    'created',
    'approved',
    'received',
    'processing',
    'not approved',
    'edit',
    'revised',
    'other',
    'cancel',
    'reviewed',
    'request review',
    'invoice whole',
    'invoice partial',
    'consolidated',
    'completed'
);

INSERT INTO parcel_status (name, color, sort_order, status)
SELECT 'Created', '#FFE45C', 10, 1
WHERE NOT EXISTS (SELECT 1 FROM parcel_status WHERE LOWER(name) = LOWER('Created'));

INSERT INTO parcel_status (name, color, sort_order, status)
SELECT 'Packed', '#BDE3FF', 20, 1
WHERE NOT EXISTS (SELECT 1 FROM parcel_status WHERE LOWER(name) = LOWER('Packed'));

INSERT INTO parcel_status (name, color, sort_order, status)
SELECT 'Labeled', '#D9D0FF', 30, 1
WHERE NOT EXISTS (SELECT 1 FROM parcel_status WHERE LOWER(name) = LOWER('Labeled'));

INSERT INTO parcel_status (name, color, sort_order, status)
SELECT 'Shipped', '#7FD7A1', 40, 1
WHERE NOT EXISTS (SELECT 1 FROM parcel_status WHERE LOWER(name) = LOWER('Shipped'));

INSERT INTO parcel_status (name, color, sort_order, status)
SELECT 'In Transit', '#8CC8FF', 50, 1
WHERE NOT EXISTS (SELECT 1 FROM parcel_status WHERE LOWER(name) = LOWER('In Transit'));

INSERT INTO parcel_status (name, color, sort_order, status)
SELECT 'Delivered', '#6EA8FF', 60, 1
WHERE NOT EXISTS (SELECT 1 FROM parcel_status WHERE LOWER(name) = LOWER('Delivered'));

DROP PROCEDURE IF EXISTS add_column_if_missing;
DROP PROCEDURE IF EXISTS modify_phone_type_width_if_needed;
DROP PROCEDURE IF EXISTS rename_column_if_needed;
DROP PROCEDURE IF EXISTS modify_column_type_if_needed;

COMMIT;