--
-- Host: localhost    Database: coastalcommand
-- ------------------------------------------------------

/*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
/*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
/*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;

--
-- Current Database: `coastalcommand`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `coastalcommand` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `coastalcommand`;

--
-- Temporary view structure for view `active_customers`
--

/*!50001 DROP VIEW IF EXISTS `active_customers`*/;
/*!50001 CREATE VIEW `active_customers` AS SELECT 
 1 AS `customer_id`,
 1 AS `first_name`,
 1 AS `last_name`,
 1 AS `email`,
 1 AS `phone`,
 1 AS `service_zip`,
 1 AS `total_orders`,
 1 AS `total_spent`,
 1 AS `last_order_date`*/;

--
-- Table structure for table `activity_log`
--

CREATE TABLE `activity_log` (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` int DEFAULT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_entity` (`entity_type`,`entity_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `activity_log`
--


--
-- Table structure for table `admin_users`
--

CREATE TABLE `admin_users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `role` enum('admin','business_user','customer') DEFAULT 'customer',
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `status` enum('active','inactive','pending') DEFAULT 'pending',
  `password_reset_token` varchar(255) DEFAULT NULL,
  `password_reset_expires` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_email` (`email`),
  KEY `idx_role` (`role`),
  KEY `idx_status` (`status`),
  KEY `idx_reset_token` (`password_reset_token`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `admin_users`
--

INSERT INTO `admin_users` VALUES (1,'admin@coastalcommandfl.com','$2y$10$kfk8p5p5p5p5p5p5p5p5p.e5K5e5K5e5K5e5K5e5K5e5K5e5K5e5','admin','Admin','User',NULL,'active',NULL,NULL,NULL,'2026-01-22 15:23:01','2026-01-22 15:23:01'),(2,'keith@coastalcommandfl.com','$2y$12$tor8s4H/P3ynH.JM77ey4u53zNZLZjsrPDkrxsqFJNc0DWMrTwEzC','admin','Keith','Admin',NULL,'active',NULL,NULL,'2026-01-25 18:50:17','2026-01-22 16:08:34','2026-01-25 18:50:17'),(3,'joe@coastalcommandfl.com','$2y$12$tor8s4H/P3ynH.JM77ey4u53zNZLZjsrPDkrxsqFJNc0DWMrTwEzC','admin','Joe','Collings',NULL,'pending','0d21fc3d969b1991ee69a28b4d12464d91c609c28745b79afd9c1b46bf4c41fc','2026-01-23 01:47:30',NULL,'2026-01-23 00:46:52','2026-01-23 00:51:40'),(4,'caralyn@coastalcommandfl.com','$2y$12$tor8s4H/P3ynH.JM77ey4u53zNZLZjsrPDkrxsqFJNc0DWMrTwEzC','business_user','Caralyn','Collins',NULL,'pending','98e82916d7a446fd41ac424d08078be10b3a5ef0b9cba67f6dbe7f6c441b19aa','2026-01-24 00:48:34',NULL,'2026-01-23 00:48:34','2026-01-23 00:51:40');

--
-- Table structure for table `analytics_daily_summary`
--

CREATE TABLE `analytics_daily_summary` (
  `summary_id` int NOT NULL AUTO_INCREMENT,
  `summary_date` date NOT NULL,
  `total_orders` int DEFAULT '0',
  `completed_orders` int DEFAULT '0',
  `cancelled_orders` int DEFAULT '0',
  `total_revenue` decimal(10,2) DEFAULT '0.00',
  `new_customers` int DEFAULT '0',
  `returning_customers` int DEFAULT '0',
  `average_satisfaction_rating` decimal(3,2) DEFAULT NULL,
  `chatbot_interactions` int DEFAULT '0',
  `support_tickets` int DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`summary_id`),
  UNIQUE KEY `summary_date` (`summary_date`),
  KEY `idx_summary_date` (`summary_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `analytics_daily_summary`
--


--
-- Table structure for table `blogs`
--

CREATE TABLE `blogs` (
  `blog_id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `excerpt` text,
  `content` longtext,
  `external_url` varchar(500) DEFAULT NULL,
  `featured_image_url` varchar(500) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `status` enum('draft','published') DEFAULT 'draft',
  `published_date` datetime DEFAULT NULL,
  `position_order` int DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`blog_id`),
  KEY `idx_status` (`status`),
  KEY `idx_published_date` (`published_date`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `blogs`
--

INSERT INTO `blogs` VALUES (1,'Professional Bin Cleaning Benefits','Discover why professional bin cleaning is essential for hygiene and pest prevention.','Regular professional bin cleaning eliminates 99.9% of bacteria, removes odors, and prevents pest infestations. Our eco-friendly process uses high-temperature steam and biodegradable products to keep your bins fresh and sanitary.','https://coastalcommandfl.blogspot.com/search?q=bin+cleaning+benefits',NULL,'Maintenance','cleaning,hygiene,bacteria','published','2026-01-22 10:33:15',1,'2026-01-22 16:33:15','2026-01-22 16:33:15'),(2,'Eco-Friendly Cleaning Solutions','Learn about our sustainable approach to trash bin sanitization.','At Coastal Command, we use 100% biodegradable cleaning products and water-saving technology. Our eco-friendly methods are gentle on the environment while delivering powerful cleaning results.','https://coastalcommandfl.blogspot.com/search?q=eco+friendly+cleaning',NULL,'Environment','eco-friendly,sustainability,green','published','2026-01-22 10:33:15',2,'2026-01-22 16:33:15','2026-01-22 16:33:15'),(3,'Odor Control Tips for Homeowners','Expert tips to keep your trash bins odor-free between cleanings.','Beyond professional cleaning, there are several steps you can take to minimize odors. Keep your bins sealed, rinse containers before disposal, and consider sprinkle baking soda for extra freshness.','https://coastalcommandfl.blogspot.com/search?q=odor+control',NULL,'Tips','odor,maintenance,tips','published','2026-01-22 10:33:15',3,'2026-01-22 16:33:15','2026-01-22 16:33:15');

--
-- Table structure for table `chatbot_interactions`
--

CREATE TABLE `chatbot_interactions` (
  `interaction_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `email_address` varchar(100) DEFAULT NULL,
  `interaction_type` enum('sms','voice_call','web_chat','email') DEFAULT 'sms',
  `input_text` text,
  `intent` varchar(100) DEFAULT NULL,
  `confidence_score` decimal(3,2) DEFAULT NULL,
  `response_text` text,
  `response_action` varchar(100) DEFAULT NULL,
  `escalated_to` int DEFAULT NULL,
  `escalation_reason` varchar(255) DEFAULT NULL,
  `duration_seconds` int DEFAULT NULL,
  `satisfaction_rating` int DEFAULT NULL,
  `feedback` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`interaction_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_interaction_type` (`interaction_type`),
  KEY `idx_intent` (`intent`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `chatbot_interactions`
--


--
-- Table structure for table `chatbot_knowledge`
--

CREATE TABLE `chatbot_knowledge` (
  `knowledge_id` int NOT NULL AUTO_INCREMENT,
  `category` varchar(100) NOT NULL COMMENT 'faq, policy, promotion, service_info, general',
  `topic` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `keywords` text COMMENT 'Comma-separated keywords for matching',
  `priority` int DEFAULT '0' COMMENT 'Higher = more important, shown first',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`knowledge_id`),
  KEY `idx_category` (`category`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Knowledge base for AI chatbot';

--
-- Dumping data for table `chatbot_knowledge`
--

INSERT INTO `chatbot_knowledge` VALUES (1,'faq','Do I need to be home?','No! We clean your bins curbside after your trash pickup day. You don\'t need to be home – just leave your bins out and we\'ll take care of everything. You\'ll receive a text notification when we\'re done.','home, present, there, curbside, curb',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(2,'faq','What if it rains?','We automatically reschedule if weather conditions aren\'t ideal. You\'ll receive a text notification with your new appointment time. No action needed on your part!','rain, weather, reschedule, storm, cancel',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(3,'faq','How long does cleaning take?','Most residential cleanings are completed in under 30 minutes. Our professional high-temperature steam equipment handles the job quickly and thoroughly.','time, long, duration, minutes, how long',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(4,'faq','What products do you use?','We use 100% eco-friendly, biodegradable cleaning solutions and natural deodorizers with lavender and mint scents. No harsh chemicals or toxins – completely safe for your family, pets, and the environment.','products, chemicals, eco, green, safe, biodegradable, organic',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(5,'faq','Can I cancel anytime?','Absolutely! There are no contracts. You can cancel, pause, or adjust your service frequency anytime with just a phone call or email. We believe in earning your business every visit.','cancel, contract, pause, stop, quit',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(6,'faq','How often should I clean my bins?','It depends on your needs. Monthly service is our most popular option and works great for most families. Quarterly is good for light trash producers. Weekly or bi-weekly is ideal for larger families or hot summer months when odors build faster.','often, frequency, how often, schedule, recommend',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(7,'faq','What areas do you service?','We proudly serve the Florida Panhandle including Fort Walton Beach, Destin, Niceville, Crestview, Panama City Beach, and surrounding communities. Call us to confirm service in your specific area!','area, location, service area, where, city, zone, panhandle, florida',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(8,'faq','Do you clean recycling bins too?','Yes! We clean all types of residential bins including trash cans and recycling bins. Same great service for all your bins.','recycling, recycle, blue bin, all bins',8,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(9,'policy','Satisfaction Guarantee','We offer a 100% satisfaction guarantee. If you\'re not completely happy with our service, let us know and we\'ll make it right or refund your money.','guarantee, refund, satisfied, happy, money back',9,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(10,'policy','Payment Methods','We accept all major credit cards, debit cards, and digital payments. Payment is processed securely online when you book your service.','payment, pay, credit card, debit, how to pay',8,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(11,'promotion','New Customer Discount','🎉 New customers get 50% OFF their first cleaning! This is a limited time offer. Book online or call (850) 818-8318 to claim your discount.','discount, deal, offer, promotion, first time, new customer, 50%, half off, special',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(12,'service_info','Our Cleaning Process','Our professional cleaning process uses high-temperature steam (200°+ degrees) that kills 99.9% of bacteria, germs, and viruses. We then apply eco-friendly deodorizers that leave your bins smelling fresh with natural lavender and mint scents.','process, how, clean, steam, temperature, bacteria, sanitize',9,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(13,'service_info','Why Clean Your Bins','Dirty trash bins harbor dangerous bacteria like E. coli, Salmonella, and Listeria. They attract pests like flies, maggots, rodents, and raccoons. Regular cleaning eliminates health hazards, removes foul odors, and keeps pests away from your home.','why, benefits, health, bacteria, germs, pests, smell, odor',9,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(14,'general','Contact Information','You can reach Coastal Command Bin Cleaning at (850) 818-8318 or email info@coastalcommandfl.com. Our office hours are Monday-Friday 7am-6pm and Saturday 8am-4pm.','contact, phone, email, call, reach, hours, talk to someone',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44'),(15,'general','Book a Service','Ready to get started? You can book online at our website or call (850) 818-8318. New customers get 50% off their first cleaning!','book, schedule, sign up, start, order, appointment',10,'active','2026-01-26 00:58:44','2026-01-26 00:58:44');

--
-- Table structure for table `content_sections`
--

CREATE TABLE `content_sections` (
  `content_id` int NOT NULL AUTO_INCREMENT,
  `section_key` varchar(100) NOT NULL,
  `section_name` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `content_type` enum('text','html','json') DEFAULT 'text',
  `position_order` int DEFAULT '0',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`content_id`),
  UNIQUE KEY `section_key` (`section_key`),
  KEY `idx_section_key` (`section_key`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `content_sections`
--

INSERT INTO `content_sections` VALUES (1,'hero_promo_badge','Hero Promo Badge','🎉 LIMITED TIME: 50% Off Your First Clean!!!','text',10,'active','2026-01-22 15:15:13','2026-01-23 00:52:47'),(2,'hero_title','Hero Title','Sparkling Clean Bins, Delivered to Your Door','text',20,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(3,'hero_subtitle','Hero Subtitle','Professional eco-friendly trash bin cleaning that kills 99.9% bacteria, eliminates odors, and keeps pests away. Starting at just $25/month.','text',30,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(4,'benefits_title','Benefits Section Title','Why Choose Coastal Command?','text',40,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(5,'benefits_card_1_title','Benefit 1 Title','Kills 99.9% Bacteria','text',50,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(6,'benefits_card_1_desc','Benefit 1 Description','Professional high-temperature steam cleaning for ultimate hygiene and safety.','text',60,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(7,'benefits_card_2_title','Benefit 2 Title','Eliminates Odors','text',70,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(8,'benefits_card_2_desc','Benefit 2 Description','Natural deodorizers with fresh lavender and mint scents keep bins smelling great.','text',80,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(9,'benefits_card_3_title','Benefit 3 Title','Eco-Friendly','text',90,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(10,'benefits_card_3_desc','Benefit 3 Description','100% biodegradable products and water-saving technology – good for the planet.','text',100,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(11,'benefits_card_4_title','Benefit 4 Title','Convenient Service','text',110,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(12,'benefits_card_4_desc','Benefit 4 Description','On-site cleaning on your curb – no need to be home, just relax!','text',120,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(13,'how_it_works_title','How It Works Section Title','How It Works','text',130,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(14,'how_it_works_step_1_title','Step 1 Title','Book Online','text',140,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(15,'how_it_works_step_1_desc','Step 1 Description','Select your plan and choose your preferred date and time. It takes less than 2 minutes!','text',150,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(16,'how_it_works_step_2_title','Step 2 Title','We Clean Your Curb','text',160,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(17,'how_it_works_step_2_desc','Step 2 Description','Our truck arrives with professional equipment. We clean your bins with 200°+ steam in under 30 minutes.','text',170,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(18,'how_it_works_step_3_title','Step 3 Title','Enjoy Fresh Bins','text',180,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(19,'how_it_works_step_3_desc','Step 3 Description','Sanitized, deodorized bins returned to your curb with text notifications and zero hassle.','text',190,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(20,'how_it_works_note','How It Works Note','💧 Our custom trucks use less water than a garden hose – good for your wallet and the planet.','text',200,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(21,'services_title','Services Section Title','Our Services','text',210,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(22,'pricing_title','Pricing Section Title','Transparent, Flexible Pricing','text',220,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(23,'pricing_note','Pricing Note','✓ No Contracts – Cancel Anytime | ✓ Free Estimates | ✓ 100% Satisfaction Guarantee','text',230,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(24,'testimonials_title','Testimonials Section Title','What Our Customers Say','text',240,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(25,'faqs_title','FAQs Section Title','Frequently Asked Questions','text',250,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(26,'blog_title','Blog Section Title','Our Blog: Trash Talk','text',260,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(27,'footer_about_title','Footer About Title','About Coastal Command','text',270,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(28,'footer_about_text','Footer About Text','Professional, eco-friendly bin cleaning services serving Florida with pride and expertise. Trusted by thousands of happy customers.','text',280,'active','2026-01-22 15:15:13','2026-01-22 15:15:13'),(29,'terms_of_service','Terms of Service','<h2>1. Services Provided</h2>\n<p>Coastal Command Bin Cleaning provides residential and commercial trash bin sanitization and cleaning services in the Florida Panhandle area, including Fort Walton Beach, Destin, Niceville, Crestview, and surrounding communities.</p>\n\n<h3>1.1 Service Description</h3>\n<ul>\n    <li>On-site trash bin cleaning using high-temperature water (200°F+)</li>\n    <li>EPA-approved disinfectants and eco-friendly cleaning solutions</li>\n    <li>Deodorization with natural scents</li>\n    <li>Service performed on scheduled cleaning days following your regular trash pickup</li>\n</ul>\n\n<h3>1.2 Service Plans</h3>\n<ul>\n    <li><strong>Monthly Service:</strong> Cleaning performed every 4 weeks</li>\n    <li><strong>Quarterly Service:</strong> Cleaning performed every 12 weeks</li>\n    <li><strong>Semi-Annual Service:</strong> Cleaning performed every 6 months</li>\n    <li><strong>Annual Service:</strong> Cleaning performed once per year</li>\n    <li><strong>One-Time Service:</strong> Single cleaning on demand</li>\n</ul>\n\n<h2>2. Customer Responsibilities</h2>\n<h3>2.1 Access to Bins</h3>\n<p>Customers must ensure bins are:</p>\n<ul>\n    <li>Placed curbside on scheduled service days by 7:00 AM</li>\n    <li>Empty of trash (bins should be emptied by waste collection before our service)</li>\n    <li>Accessible without obstructions (vehicles, gates, etc.)</li>\n    <li>In serviceable condition (not excessively damaged or broken)</li>\n</ul>\n\n<h3>2.2 Accurate Information</h3>\n<p>Customers agree to provide accurate contact information, service address, and trash pickup schedule. Changes to this information must be communicated to Coastal Command at least 48 hours before the next scheduled service.</p>\n\n<h2>3. Scheduling and Service</h2>\n<h3>3.1 Service Timing</h3>\n<p>Services are scheduled based on your regular trash pickup day. Our cleaning typically occurs on the same day as trash collection, after bins are emptied. We cannot guarantee specific service times, only the service date.</p>\n\n<h3>3.2 Weather Delays</h3>\n<p>Services may be rescheduled due to severe weather conditions, including but not limited to tropical storms, hurricanes, or other unsafe conditions. Affected customers will be notified and service rescheduled at no additional charge.</p>\n\n<h3>3.3 Missed Service</h3>\n<p>If we are unable to access your bins due to obstacles, locked gates, or bins not being placed curbside, the service may be skipped. No refunds will be issued for missed service due to customer-related access issues.</p>\n\n<h2>4. Billing and Payment</h2>\n<h3>4.1 Payment Methods</h3>\n<p>We accept payment via credit card, debit card, and ACH bank transfer through our secure payment processor (Stripe). Payment information is stored securely and charged automatically based on your service plan.</p>\n\n<h3>4.2 Recurring Billing</h3>\n<ul>\n    <li><strong>Monthly Plans:</strong> Billed every 4 weeks on the date of service</li>\n    <li><strong>Quarterly Plans:</strong> Billed every 12 weeks on the date of service</li>\n    <li><strong>Semi-Annual Plans:</strong> Billed every 6 months</li>\n    <li><strong>Annual Plans:</strong> Billed once per year</li>\n</ul>\n\n<h3>4.3 Failed Payments</h3>\n<p>If a payment fails, we will attempt to process payment up to 3 times over 10 days. If payment cannot be processed, your service may be suspended until payment is received. A $25 failed payment fee may be assessed.</p>\n\n<h3>4.4 Price Changes</h3>\n<p>Coastal Command reserves the right to adjust pricing with 30 days\' advance notice. Customers will be notified via email of any price changes.</p>\n\n<h2>5. Cancellation and Refunds</h2>\n<h3>5.1 Cancellation Policy</h3>\n<p>You may cancel your subscription at any time with no cancellation fee. Cancellations must be submitted at least 48 hours before your next scheduled service to avoid being charged.</p>\n\n<h3>5.2 Refund Policy</h3>\n<ul>\n    <li>No refunds for services already performed</li>\n    <li>Refunds for prepaid services will be prorated based on services not yet rendered</li>\n    <li>One-time service payments are non-refundable once service is completed</li>\n</ul>\n\n<h3>5.3 Service Guarantee</h3>\n<p>If you are not satisfied with our service, contact us within 48 hours and we will re-clean your bins at no additional charge.</p>\n\n<h2>6. Liability and Insurance</h2>\n<h3>6.1 Our Liability</h3>\n<p>Coastal Command Bin Cleaning is fully insured. We take reasonable precautions to avoid damage to your property. In the event of damage caused by our service, liability is limited to the repair or replacement cost, up to a maximum of $500 per incident.</p>\n\n<h3>6.2 Customer Property</h3>\n<p>Coastal Command is not liable for damage to bins that are already damaged, excessively worn, or in poor condition. We are not responsible for bins that are:</p>\n<ul>\n    <li>Cracked, broken, or structurally compromised before service</li>\n    <li>Improperly manufactured or defective</li>\n    <li>Damaged by third parties (waste collection, vehicles, weather, etc.)</li>\n</ul>\n\n<h3>6.3 Exclusions</h3>\n<p>We are not liable for:</p>\n<ul>\n    <li>Loss or theft of bins</li>\n    <li>Delays in waste collection by municipal services</li>\n    <li>Damage caused by improper bin placement by customer or waste collector</li>\n    <li>Water runoff or drainage issues on customer property</li>\n</ul>\n\n<h2>7. Privacy and Data Protection</h2>\n<p>Your personal information is collected and used in accordance with our <a href=\"/privacy/\" style=\"color: #00a86b;\">Privacy Policy</a>. We do not sell or share your personal information with third parties except as necessary to provide services (payment processing, scheduling, etc.).</p>\n\n<h2>8. Modifications to Terms</h2>\n<p>Coastal Command reserves the right to modify these Terms at any time. Changes will be posted on our website and customers will be notified via email. Continued use of services after changes indicates acceptance of modified Terms.</p>\n\n<h2>9. Dispute Resolution</h2>\n<h3>9.1 Informal Resolution</h3>\n<p>In the event of any dispute, customers agree to contact Coastal Command first to attempt informal resolution before pursuing legal action.</p>\n\n<h3>9.2 Governing Law</h3>\n<p>These Terms are governed by the laws of the State of Florida. Any legal action must be brought in the courts of Okaloosa County, Florida.</p>\n\n<h3>9.3 Arbitration</h3>\n<p>Any disputes not resolved informally will be settled through binding arbitration in accordance with Florida law, rather than in court.</p>\n\n<h2>10. Contact Information</h2>\n<p>For questions about these Terms or our services, contact us:</p>\n<ul>\n    <li><strong>Phone:</strong> (850) 818-8318</li>\n    <li><strong>Email:</strong> info@coastalcommandfl.com</li>\n    <li><strong>Address:</strong> 1016 Thomas Drive, PMB 327, Panama City Beach, FL 32408</li>\n</ul>\n\n<h2>11. Severability</h2>\n<p>If any provision of these Terms is found to be unenforceable, the remaining provisions will continue in full force and effect.</p>\n\n<h2>12. Entire Agreement</h2>\n<p>These Terms, along with our Privacy Policy, constitute the entire agreement between you and Coastal Command Bin Cleaning regarding use of our services.</p>\n\n<p style=\"margin-top: 2rem; padding-top: 2rem; border-top: 1px solid #ddd;\">\n    <strong>By using our services, you acknowledge that you have read, understood, and agree to be bound by these Terms of Service.</strong>\n</p>','html',100,'active','2026-01-25 17:46:43','2026-01-25 17:46:43'),(30,'privacy_policy','Privacy Policy','<div class=\"highlight-box\" style=\"background: #e8f5f0; border-left: 4px solid #00a86b; padding: 1rem; margin: 1.5rem 0;\">\n    <strong>Key Points:</strong>\n    <ul style=\"margin: 0.5rem 0 0 1.5rem;\">\n        <li>We collect only necessary information to provide our services</li>\n        <li>We never sell your personal information to third parties</li>\n        <li>Payment information is processed securely through Stripe (PCI-compliant)</li>\n        <li>You have the right to access, update, or delete your information</li>\n    </ul>\n</div>\n\n<h2>1. Information We Collect</h2>\n\n<h3>1.1 Personal Information</h3>\n<p>When you sign up for our services, we collect:</p>\n<ul>\n    <li><strong>Contact Information:</strong> Name, email address, phone number, service address</li>\n    <li><strong>Account Information:</strong> Username, password (encrypted), customer ID</li>\n    <li><strong>Service Details:</strong> Number of bins, trash pickup day, service frequency</li>\n    <li><strong>Billing Information:</strong> Payment card details (stored securely by Stripe, not on our servers)</li>\n</ul>\n\n<h3>1.2 Automatically Collected Information</h3>\n<p>When you visit our website, we may automatically collect:</p>\n<ul>\n    <li><strong>Device Information:</strong> IP address, browser type, operating system</li>\n    <li><strong>Usage Data:</strong> Pages visited, time spent on site, links clicked</li>\n    <li><strong>Location Data:</strong> General geographic location based on IP address</li>\n    <li><strong>Cookies:</strong> Small data files stored on your device (see Section 6)</li>\n</ul>\n\n<h3>1.3 Service-Related Information</h3>\n<p>During service delivery, we may collect:</p>\n<ul>\n    <li><strong>Service Logs:</strong> Date, time, and status of each cleaning</li>\n    <li><strong>GPS Coordinates:</strong> Location data for route optimization and service verification</li>\n    <li><strong>Photos:</strong> Before/after photos (if requested or for quality assurance)</li>\n    <li><strong>Communication Records:</strong> Emails, text messages, phone calls with customer support</li>\n</ul>\n\n<h2>2. How We Use Your Information</h2>\n\n<h3>2.1 Service Provision</h3>\n<ul>\n    <li>Schedule and perform bin cleaning services</li>\n    <li>Process payments and manage subscriptions</li>\n    <li>Send service reminders and confirmations</li>\n    <li>Optimize routes for efficient service delivery</li>\n    <li>Verify service completion</li>\n</ul>\n\n<h3>2.2 Communication</h3>\n<ul>\n    <li>Respond to customer inquiries and support requests</li>\n    <li>Send account-related notifications (password resets, billing issues)</li>\n    <li>Provide service updates and operational notices</li>\n    <li>Send promotional emails (with your consent; you can opt-out)</li>\n</ul>\n\n<h3>2.3 Business Operations</h3>\n<ul>\n    <li>Analyze usage patterns to improve our services</li>\n    <li>Detect and prevent fraud or security issues</li>\n    <li>Comply with legal obligations</li>\n    <li>Enforce our Terms of Service</li>\n    <li>Resolve disputes and troubleshoot issues</li>\n</ul>\n\n<h2>3. How We Share Your Information</h2>\n\n<h3>3.1 Service Providers</h3>\n<p>We share information with trusted third-party service providers who help us operate our business:</p>\n<ul>\n    <li><strong>Payment Processing:</strong> Stripe (PCI-DSS compliant) - for secure payment processing</li>\n    <li><strong>Communication:</strong> Twilio - for SMS notifications and phone calls</li>\n    <li><strong>Email Delivery:</strong> Gmail API - for sending service emails</li>\n    <li><strong>Mapping/Routing:</strong> OpenStreetMap/OSRM - for route optimization</li>\n    <li><strong>Web Hosting:</strong> DigitalOcean - for website and database hosting</li>\n</ul>\n<p><em>These providers are contractually obligated to protect your information and use it only for the services they provide to us.</em></p>\n\n<h3>3.2 Legal Requirements</h3>\n<p>We may disclose your information if required by law or in response to:</p>\n<ul>\n    <li>Subpoenas, court orders, or legal processes</li>\n    <li>Government or regulatory requests</li>\n    <li>Protection of our rights, property, or safety</li>\n    <li>Investigation of fraud or security issues</li>\n</ul>\n\n<h3>3.3 Business Transfers</h3>\n<p>If Coastal Command is involved in a merger, acquisition, or sale of assets, your information may be transferred as part of that transaction. We will notify you of any such change.</p>\n\n<h3>3.4 What We DO NOT Do</h3>\n<p><strong>We DO NOT:</strong></p>\n<ul>\n    <li>❌ Sell your personal information to third parties</li>\n    <li>❌ Share your data with advertisers or marketing companies</li>\n    <li>❌ Use your information for purposes unrelated to our services</li>\n    <li>❌ Store credit card details on our servers (handled by Stripe)</li>\n</ul>\n\n<h2>4. Data Security</h2>\n\n<h3>4.1 Security Measures</h3>\n<p>We implement industry-standard security measures to protect your information:</p>\n<ul>\n    <li><strong>Encryption:</strong> SSL/TLS encryption for all data transmission</li>\n    <li><strong>Secure Storage:</strong> Encrypted databases with restricted access</li>\n    <li><strong>Password Protection:</strong> Passwords hashed using bcrypt algorithm</li>\n    <li><strong>Access Controls:</strong> Limited employee access on a need-to-know basis</li>\n    <li><strong>Regular Audits:</strong> Security reviews and vulnerability assessments</li>\n    <li><strong>PCI Compliance:</strong> Payment processing meets PCI-DSS standards via Stripe</li>\n</ul>\n\n<h3>4.2 Data Retention</h3>\n<p>We retain your information for as long as necessary to provide services and comply with legal obligations:</p>\n<ul>\n    <li><strong>Active Accounts:</strong> Retained while your account is active</li>\n    <li><strong>After Cancellation:</strong> Retained for 7 years for tax and legal purposes</li>\n    <li><strong>Marketing Data:</strong> Retained until you opt-out or request deletion</li>\n    <li><strong>Service Logs:</strong> Retained for 2 years for quality assurance</li>\n</ul>\n\n<h2>5. Your Privacy Rights</h2>\n\n<h3>5.1 Access and Correction</h3>\n<p>You have the right to:</p>\n<ul>\n    <li>Access your personal information</li>\n    <li>Update or correct inaccurate information</li>\n    <li>Request a copy of your data (data portability)</li>\n</ul>\n<p>You can manage most information through your customer portal or by contacting us.</p>\n\n<h3>5.2 Deletion</h3>\n<p>You have the right to request deletion of your personal information, subject to:</p>\n<ul>\n    <li>Legal obligations to retain certain records (tax, accounting, legal disputes)</li>\n    <li>Completion of any pending transactions or services</li>\n    <li>Our legitimate business interests (fraud prevention, service improvement)</li>\n</ul>\n<p><em>To request deletion, email us at <a href=\"mailto:info@coastalcommandfl.com\" style=\"color: #00a86b;\">info@coastalcommandfl.com</a></em></p>\n\n<h3>5.3 Opt-Out</h3>\n<p>You can opt-out of:</p>\n<ul>\n    <li><strong>Marketing Emails:</strong> Click \"unsubscribe\" in any promotional email</li>\n    <li><strong>SMS Notifications:</strong> Reply STOP to any text message</li>\n    <li><strong>Cookies:</strong> Adjust your browser settings (see Section 6)</li>\n</ul>\n<p><em>Note: You cannot opt-out of essential service communications (account notifications, billing, service confirmations).</em></p>\n\n<h3>5.4 California Residents (CCPA)</h3>\n<p>If you are a California resident, you have additional rights under the California Consumer Privacy Act:</p>\n<ul>\n    <li>Right to know what personal information is collected</li>\n    <li>Right to know if personal information is sold or disclosed</li>\n    <li>Right to opt-out of the sale of personal information (we do not sell data)</li>\n    <li>Right to request deletion of personal information</li>\n    <li>Right to non-discrimination for exercising your rights</li>\n</ul>\n\n<h2>6. Cookies and Tracking Technologies</h2>\n\n<h3>6.1 What Are Cookies?</h3>\n<p>Cookies are small text files stored on your device when you visit our website. They help us provide a better user experience.</p>\n\n<h3>6.2 Types of Cookies We Use</h3>\n<ul>\n    <li><strong>Essential Cookies:</strong> Required for the website to function (login sessions, shopping cart)</li>\n    <li><strong>Performance Cookies:</strong> Track how visitors use our site (anonymized analytics)</li>\n    <li><strong>Functional Cookies:</strong> Remember your preferences (language, location)</li>\n    <li><strong>Marketing Cookies:</strong> Track effectiveness of advertising campaigns (if applicable)</li>\n</ul>\n\n<h3>6.3 Managing Cookies</h3>\n<p>You can control cookies through your browser settings:</p>\n<ul>\n    <li>Block all cookies</li>\n    <li>Accept cookies only from specific sites</li>\n    <li>Delete cookies when you close your browser</li>\n    <li>Receive notification before cookies are set</li>\n</ul>\n<p><em>Note: Blocking essential cookies may prevent some website features from working.</em></p>\n\n<h2>7. Third-Party Links</h2>\n<p>Our website may contain links to third-party sites (social media, payment processors, etc.). We are not responsible for the privacy practices of these external sites. Please review their privacy policies before providing any information.</p>\n\n<h2>8. Children\'s Privacy</h2>\n<p>Our services are not intended for children under 18 years of age. We do not knowingly collect personal information from children. If we discover that we have collected information from a child under 18, we will delete it immediately.</p>\n\n<h2>9. Changes to This Privacy Policy</h2>\n<p>We may update this Privacy Policy from time to time to reflect changes in our practices or legal requirements. Updates will be posted on this page with a revised \"Last Updated\" date. Significant changes will be communicated via email.</p>\n<p>Your continued use of our services after changes indicates acceptance of the updated Privacy Policy.</p>\n\n<h2>10. Contact Us</h2>\n<p>If you have questions, concerns, or requests regarding this Privacy Policy or our data practices, please contact us:</p>\n<ul>\n    <li><strong>Email:</strong> <a href=\"mailto:info@coastalcommandfl.com\" style=\"color: #00a86b;\">info@coastalcommandfl.com</a></li>\n    <li><strong>Phone:</strong> (850) 818-8318</li>\n    <li><strong>Mail:</strong> Coastal Command Bin Cleaning<br/>\n        1016 Thomas Drive, PMB 327<br/>\n        Panama City Beach, FL 32408</li>\n</ul>\n\n<h2>11. Data Protection Officer</h2>\n<p>For privacy-related inquiries, you may contact our Data Protection Officer at the email address above.</p>\n\n<h2>12. International Users</h2>\n<p>Our services are primarily intended for users in the United States. If you access our services from outside the U.S., please be aware that your information may be transferred to and processed in the United States, where privacy laws may differ from your jurisdiction.</p>\n\n<p style=\"margin-top: 2rem; padding-top: 2rem; border-top: 1px solid #ddd;\">\n    <strong>By using our services, you acknowledge that you have read and understood this Privacy Policy and agree to our collection, use, and disclosure of your information as described.</strong>\n</p>','html',101,'active','2026-01-25 17:46:43','2026-01-25 17:46:43');

--
-- Table structure for table `customer_contacts`
--

CREATE TABLE `customer_contacts` (
  `contact_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `contact_type` enum('email','phone','sms','in_app_message') NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `direction` enum('inbound','outbound') NOT NULL,
  `contact_date` datetime NOT NULL,
  `responded_by` int DEFAULT NULL,
  `response_time_minutes` int DEFAULT NULL,
  `sentiment` enum('positive','neutral','negative') DEFAULT NULL,
  `status` enum('open','responded','resolved','closed') DEFAULT 'open',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`contact_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_contact_date` (`contact_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `customer_contacts`
--


--
-- Table structure for table `customer_metrics`
--

CREATE TABLE `customer_metrics` (
  `metric_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `total_orders` int DEFAULT '0',
  `total_spent` decimal(10,2) DEFAULT '0.00',
  `average_satisfaction_rating` decimal(3,2) DEFAULT NULL,
  `last_order_date` date DEFAULT NULL,
  `days_since_last_order` int DEFAULT NULL,
  `subscription_status` varchar(50) DEFAULT NULL,
  `churn_risk_score` decimal(3,2) DEFAULT NULL,
  `lifetime_value` decimal(10,2) DEFAULT NULL,
  `referred_customer_count` int DEFAULT '0',
  `review_count` int DEFAULT '0',
  `average_review_rating` decimal(3,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`metric_id`),
  UNIQUE KEY `customer_id` (`customer_id`),
  KEY `idx_churn_risk_score` (`churn_risk_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `customer_metrics`
--


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

CREATE TABLE `customer_notes` (
  `note_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `user_id` int NOT NULL,
  `note_text` text NOT NULL,
  `note_type` enum('general','complaint','complaint_resolution','special_instruction','follow_up','internal') DEFAULT 'general',
  `is_internal` tinyint(1) DEFAULT '1',
  `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 `idx_customer_id` (`customer_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `customer_notes`
--


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

CREATE TABLE `customers` (
  `customer_id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `password_reset_token` varchar(64) DEFAULT NULL,
  `password_reset_expires` datetime DEFAULT NULL,
  `portal_access_enabled` tinyint(1) DEFAULT '0',
  `phone` varchar(20) NOT NULL,
  `service_address` varchar(255) NOT NULL,
  `service_city` varchar(50) NOT NULL,
  `service_state` varchar(50) NOT NULL,
  `service_zip` varchar(10) NOT NULL,
  `zone_id` int DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `geocoded_at` timestamp NULL DEFAULT NULL,
  `billing_address` varchar(255) DEFAULT NULL,
  `billing_city` varchar(50) DEFAULT NULL,
  `billing_state` varchar(50) DEFAULT NULL,
  `billing_zip` varchar(10) DEFAULT NULL,
  `number_of_bins` int DEFAULT '2',
  `preferred_contact_method` enum('email','phone','sms') DEFAULT 'email',
  `status` enum('active','inactive','paused','cancelled') DEFAULT 'active',
  `customer_lifetime_value` decimal(10,2) DEFAULT '0.00',
  `total_cleanings` int DEFAULT '0',
  `source` enum('website','referral','phone','social_media','other') DEFAULT 'website',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `uk_email_phone` (`email`,`phone`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_service_zip` (`service_zip`),
  KEY `idx_zone` (`zone_id`),
  KEY `idx_password_reset_token` (`password_reset_token`),
  FULLTEXT KEY `idx_search` (`first_name`,`last_name`,`email`,`phone`),
  CONSTRAINT `fk_customer_zone` FOREIGN KEY (`zone_id`) REFERENCES `service_zones` (`zone_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` VALUES (1,'Test','Customer','test.customer@coastalcommandfl.com',NULL,NULL,NULL,0,'(850) 555-0100','8207 Grand Palm Blvd','Panama City Beach','FL','32408',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,'email','active',0.00,0,'website',NULL,'2026-01-22 23:17:33','2026-01-22 23:17:33'),(2,'Test','Customer','test@coastalcommandfl.com',NULL,NULL,NULL,0,'(850) 555-0100','8207 Grand Palm Blvd','Panama City Beach','FL','32408',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,'email','active',0.00,0,'website',NULL,'2026-01-22 23:18:28','2026-01-22 23:18:28');

--
-- Table structure for table `feedback`
--

CREATE TABLE `feedback` (
  `feedback_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `order_id` int DEFAULT NULL,
  `feedback_type` enum('complaint','suggestion','general') DEFAULT 'general',
  `category` varchar(100) DEFAULT NULL,
  `feedback_text` text NOT NULL,
  `attachment_url` varchar(255) DEFAULT NULL,
  `status` enum('received','acknowledged','in_review','resolved','closed') DEFAULT 'received',
  `resolution_note` text,
  `priority` enum('low','medium','high') DEFAULT 'medium',
  `assigned_to` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `resolved_date` datetime DEFAULT NULL,
  PRIMARY KEY (`feedback_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `feedback`
--


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

CREATE TABLE `invoices` (
  `invoice_id` int NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(50) NOT NULL,
  `order_id` int DEFAULT NULL,
  `customer_id` int NOT NULL,
  `invoice_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  `tax` decimal(10,2) DEFAULT NULL,
  `discount` decimal(10,2) DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  `amount_paid` decimal(10,2) DEFAULT '0.00',
  `status` enum('draft','sent','viewed','partial','paid','overdue','cancelled') DEFAULT 'draft',
  `payment_terms` varchar(50) DEFAULT NULL,
  `notes` text,
  `pdf_url` varchar(255) DEFAULT NULL,
  `sent_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`invoice_id`),
  UNIQUE KEY `invoice_number` (`invoice_number`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_due_date` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `invoices`
--


--
-- Table structure for table `message_threads`
--

CREATE TABLE `message_threads` (
  `thread_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `thread_type` enum('customer_support','appointment','billing','feedback','chatbot') DEFAULT 'customer_support',
  `subject` varchar(255) DEFAULT NULL,
  `status` enum('open','in_progress','waiting_customer','resolved','closed') DEFAULT 'open',
  `assigned_to` int DEFAULT NULL,
  `priority` enum('low','medium','high','urgent') DEFAULT 'medium',
  `last_message_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`thread_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_assigned_to` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `message_threads`
--


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

CREATE TABLE `messages` (
  `message_id` int NOT NULL AUTO_INCREMENT,
  `thread_id` int NOT NULL,
  `sender_type` enum('customer','staff','chatbot','system') DEFAULT 'customer',
  `sender_id` int DEFAULT NULL,
  `sender_phone` varchar(20) DEFAULT NULL,
  `sender_email` varchar(100) DEFAULT NULL,
  `message_text` text NOT NULL,
  `message_type` enum('text','sms','email','in_app','voice') DEFAULT 'text',
  `attachments` json DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `read_at` datetime DEFAULT NULL,
  `status` enum('sent','delivered','read','failed') DEFAULT 'sent',
  `sentiment_score` decimal(3,2) DEFAULT NULL,
  `contains_urgent_keyword` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`message_id`),
  KEY `idx_thread_id` (`thread_id`),
  KEY `idx_sender_id` (`sender_id`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `messages`
--


--
-- Table structure for table `notification_preferences`
--

CREATE TABLE `notification_preferences` (
  `preference_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `notification_type` varchar(100) DEFAULT NULL,
  `channel` enum('email','sms','in_app','push') NOT NULL,
  `is_enabled` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`preference_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `notification_preferences`
--


--
-- Temporary view structure for view `open_support_tickets`
--

/*!50001 DROP VIEW IF EXISTS `open_support_tickets`*/;
/*!50001 CREATE VIEW `open_support_tickets` AS SELECT 
 1 AS `thread_id`,
 1 AS `subject`,
 1 AS `priority`,
 1 AS `first_name`,
 1 AS `last_name`,
 1 AS `email`,
 1 AS `phone`,
 1 AS `last_message_date`,
 1 AS `message_count`*/;

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `order_number` varchar(50) NOT NULL,
  `customer_id` int NOT NULL,
  `subscription_id` int DEFAULT NULL,
  `plan_id` int NOT NULL,
  `order_date` datetime NOT NULL,
  `order_status` enum('pending','confirmed','scheduled','completed','cancelled','refunded') DEFAULT 'pending',
  `scheduled_date` date DEFAULT NULL,
  `completion_date` datetime DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  `tax` decimal(10,2) DEFAULT NULL,
  `discount` decimal(10,2) DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `order_number` (`order_number`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_order_status` (`order_status`),
  KEY `idx_scheduled_date` (`scheduled_date`),
  KEY `idx_completion_date` (`completion_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `orders`
--


--
-- Table structure for table `payment_methods`
--

CREATE TABLE `payment_methods` (
  `payment_method_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `payment_type` enum('credit_card','debit_card','ach','paypal') NOT NULL,
  `card_last_four` varchar(4) DEFAULT NULL,
  `card_brand` varchar(20) DEFAULT NULL,
  `cardholder_name` varchar(100) DEFAULT NULL,
  `expiration_month` int DEFAULT NULL,
  `expiration_year` int DEFAULT NULL,
  `is_default` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `stripe_payment_method_id` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_method_id`),
  UNIQUE KEY `stripe_payment_method_id` (`stripe_payment_method_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_is_default` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `payment_methods`
--


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

CREATE TABLE `payments` (
  `payment_id` int NOT NULL AUTO_INCREMENT,
  `payment_number` varchar(50) NOT NULL,
  `order_id` int DEFAULT NULL,
  `subscription_id` int DEFAULT NULL,
  `customer_id` int NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `tax` decimal(10,2) DEFAULT '0.00',
  `payment_method_id` int DEFAULT NULL,
  `payment_status` enum('pending','processing','completed','failed','refunded') DEFAULT 'pending',
  `transaction_id` varchar(255) DEFAULT NULL,
  `stripe_charge_id` varchar(255) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `payment_date` datetime DEFAULT NULL,
  `refund_date` datetime DEFAULT NULL,
  `refund_reason` varchar(255) DEFAULT NULL,
  `refund_amount` decimal(10,2) DEFAULT NULL,
  `gateway_response` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  UNIQUE KEY `payment_number` (`payment_number`),
  UNIQUE KEY `transaction_id` (`transaction_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_payment_status` (`payment_status`),
  KEY `idx_payment_date` (`payment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `payments`
--


--
-- Table structure for table `promotions`
--

CREATE TABLE `promotions` (
  `promotion_id` int NOT NULL AUTO_INCREMENT,
  `promotion_code` varchar(50) NOT NULL,
  `promotion_name` varchar(100) NOT NULL,
  `description` text,
  `discount_type` enum('percentage','fixed_amount') NOT NULL,
  `discount_value` decimal(8,2) NOT NULL,
  `max_discount_amount` decimal(8,2) DEFAULT NULL,
  `valid_from` date NOT NULL,
  `valid_until` date NOT NULL,
  `max_uses` int DEFAULT NULL,
  `current_uses` int DEFAULT '0',
  `min_order_amount` decimal(8,2) DEFAULT NULL,
  `applicable_plans` json DEFAULT NULL,
  `status` enum('active','inactive','expired') DEFAULT 'active',
  `created_by` 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 (`promotion_id`),
  UNIQUE KEY `promotion_code` (`promotion_code`),
  KEY `idx_promotion_code` (`promotion_code`),
  KEY `idx_status` (`status`),
  KEY `idx_valid_until` (`valid_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `promotions`
--


--
-- Table structure for table `quote_inquiries`
--

CREATE TABLE `quote_inquiries` (
  `inquiry_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `number_of_bins` int NOT NULL DEFAULT '2',
  `message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('new','contacted','qualified','lost','converted') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'new',
  `assigned_to` int DEFAULT NULL,
  `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `contacted_date` datetime DEFAULT NULL,
  PRIMARY KEY (`inquiry_id`),
  KEY `idx_email` (`email`),
  KEY `idx_phone` (`phone`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `quote_inquiries`
--

INSERT INTO `quote_inquiries` VALUES (1,'Dev Test','dev@test.com','(555) 555-1234','456',2,'Development test','127.0.0.1','new',NULL,NULL,'2026-01-10 18:44:47','2026-01-10 18:44:47',NULL),(2,'Sarah Johnson','sarah.j@email.com','+1-813-555-9876','789',4,'Need weekly cleaning for commercial property','127.0.0.1','new',NULL,NULL,'2026-01-10 18:45:01','2026-01-10 18:45:01',NULL),(3,'Address Test','addr@test.com','(555) 111-2222','123456789012345',1,'Address only numbers test','127.0.0.1','new',NULL,NULL,'2026-01-10 18:45:46','2026-01-10 18:45:46',NULL),(4,'Mary Wilson','mary.wilson@example.com','(813) 246-1357','2847',5,'Commercial property needs bi-weekly service','127.0.0.1','new',NULL,NULL,'2026-01-10 18:46:06','2026-01-10 18:46:06',NULL),(5,'DB Test','db@test.com','(555) 888-9999','999 Test Avenue with Spaces, Tampa FL',3,'Direct DB test','127.0.0.1','new',NULL,NULL,'2026-01-10 18:46:20','2026-01-10 18:46:20',NULL),(6,'Post Fix Test','postfix@test.com','(555) 333-4444','888',7,NULL,'127.0.0.1','new',NULL,NULL,'2026-01-10 18:47:04','2026-01-10 18:47:04',NULL),(7,'Debug Test','debug@test.com','(555) 999-1111','999',8,NULL,'127.0.0.1','new',NULL,NULL,'2026-01-10 18:47:24','2026-01-10 18:47:24',NULL),(8,'Trim Test','trim@test.com','(555) 222-3333','777',9,NULL,'127.0.0.1','new',NULL,NULL,'2026-01-10 18:47:49','2026-01-10 18:47:49',NULL),(9,'Reformat Test','reformat@test.com','(555) 444-5555','666',10,NULL,'127.0.0.1','new',NULL,NULL,'2026-01-10 18:48:15','2026-01-10 18:48:15',NULL),(10,'Direct SQL Test','directsql@test.com','(555) 666-7777','555 Direct SQL Street Testing Full Address',11,'','127.0.0.1','new',NULL,NULL,'2026-01-10 18:48:32','2026-01-10 18:48:32',NULL),(11,'Production Test User','prodtest@coastalcommand.com','+1 (813) 555-2467','1234 Final Production Test Lane, Tampa, FL 33612',5,'Ready for production - bins.apocryia.com development site','127.0.0.1','new',NULL,NULL,'2026-01-10 18:49:12','2026-01-10 18:49:12',NULL),(12,'Email Test User','emailtest@example.com','(555) 999-1234','789 Email Test Drive, Testing City, FL 12345',3,'Testing the email notification system','127.0.0.1','new',NULL,NULL,'2026-01-10 18:52:25','2026-01-10 18:52:25',NULL),(13,'Test No Email','noemail@example.com','(555) 555-5555','999 No Email Street, Silent City, FL 99999',2,'','127.0.0.1','new',NULL,NULL,'2026-01-10 18:52:37','2026-01-10 18:52:37',NULL);

--
-- Table structure for table `referrals`
--

CREATE TABLE `referrals` (
  `referral_id` int NOT NULL AUTO_INCREMENT,
  `referrer_customer_id` int NOT NULL,
  `referred_customer_id` int DEFAULT NULL,
  `referred_email` varchar(100) DEFAULT NULL,
  `referred_phone` varchar(20) DEFAULT NULL,
  `referral_code` varchar(50) NOT NULL,
  `referral_status` enum('pending','signup','first_order','completed') DEFAULT 'pending',
  `referrer_reward_amount` decimal(8,2) DEFAULT NULL,
  `referred_reward_amount` decimal(8,2) DEFAULT NULL,
  `referrer_reward_claimed` tinyint(1) DEFAULT '0',
  `referred_reward_claimed` tinyint(1) DEFAULT '0',
  `referrer_reward_claimed_date` datetime DEFAULT NULL,
  `referred_reward_claimed_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`referral_id`),
  UNIQUE KEY `referral_code` (`referral_code`),
  KEY `idx_referrer_customer_id` (`referrer_customer_id`),
  KEY `idx_referral_status` (`referral_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `referrals`
--


--
-- Temporary view structure for view `revenue_summary`
--

/*!50001 DROP VIEW IF EXISTS `revenue_summary`*/;
/*!50001 CREATE VIEW `revenue_summary` AS SELECT 
 1 AS `payment_date`,
 1 AS `total_payments`,
 1 AS `total_amount`,
 1 AS `unique_customers`,
 1 AS `average_payment`*/;

--
-- Table structure for table `reviews`
--

CREATE TABLE `reviews` (
  `review_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `order_id` int DEFAULT NULL,
  `rating` int NOT NULL,
  `review_title` varchar(200) DEFAULT NULL,
  `review_text` text,
  `is_verified_purchase` tinyint(1) DEFAULT '1',
  `is_featured` tinyint(1) DEFAULT '0',
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `response_text` text,
  `response_by` int DEFAULT NULL,
  `response_date` datetime DEFAULT NULL,
  `helpful_count` int DEFAULT '0',
  `unhelpful_count` int DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`review_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_rating` (`rating`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `reviews_chk_1` CHECK (((`rating` >= 1) and (`rating` <= 5)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `reviews`
--


--
-- Table structure for table `route_stops`
--

CREATE TABLE `route_stops` (
  `stop_id` int NOT NULL AUTO_INCREMENT,
  `route_id` int NOT NULL,
  `schedule_id` int NOT NULL,
  `customer_id` int NOT NULL,
  `stop_sequence` int NOT NULL,
  `address` varchar(255) NOT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `estimated_arrival_time` datetime DEFAULT NULL,
  `actual_arrival_time` datetime DEFAULT NULL,
  `estimated_completion_time` datetime DEFAULT NULL,
  `actual_completion_time` datetime DEFAULT NULL,
  `status` enum('pending','in_progress','completed','skipped','cancelled') DEFAULT 'pending',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`stop_id`),
  UNIQUE KEY `uk_route_schedule` (`route_id`,`schedule_id`),
  KEY `idx_route_id` (`route_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_stop_sequence` (`stop_sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `route_stops`
--


--
-- Table structure for table `routes`
--

CREATE TABLE `routes` (
  `route_id` int NOT NULL AUTO_INCREMENT,
  `route_date` date NOT NULL,
  `route_name` varchar(100) DEFAULT NULL,
  `assigned_to` int NOT NULL,
  `zone_id` int DEFAULT NULL,
  `vehicle_id` int DEFAULT NULL,
  `status` enum('pending','in_progress','completed','cancelled') DEFAULT 'pending',
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `total_distance_miles` decimal(8,2) DEFAULT NULL,
  `total_duration_minutes` int DEFAULT NULL,
  `total_stops` int DEFAULT '0',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`route_id`),
  KEY `idx_route_date` (`route_date`),
  KEY `idx_assigned_to` (`assigned_to`),
  KEY `idx_status` (`status`),
  KEY `idx_route_zone` (`zone_id`),
  CONSTRAINT `fk_route_zone` FOREIGN KEY (`zone_id`) REFERENCES `service_zones` (`zone_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `routes`
--

INSERT INTO `routes` VALUES (1,'2026-01-22',NULL,1,NULL,NULL,'in_progress',NULL,NULL,12.50,180,3,NULL,'2026-01-22 23:19:14','2026-01-22 23:19:14'),(2,'2026-01-22',NULL,1,NULL,NULL,'in_progress',NULL,NULL,12.50,180,3,NULL,'2026-01-22 23:19:42','2026-01-22 23:19:42');

--
-- Table structure for table `schedules`
--

CREATE TABLE `schedules` (
  `schedule_id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `customer_id` int NOT NULL,
  `scheduled_date` date NOT NULL,
  `scheduled_time` time NOT NULL,
  `scheduled_datetime` datetime GENERATED ALWAYS AS (concat(`scheduled_date`,_utf8mb4' ',`scheduled_time`)) STORED,
  `time_window_start` time DEFAULT NULL,
  `time_window_end` time DEFAULT NULL,
  `status` enum('pending','confirmed','in_progress','completed','cancelled','rescheduled','no_show') DEFAULT 'pending',
  `actual_completion_time` datetime DEFAULT NULL,
  `duration_minutes` int DEFAULT NULL,
  `assigned_to` int DEFAULT NULL,
  `notes` text,
  `customer_notes` text,
  `special_instructions` text,
  `weather_note` varchar(255) DEFAULT NULL,
  `rescheduled_from` 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 (`schedule_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_scheduled_date` (`scheduled_date`),
  KEY `idx_scheduled_datetime` (`scheduled_datetime`),
  KEY `idx_status` (`status`),
  KEY `idx_assigned_to` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `schedules`
--


--
-- Table structure for table `service_plans`
--

CREATE TABLE `service_plans` (
  `plan_id` int NOT NULL AUTO_INCREMENT,
  `service_id` int NOT NULL,
  `plan_name` varchar(100) NOT NULL,
  `plan_type` enum('one_time','monthly','quarterly','annual') NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `additional_bin_price` decimal(8,2) DEFAULT '0.00',
  `billing_cycle_days` int DEFAULT NULL,
  `description` text,
  `features` json DEFAULT NULL,
  `discount_percentage` decimal(5,2) DEFAULT '0.00',
  `setup_fee` decimal(8,2) DEFAULT '0.00',
  `status` enum('active','inactive','archived') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`plan_id`),
  KEY `idx_service_id` (`service_id`),
  KEY `idx_plan_type` (`plan_type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `service_plans`
--

INSERT INTO `service_plans` VALUES (1,1,'Weekly Bin Cleaning','monthly',59.99,10.00,30,'Weekly professional bin cleaning service - billed monthly',NULL,0.00,0.00,'active','2026-01-23 18:53:59','2026-01-23 18:53:59'),(2,2,'Bi-Weekly Bin Cleaning','monthly',39.99,8.00,30,'Bi-weekly professional bin cleaning service - billed monthly',NULL,0.00,0.00,'active','2026-01-23 18:53:59','2026-01-23 18:53:59'),(3,3,'Monthly Bin Cleaning','monthly',25.00,5.00,30,'Monthly professional bin cleaning service',NULL,0.00,0.00,'active','2026-01-23 18:53:59','2026-01-23 18:53:59'),(4,4,'Quarterly Bin Cleaning','quarterly',69.99,15.00,90,'Quarterly professional bin cleaning service - billed every 3 months',NULL,0.00,0.00,'active','2026-01-23 18:53:59','2026-01-23 18:53:59'),(5,5,'Semi-Annual Bin Cleaning','annual',129.99,25.00,180,'Semi-annual professional bin cleaning service - billed every 6 months',NULL,0.00,0.00,'active','2026-01-23 18:53:59','2026-01-23 18:53:59'),(6,6,'One-Time Deep Clean','one_time',49.99,10.00,NULL,'One-time professional bin deep cleaning',NULL,0.00,0.00,'active','2026-01-23 18:53:59','2026-01-23 18:53:59');

--
-- Table structure for table `service_quality`
--

CREATE TABLE `service_quality` (
  `quality_id` int NOT NULL AUTO_INCREMENT,
  `schedule_id` int NOT NULL,
  `order_id` int NOT NULL,
  `performed_by` int DEFAULT NULL,
  `cleanliness_rating` int DEFAULT NULL,
  `professionalism_rating` int DEFAULT NULL,
  `timeliness_rating` int DEFAULT NULL,
  `customer_satisfaction` int DEFAULT NULL,
  `issues_reported` varchar(255) DEFAULT NULL,
  `follow_up_needed` tinyint(1) DEFAULT '0',
  `photos` json DEFAULT NULL,
  `additional_comments` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`quality_id`),
  KEY `idx_schedule_id` (`schedule_id`),
  KEY `idx_performed_by` (`performed_by`),
  CONSTRAINT `service_quality_chk_1` CHECK (((`cleanliness_rating` >= 1) and (`cleanliness_rating` <= 5))),
  CONSTRAINT `service_quality_chk_2` CHECK (((`professionalism_rating` >= 1) and (`professionalism_rating` <= 5))),
  CONSTRAINT `service_quality_chk_3` CHECK (((`timeliness_rating` >= 1) and (`timeliness_rating` <= 5))),
  CONSTRAINT `service_quality_chk_4` CHECK (((`customer_satisfaction` >= 1) and (`customer_satisfaction` <= 5)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `service_quality`
--


--
-- Table structure for table `service_zones`
--

CREATE TABLE `service_zones` (
  `zone_id` int NOT NULL AUTO_INCREMENT,
  `zone_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `zone_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `zip_codes` json DEFAULT NULL COMMENT 'Array of ZIP codes assigned to this zone',
  `color_hex` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT '#3498db' COMMENT 'Color for map display',
  `description` text COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`zone_id`),
  UNIQUE KEY `unique_zone_name` (`zone_name`),
  UNIQUE KEY `unique_zone_code` (`zone_code`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `service_zones`
--

INSERT INTO `service_zones` VALUES (1,'Fort Walton Beach Central','FWB-C','[\"32547\", \"32548\"]','#3498db','Central Fort Walton Beach area',1,'2026-01-24 11:50:19','2026-01-24 11:50:19'),(2,'Fort Walton Beach North','FWB-N','[\"32578\", \"32579\"]','#2ecc71','North Fort Walton Beach and Shalimar',1,'2026-01-24 11:50:19','2026-01-24 11:50:19'),(3,'Destin East','DST-E','[\"32541\", \"32550\"]','#e74c3c','Eastern Destin area',1,'2026-01-24 11:50:19','2026-01-24 11:50:19'),(4,'Destin West','DST-W','[\"32540\", \"32549\"]','#f39c12','Western Destin and Miramar Beach',1,'2026-01-24 11:50:19','2026-01-24 11:50:19'),(5,'Niceville / Valparaiso','NVL','[\"32578\", \"32580\"]','#9b59b6','Niceville and Valparaiso area',1,'2026-01-24 11:50:19','2026-01-24 11:50:19'),(6,'Crestview','CRV','[\"32536\", \"32539\"]','#1abc9c','Crestview and surrounding area',1,'2026-01-24 11:50:19','2026-01-24 11:50:19');

--
-- Table structure for table `services`
--

CREATE TABLE `services` (
  `service_id` int NOT NULL AUTO_INCREMENT,
  `service_name` varchar(100) NOT NULL,
  `service_type` enum('residential','commercial','one_time') NOT NULL,
  `frequency` varchar(50) DEFAULT NULL,
  `price_per_visit` decimal(10,2) DEFAULT NULL,
  `stripe_price_id` varchar(100) DEFAULT NULL,
  `display_order` int DEFAULT '0',
  `description` text,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`service_id`),
  UNIQUE KEY `uk_service_name` (`service_name`),
  KEY `idx_service_type` (`service_type`),
  KEY `idx_stripe_price` (`stripe_price_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `services`
--

INSERT INTO `services` VALUES (1,'Weekly','residential','Weekly',15.00,NULL,1,NULL,'active','2026-01-22 15:07:35','2026-01-22 15:07:35'),(2,'Bi-Weekly','residential','Bi-Weekly',20.00,NULL,2,NULL,'active','2026-01-22 15:07:35','2026-01-22 15:07:35'),(3,'Monthly','residential','Monthly',25.00,NULL,3,NULL,'active','2026-01-22 15:07:35','2026-01-22 15:07:35'),(4,'Quarterly','residential','Quarterly',35.00,NULL,4,NULL,'active','2026-01-22 15:07:35','2026-01-22 15:07:35'),(5,'Semi-Annual','residential','Semi-Annual',50.00,NULL,5,NULL,'active','2026-01-22 15:07:35','2026-01-22 15:07:35'),(6,'One-Time','one_time','One-Time',49.99,NULL,6,NULL,'active','2026-01-22 15:07:35','2026-01-22 15:07:35'),(7,'Commercial Weekly','commercial','Weekly',40.00,NULL,7,NULL,'active','2026-01-22 15:07:35','2026-01-22 15:07:35'),(8,'Commercial Monthly','commercial','Monthly',150.00,NULL,8,NULL,'active','2026-01-22 15:07:35','2026-01-22 15:07:35');

--
-- Table structure for table `subscriptions`
--

CREATE TABLE `subscriptions` (
  `subscription_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `plan_id` int NOT NULL,
  `subscription_number` varchar(50) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `next_billing_date` date DEFAULT NULL,
  `last_billing_date` date DEFAULT NULL,
  `status` enum('active','paused','cancelled','pending','expired') DEFAULT 'active',
  `cancellation_reason` varchar(255) DEFAULT NULL,
  `cancellation_date` datetime DEFAULT NULL,
  `auto_renew` tinyint(1) DEFAULT '1',
  `payment_method_id` int DEFAULT NULL,
  `total_paid` decimal(10,2) DEFAULT '0.00',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`subscription_id`),
  UNIQUE KEY `subscription_number` (`subscription_number`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_next_billing_date` (`next_billing_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `subscriptions`
--


--
-- Table structure for table `system_config`
--

CREATE TABLE `system_config` (
  `config_id` int NOT NULL AUTO_INCREMENT,
  `config_key` varchar(100) NOT NULL,
  `config_value` longtext,
  `config_type` enum('string','integer','boolean','json') DEFAULT 'string',
  `description` text,
  `is_sensitive` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`config_id`),
  UNIQUE KEY `config_key` (`config_key`),
  KEY `idx_config_key` (`config_key`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `system_config`
--

INSERT INTO `system_config` VALUES (1,'email_notifications_enabled','false','boolean','Enable/disable email notifications for form submissions',0,'2026-01-10 18:51:03','2026-01-10 18:52:33'),(2,'notification_email_address','admin@coastalcommand.com','string','Email address to receive form submission notifications',0,'2026-01-10 18:51:03','2026-01-10 18:51:03'),(3,'email_from_address','noreply@coastalcommand.com','string','From email address for notifications',0,'2026-01-10 18:51:03','2026-01-10 18:51:03'),(4,'email_from_name','Coastal Command Bin Cleaning','string','From name for email notifications',0,'2026-01-10 18:51:03','2026-01-10 18:51:03'),(5,'MAPS_API_TOKEN','AIzaSyD0DcNCOupYVycK4WN8_znKxZkozC3dUG8','string','0',1,'2026-01-22 23:07:43','2026-01-22 23:07:43'),(6,'TWILIO_SID','AC568a057a2c33001dbf6c2a2976a01cce','string','0',1,'2026-01-22 23:07:43','2026-01-22 23:07:43'),(7,'TWILIO_AUTH','3c9ee6ccf820a47d667b9f60c578b55c','string','0',1,'2026-01-22 23:07:43','2026-01-22 23:07:43'),(8,'TWILIO_API_SID','SK23395aa34469000f733185174925d9dd','string','0',1,'2026-01-22 23:07:43','2026-01-22 23:07:43'),(9,'TWILIO_API_SECRET','l2QSrvcHDXKFQzMcyeHw1NnZXHJDGEqJ','string','0',1,'2026-01-22 23:07:43','2026-01-22 23:07:43'),(10,'STRIPE_SECRET_KEY','sk_test_51Sssi013XWsM6TtXBROFwFx5A8EaeseNlnD6qHN3hASuiwtaHcyTXHFV7iIJ35r9ynkWLv7FbJIeA78oi0oYa0ei00t7BMNfN3','string','Stripe Secret Key (sk_test_... or sk_live_...)',1,'2026-01-23 18:53:07','2026-01-23 22:30:37'),(11,'STRIPE_PUBLISHABLE_KEY','pk_test_51Sssi013XWsM6TtXzi3Q9JMqUDZ6wBhPidf9lcm6dkolV72kxTAGl7hnqh0oeJ9cnBbpKtvQB9TdbVoY5w0oPCY90041jRy04i','string','Stripe Publishable Key (pk_test_... or pk_live_...)',0,'2026-01-23 18:53:07','2026-01-23 22:30:37'),(12,'STRIPE_WEBHOOK_SECRET','whsec_xqV9Rydu3cOnGctzPSgwTRaDWQ4LC8SW','string','Stripe Webhook Signing Secret',1,'2026-01-23 18:53:07','2026-01-23 23:11:53'),(13,'STRIPE_MODE','test','string','Stripe mode: test or live',0,'2026-01-23 18:53:07','2026-01-23 18:53:07'),(14,'OSRM_API_URL','https://router.project-osrm.org','string',NULL,0,'2026-01-24 11:35:40','2026-01-24 11:35:40'),(15,'OSRM_ENABLED','true','string',NULL,0,'2026-01-24 11:35:40','2026-01-24 11:35:40'),(16,'coastalcommandfl_KEY','AIzaSyCnzPkndlyofFAMilL8zvVojaHgQrXcgiM','string','Gemini API KEY',0,'2026-01-26 00:29:37','2026-01-26 00:29:37');

--
-- Table structure for table `user_roles`
--

CREATE TABLE `user_roles` (
  `role_id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `permissions` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `user_roles`
--


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

CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `role_id` int NOT NULL,
  `avatar_url` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive','suspended') DEFAULT 'active',
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_email` (`email`),
  KEY `idx_username` (`username`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `users`
--


--
-- Table structure for table `vehicles`
--

CREATE TABLE `vehicles` (
  `vehicle_id` int NOT NULL AUTO_INCREMENT,
  `vehicle_name` varchar(100) NOT NULL,
  `license_plate` varchar(20) NOT NULL,
  `vehicle_type` varchar(50) DEFAULT NULL,
  `make` varchar(50) DEFAULT NULL,
  `model` varchar(50) DEFAULT NULL,
  `year` year DEFAULT NULL,
  `capacity` int DEFAULT NULL,
  `status` enum('active','maintenance','retired') DEFAULT 'active',
  `last_maintenance_date` date DEFAULT NULL,
  `mileage` int DEFAULT NULL,
  `assigned_to` 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 (`vehicle_id`),
  UNIQUE KEY `license_plate` (`license_plate`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `vehicles`
--


--
-- Table structure for table `youtube_videos`
--

CREATE TABLE `youtube_videos` (
  `video_id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `youtube_url` varchar(255) NOT NULL,
  `video_key` varchar(50) DEFAULT NULL,
  `thumbnail_url` varchar(255) DEFAULT NULL,
  `summary` text,
  `category` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `position_order` int DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`video_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `youtube_videos`
--

INSERT INTO `youtube_videos` VALUES (1,'Coastal Command Service Overview','https://www.youtube.com/watch?v=dQw4w9WgXcQ','dQw4w9WgXcQ',NULL,'Learn about our bin cleaning services and how we keep your area clean','Service Overview','active',1,'2026-01-22 16:45:31','2026-01-22 16:45:31'),(2,'Before & After Bin Cleaning','https://www.youtube.com/watch?v=jNQXAC9IVRw','jNQXAC9IVRw',NULL,'See the dramatic difference our professional cleaning makes','Testimonials','active',2,'2026-01-22 16:45:31','2026-01-22 16:45:31');

--
-- Dumping events for database 'coastalcommand'
--

--
-- Dumping routines for database 'coastalcommand'
--

--
-- Current Database: `coastalcommand`
--

USE `coastalcommand`;

--
-- Final view structure for view `active_customers`
--

/*!50001 DROP VIEW IF EXISTS `active_customers`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`coastaladmin`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `active_customers` AS select `c`.`customer_id` AS `customer_id`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`email` AS `email`,`c`.`phone` AS `phone`,`c`.`service_zip` AS `service_zip`,count(distinct `o`.`order_id`) AS `total_orders`,sum(`o`.`total`) AS `total_spent`,max(`o`.`order_date`) AS `last_order_date` from (`customers` `c` left join `orders` `o` on((`c`.`customer_id` = `o`.`customer_id`))) where (`c`.`status` = 'active') group by `c`.`customer_id`,`c`.`first_name`,`c`.`last_name`,`c`.`email`,`c`.`phone`,`c`.`service_zip` */;

--
-- Final view structure for view `open_support_tickets`
--

/*!50001 DROP VIEW IF EXISTS `open_support_tickets`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`coastaladmin`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `open_support_tickets` AS select `t`.`thread_id` AS `thread_id`,`t`.`subject` AS `subject`,`t`.`priority` AS `priority`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`email` AS `email`,`c`.`phone` AS `phone`,`t`.`last_message_date` AS `last_message_date`,count(`m`.`message_id`) AS `message_count` from ((`message_threads` `t` join `customers` `c` on((`t`.`customer_id` = `c`.`customer_id`))) left join `messages` `m` on((`t`.`thread_id` = `m`.`thread_id`))) where (`t`.`status` in ('open','in_progress','waiting_customer')) group by `t`.`thread_id`,`t`.`subject`,`t`.`priority`,`c`.`first_name`,`c`.`last_name`,`c`.`email`,`c`.`phone`,`t`.`last_message_date` order by `t`.`priority` desc,`t`.`last_message_date` desc */;

--
-- Final view structure for view `revenue_summary`
--

/*!50001 DROP VIEW IF EXISTS `revenue_summary`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`coastaladmin`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `revenue_summary` AS select cast(`p`.`payment_date` as date) AS `payment_date`,count(distinct `p`.`payment_id`) AS `total_payments`,sum(`p`.`amount`) AS `total_amount`,count(distinct `p`.`customer_id`) AS `unique_customers`,avg(`p`.`amount`) AS `average_payment` from `payments` `p` where (`p`.`payment_status` = 'completed') group by cast(`p`.`payment_date` as date) */;
/*!50112 PREPARE s FROM @disable_bulk_load */;
/*!50112 EXECUTE s */;
/*!50112 DEALLOCATE PREPARE s */;


