-- Bir defa phpMyAdmin üzerinden çalıştırın (mevcut tablolar korunur)
CREATE TABLE IF NOT EXISTS supplier_order_links (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_no VARCHAR(70) NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE,
 supplier_email VARCHAR(190) NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 expires_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 first_viewed_at DATETIME NULL,
 last_viewed_at DATETIME NULL,
 view_count INT UNSIGNED NOT NULL DEFAULT 0,
 response VARCHAR(20) NULL,
 responded_at DATETIME NULL,
 response_note VARCHAR(1000) NULL,
 sent_at DATETIME NULL,
 INDEX ix_supplier_link_order(order_no),
 CONSTRAINT fk_link_order FOREIGN KEY(order_no) REFERENCES purchase_orders(order_no) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS supplier_order_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 link_id BIGINT UNSIGNED NOT NULL,
 event_type VARCHAR(30) NOT NULL,
 happened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 ip_hash CHAR(64) NULL,
 FOREIGN KEY(link_id) REFERENCES supplier_order_links(id) ON DELETE CASCADE,
 INDEX ix_supplier_event(link_id,happened_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;