-- MVP Backoffice schema
-- Complete schema: all tables, indexes, foreign keys, and views.
-- Import once into an empty database. No migrations are needed after this.
-- (The files in migrations/ are only for upgrading databases created
--  before this schema was consolidated.)
-- No login accounts are seeded; see the seed section at the end.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE `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;
;

--
-- Table structure for table `appointment_signatures`
--

CREATE TABLE `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;
;

--
-- Table structure for table `appointment_technicians`
--

CREATE TABLE `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;
;

--
-- Table structure for table `appointment_travel`
--

CREATE TABLE `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;
;

--
-- Table structure for table `appointments`
--

CREATE TABLE `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;
;

--
-- Table structure for table `calendar_config`
--

CREATE TABLE `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;
;

--
-- Table structure for table `company_settings`
--

CREATE TABLE `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;
;

--
-- Table structure for table `contract_appointment_log`
--

CREATE TABLE `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;
;

--
-- Table structure for table `contract_equipment`
--

CREATE TABLE `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;
;

--
-- Table structure for table `contract_invoice_log`
--

CREATE TABLE `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;
;

--
-- Table structure for table `contract_service_types`
--

CREATE TABLE `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;
;

--
-- Table structure for table `customer_device_tokens`
--

CREATE TABLE `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;
;

--
-- Table structure for table `customer_images`
--

CREATE TABLE `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;
;

--
-- Table structure for table `customer_notes`
--

CREATE TABLE `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;
;

--
-- Table structure for table `customers`
--

CREATE TABLE `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;
;

--
-- Table structure for table `deliveries`
--

CREATE TABLE `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;
;

--
-- Table structure for table `delivery_lines`
--

CREATE TABLE `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;
;

--
-- Table structure for table `delivery_products`
--

CREATE TABLE `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;
;

--
-- Table structure for table `email_config`
--

CREATE TABLE `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;
;

--
-- Table structure for table `email_log`
--

CREATE TABLE `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;
;

--
-- Table structure for table `equipment`
--

CREATE TABLE `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_filter_due` date DEFAULT NULL COMMENT 'RO only: date filters next due',
  `next_membrane_due` date DEFAULT NULL COMMENT 'RO only: date membrane next due',
  `filter_interval_days` int DEFAULT NULL COMMENT 'RO only: override default filter interval (days)',
  `membrane_interval_days` int DEFAULT NULL COMMENT 'RO only: override default membrane interval (days)',
  `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;
;

--
-- Table structure for table `equipment_types`
--

CREATE TABLE `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,
  `filter_interval_days` int DEFAULT NULL COMMENT 'RO only: default filter replacement interval (days)',
  `membrane_interval_days` int DEFAULT NULL COMMENT 'RO only: default membrane replacement interval (days)',
  `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;
;

--
-- Table structure for table `estimate_counter`
--

CREATE TABLE `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;
;

--
-- Table structure for table `estimate_lines`
--

CREATE TABLE `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;
;

--
-- Table structure for table `estimates`
--

CREATE TABLE `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;
;

--
-- Table structure for table `invoice_counter`
--

CREATE TABLE `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;
;

--
-- Table structure for table `invoice_lines`
--

CREATE TABLE `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;
;

--
-- Table structure for table `invoices`
--

CREATE TABLE `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;
;

--
-- Table structure for table `iot_alert_log`
--

CREATE TABLE `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;
;

--
-- Table structure for table `iot_alert_rules`
--

CREATE TABLE `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;
;

--
-- Table structure for table `iot_device_types`
--

CREATE TABLE `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;
;

--
-- Table structure for table `iot_devices`
--

CREATE TABLE `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;
;

--
-- Table structure for table `iot_readings`
--

CREATE TABLE `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;
;

--
-- Table structure for table `lead_requests`
--

CREATE TABLE `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;
;

--
-- Table structure for table `messages`
--

CREATE TABLE `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;
;

--
-- Table structure for table `notification_logs`
--

CREATE TABLE `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;
;

--
-- Table structure for table `odoo_sync_log`
--

CREATE TABLE `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;
;

--
-- Table structure for table `on_my_way_log`
--

CREATE TABLE `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;
;

--
-- Table structure for table `part_categories`
--

CREATE TABLE `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;
;

--
-- Table structure for table `parts_catalog`
--

