-- Leads Management System Database Schema
-- Complete professional leads tracking with campaigns, sources, and sales commissions

-- Lead Sources Table (Facebook, Landing Page, Referral, etc.)
CREATE TABLE IF NOT EXISTS lead_sources (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    color VARCHAR(20),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Lead Campaigns Table (Facebook campaigns, etc.)
CREATE TABLE IF NOT EXISTS lead_campaigns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    campaign_type ENUM('facebook', 'google', 'instagram', 'landing_page', 'referral', 'other') DEFAULT 'other',
    budget DECIMAL(10, 2) DEFAULT 0,
    start_date DATE,
    end_date DATE,
    status ENUM('active', 'paused', 'completed', 'cancelled') DEFAULT 'active',
    lead_source_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_source_id) REFERENCES lead_sources(id) ON DELETE SET NULL
);

-- Main Leads Table
CREATE TABLE IF NOT EXISTS leads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Contact Information
    full_name VARCHAR(200) NOT NULL,
    email VARCHAR(200),
    phone VARCHAR(50),
    company VARCHAR(200),
    position VARCHAR(100),
    
    -- Lead Details
    status ENUM('new', 'contacted', 'qualified', 'proposal', 'negotiation', 'won', 'lost', 'on_hold') DEFAULT 'new',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    lead_source_id INT,
    campaign_id INT,
    
    -- Business Information
    service_interested VARCHAR(200),
    budget_range VARCHAR(100),
    expected_revenue DECIMAL(10, 2) DEFAULT 0,
    probability INT DEFAULT 0 COMMENT 'Conversion probability 0-100%',
    
    -- Additional Data
    notes TEXT,
    custom_fields JSON COMMENT 'Store additional Zapier data',
    
    -- Assignment
    assigned_to INT COMMENT 'Sales staff ID',
    assigned_at TIMESTAMP NULL,
    assigned_by INT COMMENT 'Admin who assigned',
    
    -- Tracking
    last_contact_date TIMESTAMP NULL,
    next_followup_date TIMESTAMP NULL,
    converted_to_client_id INT NULL,
    
    -- Metadata
    ip_address VARCHAR(50),
    user_agent TEXT,
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (lead_source_id) REFERENCES lead_sources(id) ON DELETE SET NULL,
    FOREIGN KEY (campaign_id) REFERENCES lead_campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES staff_members(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_by) REFERENCES admin_users(id) ON DELETE SET NULL,
    FOREIGN KEY (converted_to_client_id) REFERENCES clients(id) ON DELETE SET NULL,
    
    INDEX idx_status (status),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_created_at (created_at),
    INDEX idx_email (email),
    INDEX idx_phone (phone)
);

-- Lead Activities/Follow-ups Table
CREATE TABLE IF NOT EXISTS lead_activities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    lead_id INT NOT NULL,
    staff_id INT,
    activity_type ENUM('call', 'email', 'meeting', 'note', 'status_change', 'assignment', 'proposal_sent', 'other') DEFAULT 'note',
    title VARCHAR(200),
    description TEXT,
    status ENUM('scheduled', 'completed', 'cancelled') DEFAULT 'completed',
    scheduled_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (staff_id) REFERENCES staff_members(id) ON DELETE SET NULL,
    INDEX idx_lead_id (lead_id),
    INDEX idx_scheduled_at (scheduled_at)
);

