-- WhatsApp Bot V23 - Purchase-order replacement warranty upgrade.
-- Execute ONCE in phpMyAdmin before uploading V23 PHP files.
-- Safe on re-import: tables are IF NOT EXISTS, missing legacy columns added conditionally.
-- Back up MySQL database first; never drop orders, transactions, or original customer data.

CREATE TABLE IF NOT EXISTS replacement_order_accounts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id INT NOT NULL,
    order_item_id INT NOT NULL,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    original_email VARCHAR(255) NOT NULL,
    email_hash CHAR(64) NOT NULL,
    origin_type VARCHAR(20) NOT NULL DEFAULT 'direct',
    replacement_status VARCHAR(20) NOT NULL DEFAULT 'active',
    replacement_email VARCHAR(255) NULL,
    delivered_at DATETIME NOT NULL,
    replaced_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_replacement_order_account (order_id, email_hash),
    KEY idx_repl_customer_order (customer_id, order_id),
    KEY idx_repl_delivered (delivered_at),
    KEY idx_repl_status (replacement_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Permanent non-reversible email hashes: protect against reusing the same
-- replacement claim after 20-day detailed-history cleanup.
CREATE TABLE IF NOT EXISTS replacement_claim_registry (
    email_hash CHAR(64) NOT NULL,
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    claimed_at DATETIME NOT NULL,
    PRIMARY KEY (email_hash),
    KEY idx_claim_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS replacement_seen_emails (
    email_hash CHAR(64) NOT NULL,
    first_order_id INT NOT NULL,
    first_seen_at DATETIME NOT NULL,
    PRIMARY KEY (email_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Keep pre-V23 replacement logs usable; new records link to delivered order-account.
CREATE TABLE IF NOT EXISTS replacement_history (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    old_account TEXT NOT NULL,
    new_account TEXT NULL,
    new_stock_id INT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    failure_reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_replacement_customer (customer_id),
    KEY idx_replacement_product (product_id),
    KEY idx_replacement_status (status),
    KEY idx_replacement_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- MySQL 5.7/8 compatible conditional upgrades.
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='replacement_history' AND COLUMN_NAME='order_id');
SET @alter_sql = IF(@has_col=0,'ALTER TABLE replacement_history ADD COLUMN order_id INT NULL','SELECT 1');
PREPARE stmt FROM @alter_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='replacement_history' AND COLUMN_NAME='order_account_id');
SET @alter_sql = IF(@has_col=0,'ALTER TABLE replacement_history ADD COLUMN order_account_id BIGINT UNSIGNED NULL','SELECT 1');
PREPARE stmt FROM @alter_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='replacement_history' AND COLUMN_NAME='old_email');
SET @alter_sql = IF(@has_col=0,'ALTER TABLE replacement_history ADD COLUMN old_email VARCHAR(255) NULL','SELECT 1');
PREPARE stmt FROM @alter_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='replacement_history' AND COLUMN_NAME='new_email');
SET @alter_sql = IF(@has_col=0,'ALTER TABLE replacement_history ADD COLUMN new_email VARCHAR(255) NULL','SELECT 1');
PREPARE stmt FROM @alter_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_index = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='replacement_history' AND INDEX_NAME='uq_v23_replacement_slot');
SET @alter_sql = IF(@has_index=0,'ALTER TABLE replacement_history ADD UNIQUE KEY uq_v23_replacement_slot (order_account_id)','SELECT 1');
PREPARE stmt FROM @alter_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
