-- 016_kms_encryption.sql
--
-- Adds KMS envelope encryption columns to customers table.
--
-- Each order gets a unique KMS data key (envelope encryption):
--   1. KMS generates a data key (plaintext + encrypted copy)
--   2. Plaintext data key encrypts decryption_key + sender_decryption_key via AES-256-GCM
--   3. Only encrypted copies stored in DB — plaintext data key never persisted
--   4. To read a key: call KMS Decrypt(kms_encrypted_data_key) → data key → decrypt key
--
-- Old columns (decryption_key, sender_decryption_key) kept during migration period.
-- Once all existing orders are migrated, old columns will be dropped in a future migration.

ALTER TABLE customers
  ADD COLUMN IF NOT EXISTS kms_encrypted_data_key    TEXT,    -- KMS-encrypted data key (base64)
  ADD COLUMN IF NOT EXISTS decryption_key_enc        TEXT,    -- AES-256-GCM encrypted recipient key (base64)
  ADD COLUMN IF NOT EXISTS decryption_key_enc_iv     TEXT,    -- IV for decryption_key_enc (base64)
  ADD COLUMN IF NOT EXISTS decryption_key_enc_tag    TEXT,    -- Auth tag for decryption_key_enc (base64)
  ADD COLUMN IF NOT EXISTS sender_decryption_key_enc TEXT,    -- AES-256-GCM encrypted sender key (base64)
  ADD COLUMN IF NOT EXISTS sender_decryption_key_enc_iv  TEXT, -- IV for sender_decryption_key_enc (base64)
  ADD COLUMN IF NOT EXISTS sender_decryption_key_enc_tag TEXT; -- Auth tag for sender_decryption_key_enc (base64)

COMMENT ON COLUMN customers.kms_encrypted_data_key        IS 'KMS-encrypted envelope data key. Call KMS Decrypt to get plaintext data key.';
COMMENT ON COLUMN customers.decryption_key_enc            IS 'Recipient decryption key encrypted with KMS data key (AES-256-GCM, base64).';
COMMENT ON COLUMN customers.decryption_key_enc_iv         IS 'IV for decryption_key_enc (base64).';
COMMENT ON COLUMN customers.decryption_key_enc_tag        IS 'Auth tag for decryption_key_enc (base64).';
COMMENT ON COLUMN customers.sender_decryption_key_enc     IS 'Sender decryption key encrypted with KMS data key (AES-256-GCM, base64).';
COMMENT ON COLUMN customers.sender_decryption_key_enc_iv  IS 'IV for sender_decryption_key_enc (base64).';
COMMENT ON COLUMN customers.sender_decryption_key_enc_tag IS 'Auth tag for sender_decryption_key_enc (base64).';

