-- Migration 011: Deliveries and Service Contracts tables
-- Run this ONCE on each database. Safe to re-run (uses IF NOT EXISTS).

-- ============================================================
-- DELIVERIES
-- ============================================================

CREATE TABLE IF NOT EXISTS `delivery_products` (
    `product_id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120) NOT NULL,
    `container_size` VARCHAR(40) NULL,
    `unit_label` VARCHAR(40) NULL,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `deliveries` (
    `delivery_id` INT AUTO_INCREMENT PRIMARY KEY,
    `delivery_number` VARCHAR(30) NULL,
    `customer_id` INT NULL,
    `recipient_name` VARCHAR(120) NULL,
    `delivery_address` VARCHAR(200) NULL,
    `delivery_city` VARCHAR(80) NULL,
    `delivery_state` VARCHAR(10) NULL,
    `delivery_zip` VARCHAR(10) NULL,
    `technician_id` INT NULL,
    `status` VARCHAR(20) DEFAULT 'pending',
    `scheduled_date` DATE NULL,
    `scheduled_window` VARCHAR(40) NULL,
    `signature_path` VARCHAR(255) NULL,
    `gps_lat` DECIMAL(10,7) NULL,
    `gps_lng` DECIMAL(10,7) NULL,
    `gps_accuracy_m` FLOAT NULL,
    `gps_captured_at` DATETIME NULL,
    `notes` TEXT NULL,
    `receipt_sent_at` DATETIME NULL,
    `created_by` INT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `delivery_lines` (
    `line_id` INT AUTO_INCREMENT PRIMARY KEY,
    `delivery_id` INT NOT NULL,
    `product_id` INT NOT NULL,
    `qty_ordered` INT DEFAULT 0,
    `qty_delivered` INT DEFAULT 0,
    `empties_returned` INT DEFAULT 0,
    `notes` VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default products if empty
INSERT IGNORE INTO `delivery_products` (`name`, `container_size`, `unit_label`) VALUES
    ('34% Hydrogen Peroxide', '5 gal', 'pail'),
    ('Liquid Chlorine (Sodium Hypochlorite)', '5 gal', 'pail'),
    ('34% Hydrogen Peroxide', '15 gal', 'drum'),
    ('Chlorine Tablets (Trichlor)', '50 lb', 'bucket');

-- ============================================================
-- SERVICE CONTRACTS
-- ============================================================

CREATE TABLE IF NOT EXISTS `service_contracts` (
    `contract_id` INT AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT NOT NULL,
    `contract_number` VARCHAR(20) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `status` ENUM('draft','active','expired','cancelled') NOT NULL DEFAULT 'draft',
    `start_date` DATE NOT NULL,
    `end_date` DATE DEFAULT NULL,
    `auto_renew` TINYINT(1) NOT NULL DEFAULT 0,
    `renew_term_months` INT DEFAULT 12,
    `frequency` ENUM('monthly','quarterly','semi_annual','annual','custom') NOT NULL DEFAULT 'annual',
    `custom_interval_days` INT DEFAULT NULL,
    `visits_per_cycle` INT NOT NULL DEFAULT 1,
    `billing_cycle` ENUM('monthly','quarterly','semi_annual','annual','per_visit') NOT NULL DEFAULT 'annual',
    `cycle_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `per_visit_price` DECIMAL(10,2) DEFAULT NULL,
    `discount_percent` DECIMAL(5,2) DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `contract_number` (`contract_number`),
    KEY `customer_id` (`customer_id`),
    KEY `idx_contracts_status` (`status`),
    KEY `idx_contracts_end_date` (`end_date`),
    KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `contract_equipment` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `contract_id` INT NOT NULL,
    `equipment_id` INT NOT NULL,
    KEY `contract_id` (`contract_id`),
    KEY `equipment_id` (`equipment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `contract_service_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `contract_id` INT NOT NULL,
    `service_type_id` INT NOT NULL,
    `included_visits` INT NOT NULL DEFAULT 1,
    KEY `contract_id` (`contract_id`),
    KEY `service_type_id` (`service_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `contract_invoice_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `contract_id` INT NOT NULL,
    `invoice_id` INT DEFAULT NULL,
    `cycle_start` DATE NOT NULL,
    `cycle_end` DATE NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `contract_id` (`contract_id`),
    KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `contract_appointment_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `contract_id` INT NOT NULL,
    `appointment_id` INT NOT NULL,
    `scheduled_date` DATE NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `contract_id` (`contract_id`),
    KEY `appointment_id` (`appointment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SETTINGS CATEGORIES (for settings refactor)
-- ============================================================

CREATE TABLE IF NOT EXISTS `setting_categories` (
    `category_id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_key` VARCHAR(50) NOT NULL,
    `category_name` VARCHAR(100) NOT NULL,
    `sort_order` INT DEFAULT 0,
    UNIQUE KEY `category_key` (`category_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed categories
INSERT IGNORE INTO `setting_categories` (`category_key`, `category_name`, `sort_order`) VALUES
    ('company', 'Company Information', 1),
    ('email', 'Email / SMTP', 2),
    ('quickbooks', 'QuickBooks Online', 3),
    ('gmail', 'Gmail OAuth', 4),
    ('push', 'Push Notifications', 5),
    ('tax', 'Tax Rates', 6),
    ('service', 'Service Settings', 7),
    ('iap', 'In-App Purchases', 8);