-- Lead Assignments History
CREATE TABLE IF NOT EXISTS lead_assignments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    lead_id INT NOT NULL,
    from_staff_id INT,
    to_staff_id INT,
    assigned_by INT,
    reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (from_staff_id) REFERENCES staff_members(id) ON DELETE SET NULL,
    FOREIGN KEY (to_staff_id) REFERENCES staff_members(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- Sales Commissions Table
CREATE TABLE IF NOT EXISTS sales_commissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    staff_id INT NOT NULL,
    lead_id INT,
    client_id INT,
    
    -- Commission Details
    commission_type ENUM('lead_conversion', 'deal_value', 'monthly_recurring', 'one_time', 'bonus') DEFAULT 'lead_conversion',
    amount DECIMAL(10, 2) NOT NULL,
    percentage DECIMAL(5, 2),
    base_value DECIMAL(10, 2) COMMENT 'Deal value or revenue amount',
    
    -- Status
    status ENUM('pending', 'approved', 'paid', 'cancelled') DEFAULT 'pending',
    approved_by INT,
    approved_at TIMESTAMP NULL,
    paid_at TIMESTAMP NULL,
    payment_reference VARCHAR(100),
    
    -- Notes
    description TEXT,
    notes TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (staff_id) REFERENCES staff_members(id) ON DELETE CASCADE,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by) REFERENCES admin_users(id) ON DELETE SET NULL,
    
    INDEX idx_staff_id (staff_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Add compensation fields to staff_members table
ALTER TABLE staff_members 
ADD COLUMN compensation_type ENUM('salary', 'commission', 'both') DEFAULT 'salary' AFTER salary,
ADD COLUMN commission_rate DECIMAL(5, 2) DEFAULT 0 COMMENT 'Percentage commission rate',
ADD COLUMN commission_structure JSON COMMENT 'Complex commission rules',
ADD COLUMN is_sales_staff TINYINT(1) DEFAULT 0;

-- Insert default lead sources
INSERT INTO lead_sources (name, description, icon, color) VALUES
('Facebook Ads', 'Leads from Facebook advertising campaigns', 'fab fa-facebook', '#1877f2'),
('Landing Page', 'Direct website landing page submissions', 'fas fa-globe', '#4CAF50'),
('Google Ads', 'Google advertising campaigns', 'fab fa-google', '#ea4335'),
('Instagram', 'Instagram social media leads', 'fab fa-instagram', '#e4405f'),
('LinkedIn', 'LinkedIn professional network', 'fab fa-linkedin', '#0077b5'),
('Referral', 'Customer or partner referrals', 'fas fa-user-friends', '#ff9800'),
('Cold Call', 'Outbound cold calling', 'fas fa-phone', '#607d8b'),
('Email Campaign', 'Email marketing campaigns', 'fas fa-envelope', '#2196f3'),
('Walk-in', 'Direct office visits', 'fas fa-door-open', '#795548'),
('Other', 'Other lead sources', 'fas fa-question-circle', '#9e9e9e');

-- Create indexes for better performance
CREATE INDEX idx_leads_status_assigned ON leads(status, assigned_to);
CREATE INDEX idx_leads_next_followup ON leads(next_followup_date);
CREATE INDEX idx_commissions_staff_status ON sales_commissions(staff_id, status);

-- Create view for lead statistics
CREATE OR REPLACE VIEW lead_statistics AS
SELECT 
    l.assigned_to as staff_id,
    s.full_name as staff_name,
    COUNT(l.id) as total_leads,
    SUM(CASE WHEN l.status = 'new' THEN 1 ELSE 0 END) as new_leads,
    SUM(CASE WHEN l.status = 'contacted' THEN 1 ELSE 0 END) as contacted_leads,
    SUM(CASE WHEN l.status = 'qualified' THEN 1 ELSE 0 END) as qualified_leads,
    SUM(CASE WHEN l.status = 'won' THEN 1 ELSE 0 END) as won_leads,
    SUM(CASE WHEN l.status = 'lost' THEN 1 ELSE 0 END) as lost_leads,
    SUM(l.expected_revenue) as total_pipeline_value,
    SUM(CASE WHEN l.status = 'won' THEN l.expected_revenue ELSE 0 END) as won_value,
    ROUND(AVG(l.probability), 2) as avg_probability,
    SUM(CASE WHEN l.status = 'won' THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(l.id), 0) as conversion_rate
FROM leads l
LEFT JOIN staff_members s ON l.assigned_to = s.id
WHERE l.assigned_to IS NOT NULL
GROUP BY l.assigned_to, s.full_name;

-- Create view for commission summary
CREATE OR REPLACE VIEW commission_summary AS
SELECT 
    sc.staff_id,
    s.full_name as staff_name,
    s.compensation_type,
    COUNT(sc.id) as total_commissions,
    SUM(CASE WHEN sc.status = 'pending' THEN sc.amount ELSE 0 END) as pending_amount,
    SUM(CASE WHEN sc.status = 'approved' THEN sc.amount ELSE 0 END) as approved_amount,
    SUM(CASE WHEN sc.status = 'paid' THEN sc.amount ELSE 0 END) as paid_amount,
    SUM(sc.amount) as total_amount,
    DATE_FORMAT(MAX(sc.paid_at), '%Y-%m') as last_payment_month
FROM sales_commissions sc
LEFT JOIN staff_members s ON sc.staff_id = s.id
GROUP BY sc.staff_id, s.full_name, s.compensation_type;
