-- Step 40: Add customer delivery address book
-- Feature: Portal customer address book for delivery checkout
-- Date: 2026-05-15

CREATE TABLE `customerDeliveryAddresses` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `customerUserId` bigint(20) UNSIGNED NOT NULL,
  `publicId` char(36) NOT NULL,
  `label` varchar(60) NOT NULL,
  `recipientName` varchar(120) DEFAULT NULL,
  `phone` varchar(40) DEFAULT NULL,
  `unit` varchar(80) NOT NULL,
  `address` varchar(255) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `deletedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customerDeliveryAddresses_publicId` (`publicId`),
  KEY `idx_customerDeliveryAddresses_user_label` (`customerUserId`,`label`),
  KEY `idx_customerDeliveryAddresses_deleted` (`deletedAt`),
  CONSTRAINT `fk_customerDeliveryAddresses_customerUserId`
    FOREIGN KEY (`customerUserId`) REFERENCES `customerUsers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;