-- Database Schema for Vendor Registration System

-- Main vendors table
CREATE TABLE vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_name VARCHAR(255) NOT NULL,
    business_type VARCHAR(100) NOT NULL,
    registration_number VARCHAR(100) NOT NULL UNIQUE,
    tax_number VARCHAR(100),
    description TEXT,
    
    -- Contact Information
    contact_person VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(50) NOT NULL,
    alternate_phone VARCHAR(50),
    website VARCHAR(255),
    
    -- Address Information
    address TEXT NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20),
    country VARCHAR(100) NOT NULL,
    
    -- Banking Information
    bank_name VARCHAR(255) NOT NULL,
    account_number VARCHAR(100) NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    sort_code VARCHAR(50),
    
    -- Budget and Categories
    budget_category VARCHAR(100) NOT NULL,
    budget_range VARCHAR(100),
    specializations TEXT,
    
    -- Status and Metadata
    status ENUM('pending', 'approved', 'rejected', 'suspended') DEFAULT 'pending',
    approved_by INT,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_vendor_email (email),
    INDEX idx_vendor_status (status),
    INDEX idx_vendor_category (budget_category),
    INDEX idx_vendor_business_type (business_type)
);

-- Vendor service categories (many-to-many relationship)
CREATE TABLE vendor_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT NOT NULL,
    category_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
    UNIQUE KEY unique_vendor_category (vendor_id, category_name),
    INDEX idx_category_name (category_name)
);

-- Vendor documents/attachments
CREATE TABLE vendor_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT NOT NULL,
    document_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    file_type VARCHAR(100) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
    INDEX idx_vendor_docs (vendor_id)
);

-- Activity logs for tracking vendor actions
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_type ENUM('admin', 'user', 'vendor') NOT NULL,
    user_id INT 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_logs (user_type, user_id),
    INDEX idx_action_logs (action),
    INDEX idx_created_logs (created_at)
);

-- Budget categories lookup table
CREATE TABLE budget_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_category_active (is_active)
);

-- Service categories lookup table
CREATE TABLE service_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_service_active (is_active)
);

-- Vendor evaluations/ratings
CREATE TABLE vendor_evaluations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT NOT NULL,
    evaluator_id INT NOT NULL,
    procurement_id INT,
    
    -- Rating categories (1-5 scale)
    quality_rating DECIMAL(2,1),
    delivery_rating DECIMAL(2,1),
    communication_rating DECIMAL(2,1),
    price_rating DECIMAL(2,1),
    overall_rating DECIMAL(2,1),
    
    comments TEXT,
    evaluation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
    INDEX idx_vendor_evaluations (vendor_id),
    INDEX idx_overall_rating (overall_rating)
);

-- Insert default budget categories
INSERT INTO budget_categories (category_name, description) VALUES
('Operations', 'Day-to-day operational expenses'),
('Capital Expenditure', 'Long-term asset purchases'),
('Marketing', 'Marketing and promotional activities'),
('IT Infrastructure', 'Technology and IT equipment'),
('Human Resources', 'HR services and recruitment'),
('Facilities', 'Office space and facility management'),
('Professional Services', 'Consulting and professional services'),
('Research & Development', 'R&D and innovation projects'),
('Emergency', 'Urgent and emergency purchases');

-- Insert default service categories
INSERT INTO service_categories (category_name, description) VALUES
('IT Equipment', 'Computer hardware, software, and IT services'),
('Office Supplies', 'Stationery, office furniture, and general supplies'),
('Maintenance', 'Building and equipment maintenance services'),
('Consulting', 'Business consulting and advisory services'),
('Marketing', 'Marketing, advertising, and promotional services'),
('Construction', 'Construction and renovation services'),
('Professional Services', 'Legal, accounting, and other professional services'),
('Manufacturing', 'Manufacturing and production services');

-- Create a view for vendor summary information
CREATE VIEW vendor_summary AS
SELECT 
    v.id,
    v.vendor_name,
    v.business_type,
    v.email,
    v.phone,
    v.city,
    v.state,
    v.country,
    v.budget_category,
    v.status,
    v.created_at,
    GROUP_CONCAT(vc.category_name SEPARATOR ', ') AS service_categories,
    COUNT(vd.id) AS document_count,
    COALESCE(AVG(ve.overall_rating), 0) AS avg_rating,
    COUNT(ve.id) AS evaluation_count
FROM vendors v
LEFT JOIN vendor_categories vc ON v.id = vc.vendor_id
LEFT JOIN vendor_documents vd ON v.id = vd.vendor_id
LEFT JOIN vendor_evaluations ve ON v.id = ve.vendor_id
GROUP BY v.id;

-- Create indexes for better performance
CREATE INDEX idx_vendor_name_search ON vendors(vendor_name);
CREATE INDEX idx_vendor_created_date ON vendors(created_at);
CREATE INDEX idx_vendor_registration ON vendors(registration_number);

-- Create a stored procedure for vendor approval
DELIMITER //
CREATE PROCEDURE ApproveVendor(
    IN vendor_id INT,
    IN approver_id INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    UPDATE vendors 
    SET status = 'approved',
        approved_by = approver_id,
        approved_at = CURRENT_TIMESTAMP
    WHERE id = vendor_id;
    
    INSERT INTO activity_logs (user_type, user_id, action, description)
    VALUES ('admin', approver_id, 'vendor_approved', CONCAT('Approved vendor ID: ', vendor_id));
    
    COMMIT;
END //
DELIMITER ;

-- Create a stored procedure for vendor search
DELIMITER //
CREATE PROCEDURE SearchVendors(
    IN search_term VARCHAR(255),
    IN category_filter VARCHAR(100),
    IN status_filter VARCHAR(20),
    IN limit_count INT,
    IN offset_count INT
)
BEGIN
    SET @sql = 'SELECT * FROM vendor_summary WHERE 1=1';
    
    IF search_term IS NOT NULL AND search_term != '' THEN
        SET @sql = CONCAT(@sql, ' AND (vendor_name LIKE ''%', search_term, '%'' OR email LIKE ''%', search_term, '%'')');
    END IF;
    
    IF category_filter IS NOT NULL AND category_filter != '' THEN
        SET @sql = CONCAT(@sql, ' AND budget_category = ''', category_filter, '''');
    END IF;
    
    IF status_filter IS NOT NULL AND status_filter != '' THEN
        SET @sql = CONCAT(@sql, ' AND status = ''', status_filter, '''');
    END IF;
    
    SET @sql = CONCAT(@sql, ' ORDER BY created_at DESC');
    
    IF limit_count IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' LIMIT ', limit_count);
    END IF;
    
    IF offset_count IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' OFFSET ', offset_count);
    END IF;
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;