-- OctaDigital Admin Panel Database Schema
-- Database: octadigital_db

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

-- Services Table
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT NOT NULL,
    price VARCHAR(50) NOT NULL,
    icon VARCHAR(50) NOT NULL,
    color VARCHAR(20) NOT NULL,
    category ENUM('Website Development', 'Mobile App Design', 'UI/UX Design', 'Digital Marketing', 'E-Commerce Solutions', 'AI Solutions', 'SEO Services', 'Software Solutions') NOT NULL,
    features TEXT NOT NULL, -- JSON array of features
    is_active TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Projects Table
CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    category VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    client_name VARCHAR(100),
    project_url VARCHAR(255),
    image_url VARCHAR(255),
    gallery TEXT, -- JSON array of images
    technologies TEXT NOT NULL, -- JSON array of tech tags
    color VARCHAR(20) NOT NULL,
    status ENUM('completed', 'in_progress', 'upcoming') DEFAULT 'completed',
    completion_date DATE,
    is_featured TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Client Projects Table
CREATE TABLE IF NOT EXISTS client_projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    project_id INT NOT NULL,
    project_name VARCHAR(150) NOT NULL,
    service_type VARCHAR(100) NOT NULL,
    budget DECIMAL(12, 2),
    status ENUM('planning', 'in_progress', 'review', 'completed', 'on_hold', 'cancelled') DEFAULT 'planning',
    start_date DATE,
    deadline DATE,
    completion_date DATE,
    progress INT DEFAULT 0,
    description TEXT,
    files TEXT, -- JSON array of file paths
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client (client_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Time Tracking Table
CREATE TABLE IF NOT EXISTS time_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    clock_in DATETIME NOT NULL,
    clock_out DATETIME,
    total_hours DECIMAL(5, 2),
    notes TEXT,
    location VARCHAR(100),
    status ENUM('active', 'completed') DEFAULT 'active',
    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,
    INDEX idx_staff (staff_id),
    INDEX idx_date (clock_in)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Leave Requests Table
CREATE TABLE IF NOT EXISTS leave_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    leave_type ENUM('vacation', 'sick', 'personal', 'emergency', 'unpaid') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_days INT NOT NULL,
    reason TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    reviewed_by INT,
    reviewed_at TIMESTAMP NULL,
    review_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 (reviewed_by) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_staff (staff_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Documents Table
CREATE TABLE IF NOT EXISTS documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_size INT,
    file_type VARCHAR(50),
    category ENUM('staff', 'client', 'project', 'company', 'other') NOT NULL,
    related_id INT, -- staff_id, client_id, or project_id
    uploaded_by INT NOT NULL,
    is_private TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_category (category),
    INDEX idx_related (related_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Salary Payments Table
CREATE TABLE IF NOT EXISTS salary_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    payment_date DATE NOT NULL,
    basic_salary DECIMAL(10, 2) NOT NULL,
    bonus DECIMAL(10, 2) DEFAULT 0,
    deductions DECIMAL(10, 2) DEFAULT 0,
    net_salary DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('bank_transfer', 'cash', 'check') DEFAULT 'bank_transfer',
    status ENUM('pending', 'paid', 'cancelled') DEFAULT 'pending',
    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,
    INDEX idx_staff (staff_id),
    INDEX idx_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contact Inquiries Table
CREATE TABLE IF NOT EXISTS contact_inquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    service VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('new', 'in_progress', 'contacted', 'converted', 'closed') DEFAULT 'new',
    assigned_to INT,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_to) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activity Logs Table
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    user_type ENUM('admin', 'staff', 'client') NOT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id, user_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings Table
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type ENUM('text', 'number', 'boolean', 'json') DEFAULT 'text',
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert Default Settings
INSERT INTO settings (setting_key, setting_value, setting_type, description) VALUES
('company_name', 'OctaDigital', 'text', 'Company Name'),
('company_email', 'info@octadigital.io', 'text', 'Company Email'),
('company_phone', '+1234567890', 'text', 'Company Phone'),
('company_address', '123 Digital Street, Tech City', 'text', 'Company Address'),
('company_website', 'https://octadigital.io', 'text', 'Company Website'),
('timezone', 'UTC', 'text', 'System Timezone'),
('currency', 'USD', 'text', 'Default Currency'),
('date_format', 'Y-m-d', 'text', 'Date Format');

-- Insert Sample Services
INSERT INTO services (title, slug, description, price, icon, color, category, features, display_order) VALUES
('Website Development', 'website-development', 'Build modern, responsive, high-performance websites', 'Starting from $2,999', 'web', '#EC4899', 'Website Development', '["Responsive Design", "E-Commerce Integration", "CMS Development", "Progressive Web Apps", "API Integration", "Performance Optimization"]', 1),
('Mobile App Design', 'mobile-app-design', 'Create stunning iOS and Android applications', 'Starting from $4,999', 'phone_iphone', '#8B5CF6', 'Mobile App Design', '["iOS Development", "Android Development", "Cross-Platform Apps", "UI/UX Design", "App Store Deployment", "Maintenance & Support"]', 2),
('UI/UX Design', 'ui-ux-design', 'Design beautiful and intuitive interfaces', 'Starting from $1,999', 'design_services', '#3B82F6', 'UI/UX Design', '["User Research", "Wireframing", "Prototyping", "Visual Design", "Usability Testing", "Design Systems"]', 3),
('Digital Marketing', 'digital-marketing', 'Grow business with data-driven marketing strategies', 'Starting from $999/month', 'trending_up', '#10B981', 'Digital Marketing', '["Social Media Marketing", "Content Marketing", "Email Campaigns", "PPC Advertising", "Analytics & Reporting", "Brand Strategy"]', 4),
('E-Commerce Solutions', 'e-commerce-solutions', 'Launch online store with powerful features', 'Starting from $3,499', 'shopping_cart', '#F59E0B', 'E-Commerce Solutions', '["Store Setup", "Payment Integration", "Inventory Management", "Order Processing", "Customer Portal", "Analytics Dashboard"]', 5),
('AI Solutions', 'ai-solutions', 'Leverage AI to automate and optimize processes', 'Custom Pricing', 'psychology', '#6366F1', 'AI Solutions', '["Machine Learning", "Natural Language Processing", "Computer Vision", "Chatbots & Virtual Assistants", "Predictive Analytics", "AI Integration"]', 6),
('SEO Services', 'seo-services', 'Improve search engine rankings and drive traffic', 'Starting from $799/month', 'search', '#14B8A6', 'SEO Services', '["Keyword Research", "On-Page SEO", "Technical SEO", "Link Building", "Local SEO", "SEO Audits"]', 7),
('Software Solutions', 'software-solutions', 'Custom software development for specific requirements', 'Custom Pricing', 'code', '#EF4444', 'Software Solutions', '["Custom Software", "Cloud Solutions", "Database Design", "System Integration", "API Development", "Legacy Modernization"]', 8);
