-- Attachments: lets an upload land straight in Google Drive instead of this server's disk.
-- storage_path becomes nullable (a Drive-stored row never touches local disk); storage_type
-- records which backend the row actually uses; drive_file_id/drive_web_view_link are the Drive
-- reference for GOOGLE_DRIVE rows only.
-- Idempotent — safe to re-run.
-- Deploy (local): mysql -u root -p db_abos_v0.1 < db/add_attachment_drive_storage.sql

USE db_abos_v0.1;

SET @is_nullable = (SELECT IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_attachments' AND COLUMN_NAME = 'storage_path');
SET @sql = IF(@is_nullable = 'NO', 'ALTER TABLE fact_attachments MODIFY COLUMN storage_path VARCHAR(500) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_attachments' AND COLUMN_NAME = 'storage_type');
SET @sql = IF(@col_exists = 0, "ALTER TABLE fact_attachments ADD COLUMN storage_type VARCHAR(20) NOT NULL DEFAULT 'LOCAL' AFTER storage_path", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_attachments' AND COLUMN_NAME = 'drive_file_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_attachments ADD COLUMN drive_file_id VARCHAR(255) NULL AFTER storage_type', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_attachments' AND COLUMN_NAME = 'drive_web_view_link');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_attachments ADD COLUMN drive_web_view_link VARCHAR(1000) NULL AFTER drive_file_id', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;