-- 008_service_addresses.sql
-- Multiple service addresses per customer (e.g. farm with several ranches)

CREATE TABLE IF NOT EXISTS `service_addresses` (
  `service_address_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id`        int(11) NOT NULL,
  `label`              varchar(100) NOT NULL COMMENT 'e.g. Main Ranch, North Pasture, Barn',
  `service_address`    varchar(255) DEFAULT NULL,
  `service_city`       varchar(100) DEFAULT NULL,
  `service_state`      varchar(50)  DEFAULT NULL,
  `service_zip`        varchar(20)  DEFAULT NULL,
  `is_primary`         tinyint(1)   NOT NULL DEFAULT 0,
  `created_at`         timestamp    NOT NULL DEFAULT current_timestamp(),
  `updated_at`         timestamp    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`service_address_id`),
  KEY `idx_service_addresses_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Link equipment to a specific service address
ALTER TABLE `equipment`
  ADD COLUMN `service_address_id` int(11) DEFAULT NULL AFTER `customer_id`;

-- Store which address was used for an appointment
ALTER TABLE `appointments`
  ADD COLUMN `service_address_id` int(11) DEFAULT NULL AFTER `customer_id`;

-- Migrate existing service addresses from customers table into service_addresses
-- Only for customers that have a service_address set
INSERT INTO `service_addresses` (`customer_id`, `label`, `service_address`, `service_city`, `service_state`, `service_zip`, `is_primary`)
SELECT c.customer_id,
       'Main Address',
       c.service_address,
       c.service_city,
       c.service_state,
       c.service_zip,
       1
FROM `customers` c
WHERE c.service_address IS NOT NULL AND TRIM(c.service_address) != '';

-- Set equipment to use the primary service address where applicable
UPDATE `equipment` e
JOIN `service_addresses` sa ON sa.customer_id = e.customer_id AND sa.is_primary = 1
SET e.service_address_id = sa.service_address_id
WHERE e.service_address_id IS NULL;