START TRANSACTION;

DROP PROCEDURE IF EXISTS add_column_if_missing;
DROP PROCEDURE IF EXISTS rename_column_if_needed;
DROP PROCEDURE IF EXISTS modify_column_type_if_needed;
DROP PROCEDURE IF EXISTS add_index_if_missing;
DROP PROCEDURE IF EXISTS insert_order_status_if_missing;
DROP PROCEDURE IF EXISTS insert_parcel_status_if_missing;

DELIMITER $$

CREATE PROCEDURE add_column_if_missing(
    IN p_table VARCHAR(64),
    IN p_column VARCHAR(64),
    IN p_definition TEXT
)
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
    ) AND 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 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 EXISTS (
        SELECT 1
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
    ) AND 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$$

CREATE PROCEDURE add_index_if_missing(
    IN p_table VARCHAR(64),
    IN p_index_name VARCHAR(64),
    IN p_index_sql TEXT
)
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
    ) AND NOT EXISTS (
        SELECT 1
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND INDEX_NAME = p_index_name
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD ', p_index_sql);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

CREATE PROCEDURE insert_order_status_if_missing(
    IN p_name VARCHAR(100),
    IN p_description TEXT,
    IN p_sort_order INT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM order_status
        WHERE LOWER(name) = LOWER(p_name)
    ) THEN
        INSERT INTO order_status (name, description, status, sort_order)
        VALUES (p_name, p_description, 1, p_sort_order);
    ELSE
        UPDATE order_status
        SET status = 1,
            sort_order = CASE WHEN sort_order IS NULL OR sort_order = 0 THEN p_sort_order ELSE sort_order END,
            description = CASE WHEN (description IS NULL OR description = '') AND p_description <> '' THEN p_description ELSE description END
        WHERE LOWER(name) = LOWER(p_name);
    END IF;
END$$

CREATE PROCEDURE insert_parcel_status_if_missing(
    IN p_name VARCHAR(100),
    IN p_color VARCHAR(30),
    IN p_sort_order INT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM parcel_status
        WHERE LOWER(name) = LOWER(p_name)
    ) THEN
        INSERT INTO parcel_status (name, color, sort_order, status)
        VALUES (p_name, p_color, p_sort_order, 1);
    ELSE
        UPDATE parcel_status
        SET status = 1,
            sort_order = CASE WHEN sort_order IS NULL OR sort_order = 0 THEN p_sort_order ELSE sort_order END,
            color = CASE WHEN color IS NULL OR color = '' THEN p_color ELSE color END
        WHERE LOWER(name) = LOWER(p_name);
    END IF;
END$$

DELIMITER ;

-- =========================================================
-- Tables needed by parcel flow
-- =========================================================

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 NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL 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)
);

-- =========================================================
-- Core parcel-flow column fixes
-- =========================================================

CALL add_column_if_missing('order_status', 'sort_order', 'INT NOT NULL DEFAULT 0');
CALL add_column_if_missing('order_products', 'fulfilled_qty', 'INT 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_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');

CALL add_index_if_missing('parcel', 'idx_parcel_order', 'INDEX `idx_parcel_order` (`ord_id`)');
CALL add_index_if_missing('parcel', 'idx_parcel_status', 'INDEX `idx_parcel_status` (`parcel_status_id`)');
CALL add_index_if_missing('parcel_item', 'idx_parcel_item_parcel', 'INDEX `idx_parcel_item_parcel` (`parcel_id`)');
CALL add_index_if_missing('parcel_item', 'idx_parcel_item_order_product', 'INDEX `idx_parcel_item_order_product` (`ord_prod_id`)');

-- =========================================================
-- Backfill old parcel data into new columns
-- =========================================================

