-- ============================================================
-- MVP BACKOFFICE - FULL DATABASE SCHEMA
-- Consolidated from migrations 001-014
-- Generated: 2026-09-15
-- Usage: mysql -u root water_tracker < 000_full_schema.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `appointment_equipment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `appointment_id` int NOT NULL,
  `equipment_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `appointment_id` (`appointment_id`),
  KEY `equipment_id` (`equipment_id`),
  CONSTRAINT `appointment_equipment_ibfk_1` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`appointment_id`) ON DELETE CASCADE,
  CONSTRAINT `appointment_equipment_ibfk_2` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`equipment_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `appointment_signatures` (
  `signature_id` int unsigned NOT NULL AUTO_INCREMENT,
  `appointment_id` int NOT NULL,
  `customer_id` int NOT NULL,
  `signer_name` varchar(255) NOT NULL,
  `signer_role` enum('customer','technician') NOT NULL DEFAULT 'customer',
  `signature_path` varchar(255) NOT NULL,
  `gps_lat` decimal(10,7) DEFAULT NULL,
  `gps_lng` decimal(10,7) DEFAULT NULL,
  `signed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`signature_id`),
  KEY `idx_appt` (`appointment_id`),
  KEY `idx_cust` (`customer_id`),
  CONSTRAINT `fk_sig_appt` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`appointment_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sig_cust` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `appointment_technicians` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `appointment_id` int NOT NULL,
  `technician_id` int NOT NULL,
  `assigned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `role` enum('lead','technician') NOT NULL DEFAULT 'technician',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_appt_tech` (`appointment_id`,`technician_id`),
  KEY `technician_id` (`technician_id`),
  CONSTRAINT `appointment_technicians_ibfk_1` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`appointment_id`) ON DELETE CASCADE,
  CONSTRAINT `appointment_technicians_ibfk_2` FOREIGN KEY (`technician_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `appointment_travel` (
  `id` int NOT NULL AUTO_INCREMENT,
  `appointment_id` int NOT NULL,
  `prev_appointment_id` int DEFAULT NULL COMMENT 'null = first stop of the day',
  `travel_minutes` int DEFAULT NULL COMMENT 'estimated drive time',
  `distance_miles` decimal(6,2) DEFAULT NULL,
  `stop_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_appt_prev` (`appointment_id`,`prev_appointment_id`),
  KEY `idx_appt` (`appointment_id`),
  KEY `idx_date_tech` (`appointment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `appointments` (
  `appointment_id` int NOT NULL AUTO_INCREMENT,
  `odoo_task_id` int DEFAULT NULL,
  `odoo_event_id` int DEFAULT NULL,
  `customer_id` int NOT NULL,
  `service_address_id` int DEFAULT NULL,
  `service_type_id` int NOT NULL,
  `requested_date` date NOT NULL,
  `requested_window` enum('Morning','Afternoon','Either') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Either',
  `customer_notes` text COLLATE utf8mb4_general_ci,
  `confirmed_date` date DEFAULT NULL,
  `confirmed_time` time DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `on_site_at` datetime DEFAULT NULL,
  `gps_lat` decimal(10,7) DEFAULT NULL,
  `gps_lng` decimal(10,7) DEFAULT NULL,
  `gps_accuracy_m` float DEFAULT NULL,
  `technician_id` int DEFAULT NULL,
  `assigned_by` int DEFAULT NULL,
  `booking_source` enum('customer_app','phone') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'customer_app',
  `status` enum('pending','confirmed','in_progress','completed','cancelled') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
  `office_notes` text COLLATE utf8mb4_general_ci,
  `tech_notes` text COLLATE utf8mb4_general_ci,
  `services_performed` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL,
  `salt_bags` int DEFAULT NULL,
  `salt_delivery` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Customer requested salt delivery with this appointment',
  `oxyblast` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Customer requested Hydrogen Peroxide / OxyBlast service',
  PRIMARY KEY (`appointment_id`),
  KEY `customer_id` (`customer_id`),
  KEY `service_type_id` (`service_type_id`),
  KEY `technician_id` (`technician_id`),
  KEY `assigned_by` (`assigned_by`),
  KEY `idx_appt_odoo_event` (`odoo_event_id`),
  CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE,
  CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`service_type_id`) REFERENCES `service_types` (`type_id`),
  CONSTRAINT `appointments_ibfk_3` FOREIGN KEY (`technician_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `appointments_ibfk_4` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `calendar_config` (
  `id` int NOT NULL AUTO_INCREMENT,
  `provider` enum('google','ical','both') NOT NULL DEFAULT 'ical',
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `google_client_id` varchar(255) DEFAULT NULL,
  `google_client_secret` varchar(255) DEFAULT NULL,
  `google_access_token` text,
  `google_refresh_token` text,
  `google_calendar_id` varchar(255) DEFAULT 'primary',
  `ical_secret` varchar(64) DEFAULT NULL,
  `sync_direction` enum('mvp_to_odoo','odoo_to_mvp','bidirectional') DEFAULT 'mvp_to_odoo',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `company_settings` (
  `setting_key` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `setting_value` text COLLATE utf8mb4_general_ci,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `contract_appointment_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contract_id` int NOT NULL,
  `appointment_id` int NOT NULL,
  `scheduled_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`),
  KEY `appointment_id` (`appointment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `contract_equipment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contract_id` int NOT NULL,
  `equipment_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`),
  KEY `equipment_id` (`equipment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `contract_invoice_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `contract_service_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contract_id` int NOT NULL,
  `service_type_id` int NOT NULL,
  `included_visits` int NOT NULL DEFAULT '1' COMMENT 'How many of this service type per cycle',
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`),
  KEY `service_type_id` (`service_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `customer_device_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `device_token` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `platform` varchar(10) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'ios',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_device_token` (`device_token`),
  KEY `idx_customer_active` (`customer_id`,`is_active`),
  CONSTRAINT `customer_device_tokens_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `customer_images` (
  `image_id` int unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `appointment_id` int DEFAULT NULL,
  `filename` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `caption` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `uploaded_by` int NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`image_id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_appt` (`appointment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `customer_notes` (
  `note_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `equipment_id` int DEFAULT NULL,
  `appointment_id` int DEFAULT NULL,
  `author_id` int NOT NULL,
  `author_role_context` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `note_text` text COLLATE utf8mb4_general_ci NOT NULL,
  `is_visible_to_customer` tinyint(1) NOT NULL DEFAULT '0',
  `is_pinned` tinyint(1) NOT NULL DEFAULT '0',
  `pinned_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`note_id`),
  KEY `customer_id` (`customer_id`),
  KEY `equipment_id` (`equipment_id`),
  KEY `author_id` (`author_id`),
  KEY `idx_notes_appointment` (`appointment_id`),
  CONSTRAINT `customer_notes_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE,
  CONSTRAINT `customer_notes_ibfk_2` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`equipment_id`) ON DELETE SET NULL,
  CONSTRAINT `customer_notes_ibfk_3` FOREIGN KEY (`author_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `customers` (
  `customer_id` int NOT NULL AUTO_INCREMENT,
  `odoo_partner_id` int DEFAULT NULL,
  `user_id` int NOT NULL,
  `first_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `last_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `company_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone2` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email2` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_city` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_state` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_zip` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_city` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_state` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_zip` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `has_separate_billing` tinyint(1) NOT NULL DEFAULT '0',
  `qbo_override_customer_id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `do_not_service` tinyint(1) NOT NULL DEFAULT '0',
  `auto_service_reminder` 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,
  `override_pin` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `override_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `last_login_platform` enum('ios','android','ubuntu_touch') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `location_label` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Short name for this location, e.g. "North Ranch"',
  `location_contact` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'On-site contact name for this location',
  `push_notifications_enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Customer opt-in for browser push notifications',
  `has_separate_service_contact` tinyint(1) NOT NULL DEFAULT '0',
  `service_contact_first_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_contact_last_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_contact_phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_contact_email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `has_separate_billing_contact` tinyint(1) NOT NULL DEFAULT '0',
  `billing_contact_first_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_contact_last_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_contact_company` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_contact_email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_mailing_address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_mailing_city` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_mailing_state` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `billing_mailing_zip` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `include_pictures_on_invoices` tinyint(1) NOT NULL DEFAULT '0',
  `include_notes_on_invoices` tinyint(1) NOT NULL DEFAULT '0',
  `service_lat` decimal(10,7) DEFAULT NULL,
  `service_lng` decimal(10,7) DEFAULT NULL,
  `geo_updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `deliveries` (
  `delivery_id` int NOT NULL AUTO_INCREMENT,
  `delivery_number` varchar(30) DEFAULT NULL,
  `customer_id` int DEFAULT NULL,
  `recipient_name` varchar(120) DEFAULT NULL,
  `delivery_address` varchar(200) DEFAULT NULL,
  `delivery_city` varchar(80) DEFAULT NULL,
  `delivery_state` varchar(10) DEFAULT NULL,
  `delivery_zip` varchar(10) DEFAULT NULL,
  `technician_id` int DEFAULT NULL,
  `status` varchar(20) DEFAULT 'pending',
  `scheduled_date` date DEFAULT NULL,
  `scheduled_window` varchar(40) DEFAULT NULL,
  `signature_path` varchar(255) DEFAULT NULL,
  `gps_lat` decimal(10,7) DEFAULT NULL,
  `gps_lng` decimal(10,7) DEFAULT NULL,
  `gps_accuracy_m` float DEFAULT NULL,
  `gps_captured_at` datetime DEFAULT NULL,
  `notes` text,
  `receipt_sent_at` datetime DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`delivery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `delivery_lines` (
  `line_id` int NOT NULL AUTO_INCREMENT,
  `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) DEFAULT NULL,
  PRIMARY KEY (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `delivery_products` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `container_size` varchar(40) DEFAULT NULL,
  `unit_label` varchar(40) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `email_config` (
  `id` int NOT NULL AUTO_INCREMENT,
  `provider` enum('gmail','protonmail','smtp') NOT NULL DEFAULT 'smtp',
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `smtp_host` varchar(255) DEFAULT NULL,
  `smtp_port` int DEFAULT '587',
  `smtp_user` varchar(255) DEFAULT NULL,
  `smtp_pass` varchar(255) DEFAULT NULL,
  `smtp_encryption` enum('tls','ssl','none') DEFAULT 'tls',
  `imap_host` varchar(255) DEFAULT NULL,
  `imap_port` int DEFAULT '993',
  `gmail_client_id` varchar(255) DEFAULT NULL,
  `gmail_client_secret` varchar(255) DEFAULT NULL,
  `gmail_access_token` text,
  `gmail_refresh_token` text,
  `gmail_token_expiry` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `email_log` (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `invoice_id` int DEFAULT NULL,
  `email_type` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `to_email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `subject` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `appointment_id` int DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`log_id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `idx_email_log_appt_type` (`appointment_id`,`email_type`),
  CONSTRAINT `email_log_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_email_log_appt` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`appointment_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `equipment` (
  `equipment_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `service_address_id` int DEFAULT NULL,
  `type_id` int NOT NULL,
  `model` varchar(150) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `install_date` date DEFAULT NULL,
  `service_interval_days` int DEFAULT NULL,
  `last_service_date` date DEFAULT NULL,
  `last_filter_date` date DEFAULT NULL COMMENT 'RO only: date filters were last replaced',
  `last_membrane_date` date DEFAULT NULL COMMENT 'RO only: date membrane was last replaced',
  `next_service_due` date DEFAULT NULL,
  `assigned_technician` int DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `notes` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `self_service` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = customer services this themselves, exclude from due tracking',
  `part_id` int DEFAULT NULL COMMENT 'parts_catalog.part_id override for this specific equipment item; NULL = use equipment_types.default_part_id',
  PRIMARY KEY (`equipment_id`),
  KEY `customer_id` (`customer_id`),
  KEY `type_id` (`type_id`),
  KEY `assigned_technician` (`assigned_technician`),
  KEY `idx_equipment_part` (`part_id`),
  CONSTRAINT `equipment_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE,
  CONSTRAINT `equipment_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `equipment_types` (`type_id`),
  CONSTRAINT `equipment_ibfk_3` FOREIGN KEY (`assigned_technician`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `equipment_types` (
  `type_id` int NOT NULL AUTO_INCREMENT,
  `type_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `default_interval_days` int DEFAULT NULL,
  `category` enum('water','air') COLLATE utf8mb4_general_ci NOT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_tracked` tinyint(1) NOT NULL DEFAULT '1',
  `show_to_customer` tinyint(1) NOT NULL DEFAULT '1',
  `no_service_schedule` tinyint(1) NOT NULL DEFAULT '0',
  `default_part_id` int DEFAULT NULL COMMENT 'parts_catalog.part_id used as default invoice line item for this equipment type',
  PRIMARY KEY (`type_id`),
  KEY `idx_equipment_types_default_part` (`default_part_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `estimate_counter` (
  `id` int NOT NULL AUTO_INCREMENT,
  `year` int NOT NULL,
  `sequence` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `year_seq` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `estimate_lines` (
  `line_id` int NOT NULL AUTO_INCREMENT,
  `estimate_id` int NOT NULL,
  `part_id` int DEFAULT NULL,
  `line_type` enum('labor','service_call','parts','filter','equipment','salt','warranty','discount','custom') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'custom',
  `line_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `quantity` decimal(8,2) NOT NULL DEFAULT '1.00',
  `unit_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_taxable` tinyint(1) NOT NULL DEFAULT '0',
  `h2o2_prorate` decimal(5,2) DEFAULT NULL,
  `discount_note` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `line_total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`line_id`),
  KEY `estimate_id` (`estimate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `estimates` (
  `estimate_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `appointment_id` int DEFAULT NULL COMMENT 'Linked appointment if estimate came from a service call',
  `contract_id` int DEFAULT NULL COMMENT 'Linked contract if estimate is for contract-related work',
  `estimate_number` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `status` enum('draft','sent','approved','rejected','expired','converted') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',
  `issue_date` date NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `subtotal` decimal(10,2) NOT NULL DEFAULT '0.00',
  `taxable_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tax_rate` decimal(5,4) NOT NULL DEFAULT '0.0000',
  `tax_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `notes` text COLLATE utf8mb4_general_ci,
  `customer_response_notes` text COLLATE utf8mb4_general_ci COMMENT 'Customer notes when approving/rejecting',
  `created_by` int NOT NULL,
  `converted_invoice_id` int DEFAULT NULL COMMENT 'Invoice created when estimate was approved/converted',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`estimate_id`),
  UNIQUE KEY `estimate_number` (`estimate_number`),
  KEY `customer_id` (`customer_id`),
  KEY `appointment_id` (`appointment_id`),
  KEY `contract_id` (`contract_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_estimates_status` (`status`),
  KEY `idx_estimates_expiry` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `invoice_counter` (
  `id` int NOT NULL AUTO_INCREMENT,
  `year` int NOT NULL,
  `sequence` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `year_seq` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `invoice_lines` (
  `line_id` int NOT NULL AUTO_INCREMENT,
  `line_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `invoice_id` int NOT NULL,
  `part_id` int DEFAULT NULL,
  `line_type` enum('labor','service_call','parts','filter','equipment','salt','warranty','discount','custom') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'custom',
  `description` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `quantity` decimal(8,2) NOT NULL DEFAULT '1.00',
  `unit_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_taxable` tinyint(1) NOT NULL DEFAULT '0',
  `h2o2_prorate` decimal(5,2) DEFAULT NULL,
  `discount_note` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `line_total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`line_id`),
  KEY `invoice_id` (`invoice_id`),
  CONSTRAINT `invoice_lines_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `invoices` (
  `invoice_id` int NOT NULL AUTO_INCREMENT,
  `odoo_move_id` int DEFAULT NULL,
  `odoo_amount_total` decimal(12,2) DEFAULT NULL,
  `odoo_amount_residual` decimal(12,2) DEFAULT NULL,
  `customer_id` int NOT NULL,
  `appointment_id` int DEFAULT NULL,
  `invoice_number` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `status` enum('draft','sent','paid','void') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',
  `issue_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `subtotal` decimal(10,2) NOT NULL DEFAULT '0.00',
  `taxable_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tax_rate` decimal(5,4) NOT NULL DEFAULT '0.0000',
  `tax_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `notes` text COLLATE utf8mb4_general_ci,
  `created_by` int NOT NULL,
  `qbo_id` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `card_fee_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `card_fee_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `card_fee_source` enum('manual','customer_pending') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `card_fee_pending_since` datetime DEFAULT NULL,
  `qbo_sync_status` enum('pending','synced','error','skipped') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `qbo_synced_at` timestamp NULL DEFAULT NULL,
  `qbo_sync_error` text COLLATE utf8mb4_general_ci,
  `auto_created` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`invoice_id`),
  UNIQUE KEY `invoice_number` (`invoice_number`),
  KEY `customer_id` (`customer_id`),
  KEY `appointment_id` (`appointment_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_invoices_qbo_sync` (`qbo_sync_status`),
  CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
  CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`appointment_id`) ON DELETE SET NULL,
  CONSTRAINT `invoices_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `iot_alert_log` (
  `alert_id` int NOT NULL AUTO_INCREMENT,
  `device_id` int NOT NULL,
  `rule_id` int NOT NULL,
  `customer_id` int NOT NULL,
  `metric_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `triggered_value` decimal(12,4) NOT NULL,
  `threshold_value` decimal(12,4) NOT NULL,
  `condition` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `message` text COLLATE utf8mb4_general_ci,
  `triggered_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `acknowledged_at` timestamp NULL DEFAULT NULL,
  `acknowledged_by` int DEFAULT NULL,
  `notification_sent` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`alert_id`),
  KEY `device_id` (`device_id`),
  KEY `rule_id` (`rule_id`),
  KEY `customer_id` (`customer_id`),
  KEY `idx_alerts_unacked` (`acknowledged_at`,`triggered_at`),
  KEY `idx_alerts_triggered_at` (`triggered_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `iot_alert_rules` (
  `rule_id` int NOT NULL AUTO_INCREMENT,
  `device_id` int NOT NULL,
  `metric_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `rule_name` varchar(150) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'e.g. "Low Salt Warning"',
  `condition` enum('below','above','equals','below_or_equals','above_or_equals') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'below',
  `threshold_value` decimal(12,4) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `notification_channels` json DEFAULT NULL COMMENT '["push","email"] - where to send alerts',
  `cooldown_minutes` int NOT NULL DEFAULT '60' COMMENT 'Minimum time between repeated alerts for same rule',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rule_id`),
  KEY `device_id` (`device_id`),
  KEY `idx_rules_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `iot_device_types` (
  `type_id` int NOT NULL AUTO_INCREMENT,
  `type_slug` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Machine-readable identifier, e.g. salt_monitor, tds_sensor',
  `type_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `metrics` json DEFAULT NULL COMMENT 'Array of metric objects: [{name, unit, min, max, alert_threshold_low, alert_threshold_high}]',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`type_id`),
  UNIQUE KEY `type_slug` (`type_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `iot_devices` (
  `device_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `equipment_id` int DEFAULT NULL COMMENT 'Linked equipment this sensor monitors',
  `type_id` int NOT NULL,
  `device_name` varchar(150) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Human-readable name, e.g. "Kitchen Softener Salt Monitor"',
  `device_key` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'API key for device authentication',
  `mac_address` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `firmware_version` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('active','inactive','offline','error') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'active',
  `last_seen` datetime DEFAULT NULL COMMENT 'Timestamp of last telemetry upload',
  `last_reading_summary` json DEFAULT NULL COMMENT 'Cached last reading values for quick dashboard display',
  `notes` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`device_id`),
  UNIQUE KEY `device_key` (`device_key`),
  KEY `customer_id` (`customer_id`),
  KEY `equipment_id` (`equipment_id`),
  KEY `type_id` (`type_id`),
  KEY `idx_devices_status` (`status`),
  KEY `idx_devices_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `iot_readings` (
  `reading_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `device_id` int NOT NULL,
  `customer_id` int NOT NULL,
  `metric_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'e.g. salt_level, tds, ph, pressure, flow_rate',
  `value` decimal(12,4) NOT NULL,
  `unit` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'e.g. percent, ppm, psi, gpm',
  `recorded_at` datetime NOT NULL COMMENT 'When the sensor took the reading',
  `server_received_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the server received the upload',
  `metadata` json DEFAULT NULL COMMENT 'Optional device-specific metadata (battery %, signal strength, etc.)',
  PRIMARY KEY (`reading_id`),
  KEY `device_id` (`device_id`),
  KEY `customer_id` (`customer_id`),
  KEY `idx_readings_metric_time` (`metric_name`,`recorded_at`),
  KEY `idx_readings_recorded_at` (`recorded_at`),
  KEY `idx_readings_device_time` (`device_id`,`recorded_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `lead_requests` (
  `lead_id` int NOT NULL AUTO_INCREMENT,
  `service_type` varchar(100) DEFAULT NULL,
  `first_name` varchar(80) DEFAULT NULL,
  `last_name` varchar(80) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(160) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `city` varchar(80) DEFAULT NULL,
  `state` varchar(10) DEFAULT NULL,
  `preferred_date` date DEFAULT NULL,
  `preferred_window` varchar(40) DEFAULT NULL,
  `referral` varchar(80) DEFAULT NULL,
  `notes` text,
  `submitted_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `converted` tinyint(1) DEFAULT '0',
  `status` varchar(20) DEFAULT 'new',
  `assigned_to` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`lead_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `messages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `appointment_id` int DEFAULT NULL,
  `direction` enum('inbound','outbound') NOT NULL,
  `channel` enum('app','email','sms') NOT NULL DEFAULT 'app',
  `subject` varchar(255) DEFAULT NULL,
  `body` text NOT NULL,
  `status` enum('sent','delivered','read','failed') NOT NULL DEFAULT 'sent',
  `sent_by` int DEFAULT NULL COMMENT 'user_id of sender (null = customer)',
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_appointment` (`appointment_id`),
  KEY `idx_created` (`created_at`),
  CONSTRAINT `fk_msg_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `notification_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `device_token` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `notification_type` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `body` text COLLATE utf8mb4_general_ci,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `status` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `response_code` int DEFAULT NULL,
  `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer_sent` (`customer_id`,`sent_at`),
  KEY `idx_type_sent` (`notification_type`,`sent_at`),
  CONSTRAINT `notification_logs_chk_1` CHECK (json_valid(`payload`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `odoo_sync_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `run_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `direction` enum('push','pull','both') NOT NULL DEFAULT 'both',
  `customers_pushed` int DEFAULT '0',
  `customers_pulled` int DEFAULT '0',
  `parts_pushed` int DEFAULT '0',
  `parts_pulled` int DEFAULT '0',
  `invoices_pushed` int DEFAULT '0',
  `invoices_pulled` int DEFAULT '0',
  `appointments_pushed` int DEFAULT '0',
  `payments_pulled` int DEFAULT '0',
  `errors` int DEFAULT '0',
  `duration_ms` int DEFAULT '0',
  `notes` text,
  PRIMARY KEY (`id`),
  KEY `idx_run_at` (`run_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `on_my_way_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `appointment_id` int NOT NULL,
  `technician_id` int NOT NULL,
  `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_appt_tech` (`appointment_id`,`technician_id`),
  KEY `technician_id` (`technician_id`),
  CONSTRAINT `on_my_way_log_ibfk_1` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`appointment_id`) ON DELETE CASCADE,
  CONSTRAINT `on_my_way_log_ibfk_2` FOREIGN KEY (`technician_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `part_categories` (
  `category_id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `part_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `part_categories` (`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `parts_catalog` (
  `part_id` int NOT NULL AUTO_INCREMENT,
  `odoo_product_id` int DEFAULT NULL,
  `category_id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `brand` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sku` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `barcode` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_description` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `tech_description` text COLLATE utf8mb4_general_ci,
  `unit` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'each',
  `cost_price` decimal(10,2) DEFAULT NULL,
  `sell_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_h2o2` tinyint(1) NOT NULL DEFAULT '0',
  `is_taxable` tinyint(1) NOT NULL DEFAULT '1',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int 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 (`part_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `parts_catalog_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `part_categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `payment_processors` (
  `id` int NOT NULL AUTO_INCREMENT,
  `processor` enum('qbo','stripe') NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `qbo_access_token` text,
  `qbo_refresh_token` text,
  `qbo_token_expiry` datetime DEFAULT NULL,
  `stripe_publishable_key` varchar(255) DEFAULT NULL,
  `stripe_secret_key` varchar(255) DEFAULT NULL,
  `stripe_webhook_secret` varchar(255) DEFAULT NULL,
  `stripe_account_id` varchar(255) DEFAULT NULL,
  `test_mode` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_processor` (`processor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `payments` (
  `payment_id` int NOT NULL AUTO_INCREMENT,
  `odoo_payment_id` int DEFAULT NULL,
  `invoice_id` int NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_method` enum('cash','check','card_office','card_field','card_online','warranty','gift_certificate','other') COLLATE utf8mb4_general_ci NOT NULL,
  `check_number` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `payment_notes` text COLLATE utf8mb4_general_ci,
  `payment_date` date NOT NULL,
  `recorded_by` int NOT NULL,
  `deposit_account_id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `qbo_id` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `qbo_payment_id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `qbo_sync_status` enum('pending','synced','error','skipped') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `qbo_synced_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`payment_id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `recorded_by` (`recorded_by`),
  KEY `idx_payments_qbo_sync` (`qbo_sync_status`),
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`),
  CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `pending_notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `body` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `read_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `idx_unread` (`customer_id`,`read_at`),
  CONSTRAINT `pending_notifications_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `push_log` (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int DEFAULT NULL,
  `appointment_id` int DEFAULT NULL,
  `invoice_id` int DEFAULT NULL,
  `event_type` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `body` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sent_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `success_count` int NOT NULL DEFAULT '0',
  `failure_count` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`log_id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `push_subscriptions` (
  `subscription_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `endpoint` varchar(500) COLLATE utf8mb4_general_ci NOT NULL,
  `p256dh_key` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `auth_key` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `user_agent` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` datetime DEFAULT NULL,
  PRIMARY KEY (`subscription_id`),
  UNIQUE KEY `uniq_endpoint` (`endpoint`),
  KEY `idx_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `qbo_customers` (
  `customer_id` int NOT NULL,
  `qbo_customer_id` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `qbo_display_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `synced_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  CONSTRAINT `qbo_customers_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `qbo_items` (
  `item_key` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `qbo_item_id` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `item_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `synced_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`item_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `refresh_tokens` (
  `token_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `token_hash` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`token_id`),
  UNIQUE KEY `token_hash` (`token_hash`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `refresh_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `service_addresses` (
  `service_address_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `label` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'e.g. Main Ranch, North Pasture, Barn',
  `service_address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_city` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_state` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `service_zip` varchar(20) COLLATE utf8mb4_general_ci 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,
  `lat` decimal(10,7) DEFAULT NULL,
  `lng` decimal(10,7) DEFAULT NULL,
  `geo_updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`service_address_id`),
  KEY `idx_service_addresses_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `service_call_types` (
  `sc_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(150) COLLATE utf8mb4_general_ci NOT NULL,
  `description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_taxable` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`sc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `service_contracts` (
  `contract_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `contract_number` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Descriptive name, e.g. "Annual Water Softener Maintenance"',
  `status` enum('draft','active','expired','cancelled') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL COMMENT 'NULL = ongoing / auto-renewing',
  `auto_renew` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = automatically renew for another term when end_date is reached',
  `renew_term_months` int DEFAULT '12' COMMENT 'How many months to add on auto-renew',
  `frequency` enum('monthly','quarterly','semi_annual','annual','custom') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'annual',
  `custom_interval_days` int DEFAULT NULL COMMENT 'Used when frequency = custom',
  `visits_per_cycle` int NOT NULL DEFAULT '1' COMMENT 'How many service visits per billing cycle',
  `billing_cycle` enum('monthly','quarterly','semi_annual','annual','per_visit') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'annual',
  `cycle_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Price per billing cycle',
  `per_visit_price` decimal(10,2) DEFAULT NULL COMMENT 'Override price per individual visit (if billing = per_visit)',
  `discount_percent` decimal(5,2) DEFAULT NULL COMMENT 'Discount % applied to standard service rates',
  `notes` text COLLATE utf8mb4_general_ci,
  `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,
  PRIMARY KEY (`contract_id`),
  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 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `service_records` (
  `record_id` int NOT NULL AUTO_INCREMENT,
  `equipment_id` int NOT NULL,
  `technician_id` int NOT NULL,
  `service_date` date NOT NULL,
  `service_type` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `next_service_due` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `filter_replaced` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'RO only: filters were replaced on this visit',
  `membrane_replaced` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'RO only: membrane was replaced on this visit',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `logged_by` enum('technician','customer') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'technician',
  `service_type_label` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `materials_used` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`record_id`),
  KEY `equipment_id` (`equipment_id`),
  KEY `technician_id` (`technician_id`),
  CONSTRAINT `service_records_ibfk_1` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`equipment_id`) ON DELETE CASCADE,
  CONSTRAINT `service_records_ibfk_2` FOREIGN KEY (`technician_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `service_types` (
  `type_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `min_days_out` int NOT NULL DEFAULT '3',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `default_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `price_overridable` tinyint(1) NOT NULL DEFAULT '1',
  `is_salt_delivery` tinyint(1) NOT NULL DEFAULT '0',
  `min_bags_required` int NOT NULL DEFAULT '0',
  `extended_reminders` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = send 7/3/1-day reminder cadence in addition to the 24h reminder',
  `customer_requestable` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0 = office/tech only, do not show in customer booking form',
  `skip_auto_invoice_lines` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = autoCreateInvoiceForAppointment creates draft shell but skips autoPopulateLines',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `setting_categories` (
  `category_id` int NOT NULL AUTO_INCREMENT,
  `category_key` varchar(50) NOT NULL,
  `category_name` varchar(100) NOT NULL,
  `sort_order` int DEFAULT '0',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `category_key` (`category_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `tax_rates` (
  `rate_id` int NOT NULL AUTO_INCREMENT,
  `city` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `state` varchar(2) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'IL',
  `rate` decimal(5,4) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rate_id`),
  UNIQUE KEY `city_state` (`city`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `tech_availability` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `day_of_week` tinyint NOT NULL COMMENT '0=Sun,1=Mon,...,6=Sat',
  `start_time` time NOT NULL DEFAULT '09:00:00',
  `end_time` time NOT NULL DEFAULT '17:00:00',
  `max_appts` int NOT NULL DEFAULT '8' COMMENT 'max appointments per day',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_tech_day` (`user_id`,`day_of_week`),
  KEY `idx_tech` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `tech_blocked_dates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `block_date` date NOT NULL,
  `reason` varchar(100) DEFAULT NULL COMMENT 'pto, holiday, training, etc.',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_tech_date` (`user_id`,`block_date`),
  KEY `idx_tech` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `tech_clock_time` (
  `entry_id` int NOT NULL AUTO_INCREMENT,
  `technician_id` int NOT NULL,
  `clocked_in_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `clocked_out_at` datetime DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `force_closed` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entry_id`),
  KEY `idx_tech_open` (`technician_id`,`clocked_out_at`,`force_closed`),
  KEY `idx_tech_date` (`technician_id`,`clocked_in_at`),
  CONSTRAINT `fk_clt_tech` FOREIGN KEY (`technician_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tech_clock_time_audit` (
  `audit_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `entry_id` int NOT NULL,
  `technician_id` int NOT NULL,
  `action` enum('create','update','delete') COLLATE utf8mb4_general_ci NOT NULL,
  `changed_by` int NOT NULL,
  `old_clocked_in_at` datetime DEFAULT NULL,
  `old_clocked_out_at` datetime DEFAULT NULL,
  `old_notes` text COLLATE utf8mb4_general_ci,
  `new_clocked_in_at` datetime DEFAULT NULL,
  `new_clocked_out_at` datetime DEFAULT NULL,
  `new_notes` text COLLATE utf8mb4_general_ci,
  `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`audit_id`),
  KEY `idx_entry` (`entry_id`),
  KEY `idx_technician` (`technician_id`),
  KEY `idx_changed_at` (`changed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `tech_time_entries` (
  `entry_id` int NOT NULL AUTO_INCREMENT,
  `technician_id` int NOT NULL,
  `clock_in` datetime NOT NULL,
  `clock_out` datetime DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `appointment_id` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entry_id`),
  KEY `appointment_id` (`appointment_id`),
  KEY `idx_tech_open` (`technician_id`,`clock_out`),
  CONSTRAINT `tech_time_entries_ibfk_1` FOREIGN KEY (`technician_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `tech_time_entries_ibfk_2` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`appointment_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `time_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `event_type` enum('CLOCK_IN','CLOCK_OUT') COLLATE utf8mb4_general_ci NOT NULL,
  `event_timestamp` datetime NOT NULL,
  `server_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `session_id` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_event` (`user_id`,`event_timestamp`),
  KEY `idx_user_session` (`user_id`,`session_id`),
  CONSTRAINT `time_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `first_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `last_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `role` enum('customer','technician','admin') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'customer',
  `is_field_tech` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` timestamp NULL DEFAULT NULL,
  `device_token` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `water_tests` (
  `test_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `label` varchar(150) COLLATE utf8mb4_general_ci NOT NULL,
  `test_date` date NOT NULL,
  `filename` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `uploaded_by` int NOT NULL,
  `is_current` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`test_id`),
  KEY `customer_id` (`customer_id`),
  KEY `uploaded_by` (`uploaded_by`),
  CONSTRAINT `water_tests_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE,
  CONSTRAINT `water_tests_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- SEED DATA: Service Types
-- ============================================================
INSERT IGNORE INTO `service_types` (`type_id`, `name`, `min_days_out`, `is_active`, `default_price`, `price_overridable`, `is_salt_delivery`, `min_bags_required`, `extended_reminders`, `customer_requestable`, `skip_auto_invoice_lines`) VALUES
(11, 'Routine Service',         3, 0,  0.00, 1, 0, 0, 0, 1, 0),
(12, 'Salt Delivery',           4, 0,  0.00, 1, 1, 0, 0, 1, 0),
(13, 'Service Call',            1, 0, 77.00, 1, 0, 0, 0, 1, 0),
(14, 'Water Test',              1, 0,  0.00, 1, 0, 0, 0, 1, 0),
(15, 'Sales',                   1, 1,  0.00, 1, 0, 0, 0, 1, 0),
(16, 'Service',                 3, 1,  0.00, 1, 0, 0, 0, 1, 0),
(17, 'Testing',                 1, 1,  0.00, 1, 0, 0, 0, 1, 0),
(18, 'Equipment Delivery',      1, 1,  0.00, 1, 0, 0, 0, 1, 0),
(19, 'Salt Delivery',           4, 1,  0.00, 1, 0, 0, 0, 1, 0),
(20, 'Installation',            3, 1,  0.00, 1, 0, 0, 0, 1, 0),
(21, 'Diagnostic',              1, 1, 77.00, 1, 0, 0, 0, 1, 0);

-- ============================================================
-- SEED DATA: Part Categories
-- ============================================================
INSERT IGNORE INTO `part_categories` (`category_id`, `parent_id`, `name`, `sort_order`) VALUES
(1,  NULL, 'Water Treatment',   1),
(2,  NULL, 'Air Treatment',     2),
(3,  NULL, 'General',           3),
(4,   1,   'Softener Parts',   10),
(5,   1,   'RO Parts',         20),
(6,   1,   'UV Parts',         30),
(7,   1,   'Salt Products',    40),
(8,   2,   'HEPA Filters',     10),
(9,   2,   'UV Bulbs',         20),
(10,  2,   'Carbon Filters',   30);

-- ============================================================
-- SEED DATA: Setting Categories
-- ============================================================
INSERT IGNORE INTO `setting_categories` (`category_id`, `category_key`, `category_name`, `sort_order`) VALUES
(1, 'company',    'Company Information', 1),
(2, 'email',      'Email Configuration', 2),
(3, 'sms',        'SMS Configuration',   3),
(4, 'accounting', 'Accounting',          4),
(5, 'automation', 'Automation',          5),
(6, 'iot',        'IoT Devices',         6),
(7, 'service',    'Service Settings',    7);

SET FOREIGN_KEY_CHECKS = 1;