CREATE TABLE `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;
;

--
-- Table structure for table `payment_processors`
--

CREATE TABLE `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;
;

--
-- Table structure for table `payments`
--

CREATE TABLE `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;
;

--
-- Table structure for table `pending_notifications`
--

CREATE TABLE `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;
;

--
-- Table structure for table `push_log`
--

CREATE TABLE `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;
;

--
-- Table structure for table `push_subscriptions`
--

CREATE TABLE `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;
;

--
-- Table structure for table `qbo_customers`
--

CREATE TABLE `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;
;

--
-- Table structure for table `qbo_items`
--

CREATE TABLE `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;
;

--
-- Table structure for table `refresh_tokens`
--

CREATE TABLE `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;
;

--
-- Table structure for table `salt_tracker`
--

CREATE TABLE `salt_tracker` (
  `customer_id` int NOT NULL,
  `interval_days` int NOT NULL DEFAULT '90' COMMENT 'Reminder cadence: 30, 90, or 180 days',
  `last_checked_date` date DEFAULT NULL,
  `next_due_date` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  CONSTRAINT `fk_salt_tracker_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
;

--
-- Table structure for table `service_addresses`
--

CREATE TABLE `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;
;

--
-- Table structure for table `service_call_types`
--

CREATE TABLE `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;
;

--
-- Table structure for table `service_contracts`
--

CREATE TABLE `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;
;

--
-- Table structure for table `service_records`
--

CREATE TABLE `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;
;

--
-- Table structure for table `service_types`
--

CREATE TABLE `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;
;

--
-- Table structure for table `setting_categories`
--

CREATE TABLE `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;
;

--
-- Table structure for table `tax_rates`
--

CREATE TABLE `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;
;

--
-- Table structure for table `tech_availability`
--

CREATE TABLE `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;
;

--
-- Table structure for table `tech_blocked_dates`
--

CREATE TABLE `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;
;

--
-- Table structure for table `tech_clock_time`
--

CREATE TABLE `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;
;

--
-- Table structure for table `tech_clock_time_audit`
--

CREATE TABLE `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;
;

--
-- Table structure for table `tech_time_entries`
--

CREATE TABLE `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;
;

--
-- Table structure for table `time_logs`
--

CREATE TABLE `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;
;

--
-- Table structure for table `users`
--

CREATE TABLE `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;
;

--
-- Temporary view structure for view `v_equipment_overview`
--

/*!50001 CREATE VIEW `v_equipment_overview` AS SELECT 
 1 AS `equipment_id`,
 1 AS `customer_id`,
 1 AS `customer_name`,
 1 AS `phone`,
 1 AS `service_address`,
 1 AS `service_city`,
 1 AS `service_state`,
 1 AS `service_zip`,
 1 AS `type_name`,
 1 AS `category`,
 1 AS `model`,
 1 AS `install_date`,
 1 AS `effective_interval_days`,
 1 AS `last_service_date`,
 1 AS `next_service_due`,
 1 AS `days_until_due`,
 1 AS `technician_email`*/;

--
-- Temporary view structure for view `v_service_due`
--

/*!50001 CREATE VIEW `v_service_due` AS SELECT 
 1 AS `equipment_id`,
 1 AS `customer_id`,
 1 AS `customer_name`,
 1 AS `phone`,
 1 AS `service_address`,
 1 AS `service_city`,
 1 AS `service_state`,
 1 AS `service_zip`,
 1 AS `type_name`,
 1 AS `category`,
 1 AS `model`,
 1 AS `install_date`,
 1 AS `effective_interval_days`,
 1 AS `last_service_date`,
 1 AS `next_service_due`,
 1 AS `days_until_due`,
 1 AS `technician_email`*/;

--
-- Table structure for table `water_tests`
--