SET @has_parcel_logistic_provider_id := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'parcel'
      AND COLUMN_NAME = 'logistic_provider_id'
);
SET @has_parcel_logistic_p_id := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'parcel'
      AND COLUMN_NAME = 'logistic_p_id'
);
SET @sql := IF(@has_parcel_logistic_provider_id = 1 AND @has_parcel_logistic_p_id = 1,
    'UPDATE parcel SET logistic_provider_id = COALESCE(logistic_provider_id, logistic_p_id) WHERE logistic_provider_id IS NULL AND logistic_p_id IS NOT NULL',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

SET @has_parcel_invoice_id := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'parcel'
      AND COLUMN_NAME = 'invoice_id'
);
SET @has_invoices_table := (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
);
SET @has_invoices_inv_number := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'invoices'
      AND COLUMN_NAME = 'inv_number'
);
SET @sql := IF(@has_parcel_invoice_id = 1 AND @has_invoices_table = 1 AND @has_invoices_inv_number = 1,
    'UPDATE parcel p LEFT JOIN invoices i ON i.inv_id = p.invoice_id SET p.invoice_number = COALESCE(p.invoice_number, i.inv_number, CONCAT(''INV-'', p.invoice_id), CONCAT(''PARCEL-'', p.parcel_id)) WHERE p.invoice_number IS NULL OR p.invoice_number = ''''',
    'UPDATE parcel SET invoice_number = COALESCE(NULLIF(invoice_number, ''''), CONCAT(''PARCEL-'', parcel_id)) WHERE invoice_number IS NULL OR invoice_number = ''''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE order_products
SET fulfilled_qty = 0
WHERE fulfilled_qty IS NULL;

-- =========================================================
-- Order statuses required by parcel flow
-- =========================================================

CALL insert_order_status_if_missing('Created', 'Initial order placement', 10);
CALL insert_order_status_if_missing('Approved', 'Order confirmed by approving manager', 20);
CALL insert_order_status_if_missing('Received', 'Order received at station', 30);
CALL insert_order_status_if_missing('Processing', 'Order is being fulfilled through parcels.', 35);
CALL insert_order_status_if_missing('Not Approved', 'Order rejected or needs revision.', 40);
CALL insert_order_status_if_missing('Edit', 'Order needs edits before moving forward.', 45);
CALL insert_order_status_if_missing('Revised', 'Order was revised and is ready for review.', 50);
CALL insert_order_status_if_missing('Other', 'Order moved to another review path.', 55);
CALL insert_order_status_if_missing('Cancel', 'Order was cancelled.', 60);
CALL insert_order_status_if_missing('Reviewed', 'Order review was completed.', 65);
CALL insert_order_status_if_missing('Request Review', 'Ask for another review before fulfillment.', 70);
CALL insert_order_status_if_missing('Invoice Whole', 'Fulfill the whole order.', 75);
CALL insert_order_status_if_missing('Invoice Partial', 'Fulfill the order using parcels.', 80);
CALL insert_order_status_if_missing('Consolidated', 'All parcels are delivered and the order is ready to complete.', 90);
CALL insert_order_status_if_missing('Completed', 'Order is completed.', 100);

-- =========================================================
-- Parcel statuses required by parcel flow
-- =========================================================

CALL insert_parcel_status_if_missing('Created', '#FFE45C', 10);
CALL insert_parcel_status_if_missing('Packed', '#BDE3FF', 20);
CALL insert_parcel_status_if_missing('Labeled', '#D9D0FF', 30);
CALL insert_parcel_status_if_missing('Shipped', '#7FD7A1', 40);
CALL insert_parcel_status_if_missing('In Transit', '#8CC8FF', 50);
CALL insert_parcel_status_if_missing('Delivered', '#6EA8FF', 60);

DROP PROCEDURE IF EXISTS add_column_if_missing;
DROP PROCEDURE IF EXISTS rename_column_if_needed;
DROP PROCEDURE IF EXISTS modify_column_type_if_needed;
DROP PROCEDURE IF EXISTS add_index_if_missing;
DROP PROCEDURE IF EXISTS insert_order_status_if_missing;
DROP PROCEDURE IF EXISTS insert_parcel_status_if_missing;

COMMIT;