-- Phase 8.3: Add explicit partner_id foreign key link to suppliers table (RFC-005)
-- Replaces fragile and loose mobile-number-based domain coupling with an explicit relation.

SET @column_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'suppliers'
      AND COLUMN_NAME = 'partner_id'
);

SET @stmt = IF(@column_exists = 0,
    'ALTER TABLE `suppliers` 
        ADD COLUMN `partner_id` INT(10) UNSIGNED NULL AFTER `name`,
        ADD KEY `idx_suppliers_partner_id` (`partner_id`),
        ADD CONSTRAINT `fk_suppliers_partner` FOREIGN KEY (`partner_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL',
    'SELECT 1'
);

PREPARE alter_stmt FROM @stmt;
EXECUTE alter_stmt;
DEALLOCATE PREPARE alter_stmt;

-- Backfill existing suppliers that match active partner accounts
UPDATE suppliers s
JOIN customers c ON c.mobile = s.mobile AND s.mobile IS NOT NULL AND s.mobile != ''
SET s.partner_id = c.id
WHERE s.partner_id IS NULL;
