-- QuantumPay Database Schema
-- Run this SQL to create the database and tables

CREATE DATABASE IF NOT EXISTS kameumfq_newdata CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE kameumfq_newdata;

-- Quotes table
CREATE TABLE IF NOT EXISTS quotes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    service VARCHAR(100) NOT NULL,
    message TEXT,
    status ENUM('pending', 'contacted', 'converted', 'closed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Payments table
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_name VARCHAR(100) NOT NULL,
    plan_price DECIMAL(10, 2) NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(50),
    payment_method ENUM('card', 'mobile_money', 'bank_transfer') NOT NULL,
    transaction_id VARCHAR(255) UNIQUE,
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (customer_email),
    INDEX idx_transaction (transaction_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Services table
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Bank transfers table
CREATE TABLE IF NOT EXISTS bank_transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(50),
    bank_name VARCHAR(255) NOT NULL,
    account_number VARCHAR(100) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    receipt_path VARCHAR(500),
    status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (customer_email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample services
INSERT INTO services (name, category, description, icon) VALUES
('Uniformed Security Officers', 'Security Personnel', 'Professional uniformed officers providing visible deterrence', 'fa-user-shield'),
('Maritime Operations', 'Security Personnel', 'Specialized security for ports and vessels', 'fa-ship'),
('Mobile Patrol', 'Security Personnel', '24/7 mobile patrol units', 'fa-car'),
('Off Duty Police Officers', 'Security Personnel', 'Certified law enforcement officers', 'fa-id-badge'),
('Special Event Security', 'Security Personnel', 'Crowd management and event security', 'fa-calendar-star'),
('Crossing Guards', 'Security Personnel', 'School and pedestrian crossing specialists', 'fa-traffic-light'),
('Investigations & Consulting', 'Security Personnel', 'Professional investigative services', 'fa-search'),
('Video Surveillance', 'Technology Services', 'Advanced video surveillance systems', 'fa-video'),
('Facial Recognition', 'Technology Services', 'AI-powered facial recognition', 'fa-brain'),
('Security Monitoring', 'Technology Services', '24/7 security monitoring center', 'fa-desktop'),
('Loss Prevention', 'Technology Services', 'Retail security and theft prevention', 'fa-shopping-cart'),
('Risk Management', 'Technology Services', 'Comprehensive risk assessment', 'fa-chart-line'),
('Residential Security', 'Industries', 'Home and community security', 'fa-home'),
('Retail Security', 'Industries', 'Retail store protection', 'fa-store'),
('Industrial Safety', 'Industries', 'Manufacturing facility security', 'fa-industry'),
('Commercial Security', 'Industries', 'Office building security', 'fa-building'),
('Campus Safety', 'Industries', 'Educational institution security', 'fa-graduation-cap'),
('Government Facilities', 'Industries', 'Government building security', 'fa-landmark'),
('Cruise Ship Security', 'Industries', 'Maritime passenger vessel security', 'fa-anchor');
