-- UPC Services Database Schema
-- Run this in your MySQL client first

CREATE DATABASE IF NOT EXISTS upc_services CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE upc_services;

-- Admins
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin','admin') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Services
CREATE TABLE services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    excerpt TEXT,
    content LONGTEXT,
    icon VARCHAR(100) DEFAULT 'fa-building',
    image VARCHAR(300),
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Property Types
CREATE TABLE property_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE
);

-- Property Statuses
CREATE TABLE property_statuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL  -- For Sale, For Rent, Sold, Off Plan
);

-- Counties (Kenya)
CREATE TABLE counties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Properties
CREATE TABLE properties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    slug VARCHAR(300) NOT NULL UNIQUE,
    description LONGTEXT,
    type_id INT,
    status_id INT,
    county_id INT,
    town VARCHAR(150),
    location_name VARCHAR(300),
    latitude DECIMAL(10,7),
    longitude DECIMAL(10,7),
    price DECIMAL(15,2),
    price_label VARCHAR(50),   -- e.g. "Per Month", "Total Price"
    bedrooms TINYINT,
    bathrooms TINYINT,
    size_sqm DECIMAL(10,2),
    amenities TEXT,            -- JSON array of amenity strings
    is_featured TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (type_id) REFERENCES property_types(id) ON DELETE SET NULL,
    FOREIGN KEY (status_id) REFERENCES property_statuses(id) ON DELETE SET NULL,
    FOREIGN KEY (county_id) REFERENCES counties(id) ON DELETE SET NULL
);

-- Property Images
CREATE TABLE property_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    image_path VARCHAR(300) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
);

-- Blogs
CREATE TABLE blogs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    slug VARCHAR(300) NOT NULL UNIQUE,
    excerpt TEXT,
    content LONGTEXT,
    cover_image VARCHAR(300),
    admin_id INT,
    category VARCHAR(100),
    tags VARCHAR(500),
    is_published TINYINT(1) DEFAULT 0,
    views INT DEFAULT 0,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
);

-- Contact Messages
CREATE TABLE contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(200) NOT NULL,
    phone VARCHAR(30),
    subject VARCHAR(300),
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    ip_address VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Property Enquiries
CREATE TABLE property_enquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(200) NOT NULL,
    phone VARCHAR(30),
    message TEXT,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL
);

-- Site Settings
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================================
-- SEED DATA
-- ============================================================

-- Property Types (Kenya real estate categories)
INSERT INTO property_types (name, slug) VALUES
('Apartment', 'apartment'),
('Bungalow', 'bungalow'),
('Maisonette', 'maisonette'),
('Townhouse', 'townhouse'),
('Villa', 'villa'),
('Bedsitter', 'bedsitter'),
('Studio', 'studio'),
('Penthouse', 'penthouse'),
('Commercial', 'commercial'),
('Land', 'land'),
('Off-Plan', 'off-plan');

-- Property Statuses
INSERT INTO property_statuses (name) VALUES
('For Sale'),
('For Rent'),
('Sold'),
('Off Plan'),
('Let');

-- Kenya Counties (Major ones)
INSERT INTO counties (name) VALUES
('Nairobi'),('Mombasa'),('Kisumu'),('Nakuru'),('Kiambu'),
('Machakos'),('Kajiado'),('Murang\'a'),('Nyeri'),('Meru'),
('Thika'),('Eldoret'),('Malindi'),('Nanyuki'),('Limuru');

-- Services
INSERT INTO services (title, slug, excerpt, content, icon, image, sort_order) VALUES
('Architectural Design', 'architectural-design', 'Innovative designs that transform your vision into landmark spaces.', '<p>Our architectural design team blends functionality with artistry to create spaces that inspire. From concept to completion, we deliver designs that stand the test of time.</p>', 'fa-drafting-compass', 'https://images.unsplash.com/photo-1487958449943-2429e8be8625?w=800', 1),
('Urban Planning', 'urban-planning', 'Strategic land use and development planning for sustainable communities.', '<p>We provide comprehensive urban planning services that shape the growth of cities, towns and communities across Kenya.</p>', 'fa-city', 'https://images.unsplash.com/photo-1486325212027-8081e485255e?w=800', 2),
('Building Construction', 'building-construction', 'Quality construction services delivered on time and within budget.', '<p>UPC Services brings decades of experience in delivering high-quality construction projects across residential and commercial sectors.</p>', 'fa-hard-hat', 'https://images.unsplash.com/photo-1504307651254-35680f356dfd?w=800', 3),
('Environmental Impact Assessment', 'eia', 'Comprehensive EIA services for regulatory compliance and sustainability.', '<p>We help you understand and navigate the environmental implications of your project, ensuring compliance with NEMA requirements.</p>', 'fa-leaf', 'https://images.unsplash.com/photo-1501854140801-50d01698950b?w=800', 4),
('Property Valuation', 'property-valuation', 'Accurate and certified property valuations for all purposes.', '<p>Our certified valuers provide accurate, defensible valuations for sales, purchases, mortgage financing, insurance and legal purposes.</p>', 'fa-chart-line', 'https://images.unsplash.com/photo-1560518883-ce09059eeffa?w=800', 5),
('NCA Services', 'nca-services', 'Expert NCA registration and compliance services for contractors.', '<p>We guide construction professionals through NCA registration, compliance and renewal processes ensuring your business operates legally.</p>', 'fa-certificate', 'https://images.unsplash.com/photo-1450101499163-c8848c66ca85?w=800', 6);

-- Default Settings
INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'UPC Services'),
('tagline', 'Creating Landmark Spaces with Commitment to Quality'),
('phone', '+254 700 817 714'),
('email', 'info@upcservices.co.ke'),
('address', 'Nairobi, Kenya'),
('about_short', 'UPC Services is a leading architectural and urban planning firm in Kenya, delivering world-class spaces with precision and passion.'),
('facebook', '#'),
('twitter', '#'),
('instagram', '#'),
('linkedin', '#'),
('meta_description', 'UPC Services - Premier architectural design, urban planning, property valuation and construction firm in Kenya. View our properties and services.'),
('google_analytics', '');