-- Roofing Nation LLC - Lead Management Database Schema
-- Run this SQL in phpMyAdmin to create the leads table

-- Create leads table
CREATE TABLE IF NOT EXISTS `leads` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `address` VARCHAR(500) DEFAULT NULL,
  `service` VARCHAR(100) DEFAULT NULL,
  `reason` VARCHAR(100) DEFAULT NULL,
  `subject` VARCHAR(200) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `origin` VARCHAR(255) NOT NULL COMMENT 'Form identifier or URL path',
  `coordinates_lat` DECIMAL(10, 8) DEFAULT NULL,
  `coordinates_lng` DECIMAL(11, 8) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `status` ENUM('new', 'contacted', 'qualified', 'closed', 'spam') DEFAULT 'new',
  `notes` TEXT DEFAULT NULL COMMENT 'Internal notes for sales team',
  `assigned_to` VARCHAR(100) DEFAULT NULL COMMENT 'Sales rep assigned',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  -- Indexes for better query performance
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_status` (`status`),
  INDEX `idx_origin` (`origin`),
  INDEX `idx_email` (`email`),
  INDEX `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Example query to view recent leads
-- SELECT * FROM leads ORDER BY created_at DESC LIMIT 50;

-- Example query to view leads by status
-- SELECT * FROM leads WHERE status = 'new' ORDER BY created_at DESC;

-- Example query to count leads by origin
-- SELECT origin, COUNT(*) as count FROM leads GROUP BY origin ORDER BY count DESC;

-- Example query to update lead status
-- UPDATE leads SET status = 'contacted', notes = 'Called on 2024-01-15' WHERE id = 1;