CREATE TABLE `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;
;

--
-- Final view structure for view `v_equipment_overview`
--

/*!50001 DROP VIEW IF EXISTS `v_equipment_overview`*/;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */

/*!50001 VIEW `v_equipment_overview` AS select `e`.`equipment_id` AS `equipment_id`,`e`.`customer_id` AS `customer_id`,concat(`c`.`first_name`,' ',`c`.`last_name`) AS `customer_name`,`c`.`phone` AS `phone`,`c`.`service_address` AS `service_address`,`c`.`service_city` AS `service_city`,`c`.`service_state` AS `service_state`,`c`.`service_zip` AS `service_zip`,`et`.`type_name` AS `type_name`,`et`.`category` AS `category`,`e`.`model` AS `model`,`e`.`install_date` AS `install_date`,coalesce(`e`.`service_interval_days`,`et`.`default_interval_days`) AS `effective_interval_days`,`e`.`last_service_date` AS `last_service_date`,`e`.`next_service_due` AS `next_service_due`,(to_days(`e`.`next_service_due`) - to_days(curdate())) AS `days_until_due`,concat(`u`.`email`) AS `technician_email` from (((`equipment` `e` join `customers` `c` on((`e`.`customer_id` = `c`.`customer_id`))) join `equipment_types` `et` on((`e`.`type_id` = `et`.`type_id`))) left join `users` `u` on((`e`.`assigned_technician` = `u`.`user_id`))) where (`e`.`is_active` = 1) */;

--
-- Final view structure for view `v_service_due`
--

/*!50001 DROP VIEW IF EXISTS `v_service_due`*/;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */

/*!50001 VIEW `v_service_due` AS select `v_equipment_overview`.`equipment_id` AS `equipment_id`,`v_equipment_overview`.`customer_id` AS `customer_id`,`v_equipment_overview`.`customer_name` AS `customer_name`,`v_equipment_overview`.`phone` AS `phone`,`v_equipment_overview`.`service_address` AS `service_address`,`v_equipment_overview`.`service_city` AS `service_city`,`v_equipment_overview`.`service_state` AS `service_state`,`v_equipment_overview`.`service_zip` AS `service_zip`,`v_equipment_overview`.`type_name` AS `type_name`,`v_equipment_overview`.`category` AS `category`,`v_equipment_overview`.`model` AS `model`,`v_equipment_overview`.`install_date` AS `install_date`,`v_equipment_overview`.`effective_interval_days` AS `effective_interval_days`,`v_equipment_overview`.`last_service_date` AS `last_service_date`,`v_equipment_overview`.`next_service_due` AS `next_service_due`,`v_equipment_overview`.`days_until_due` AS `days_until_due`,`v_equipment_overview`.`technician_email` AS `technician_email` from `v_equipment_overview` where (`v_equipment_overview`.`next_service_due` <= (curdate() + interval 30 day)) order by `v_equipment_overview`.`next_service_due` */;

SET FOREIGN_KEY_CHECKS=1;

-- Seed data ----------------------------------------------------------------
-- No login accounts are seeded. After importing, create the first admin
-- directly in MySQL. Generate the hash with PHP first:
--   php -r "echo password_hash('choose-a-strong-password', PASSWORD_BCRYPT), PHP_EOL;"
-- then:
--   INSERT INTO `users` (`email`, `password_hash`, `role`, `is_active`)
--   VALUES ('admin@example.com', '<paste-hash-here>', 'admin', 1);

INSERT INTO `equipment_types` (`type_name`, `default_interval_days`, `category`) VALUES
  ('Reverse Osmosis / Drinking Water', 365, 'water'),
  ('Whole-House Sediment Prefilter',   180, 'water'),
  ('Hydrogen Peroxide System',         180, 'water'),
  ('Water Softener',                   365, 'water'),
  ('Pump Tube',                        365, 'water'),
  ('UV Sterilizer',                    365, 'water'),
  ('Air Purifier / UV System',         365, 'air'),
  ('HVAC Filter',                       90, 'air'),
  ('Room Air Filter',                   90, 'air');

INSERT INTO `company_settings` (`setting_key`, `setting_value`) VALUES
('company_name',     'Catalytic Water Solutions'),
('company_phone',    '(309) 701-4807'),
('company_email',    'office@catalyticwater.com'),
('company_website',  'https://catalyticwater.com/'),
('company_address',  '961 County Road 2000E'),
('company_city',     'Secor'),
('company_state',    'IL'),
('company_zip',      '61771'),
('customer_app_url', 'https://catalyticwsmvp.myusa.cloud/customer.html'),
('default_tax_rate', '0.0625');

INSERT INTO `invoice_counter` (`id`, `year`, `sequence`) VALUES (1, YEAR(CURDATE()), 0);