-- Views and Stored Procedures for Bank Tools Database

-- ============================================
-- VIEW: Employee Business Cards
-- ============================================
CREATE OR REPLACE VIEW v_employee_cards AS
SELECT 
    bc.id,
    bc.employee_id,
    e.employee_code,
    e.first_name,
    e.last_name,
    bc.employee_name,
    bc.job_title,
    bc.department,
    d.name as department_name,
    bc.email,
    bc.phone,
    bc.mobile,
    bc.address,
    bc.website,
    bc.card_design,
    bc.is_active,
    bc.download_count,
    bc.created_at,
    bc.updated_at
FROM business_cards bc
LEFT JOIN employees e ON bc.employee_id = e.id
LEFT JOIN departments d ON e.department_id = d.id;

-- ============================================
-- VIEW: QR Code Statistics
-- ============================================
CREATE OR REPLACE VIEW v_qr_code_stats AS
SELECT 
    qc.id,
    qc.code,
    qc.filename,
    qc.title,
    qc.category,
    qc.file_type,
    qc.file_size,
    qc.is_active,
    qc.download_count,
    qc.scan_count,
    COUNT(qcs.id) as total_scans,
    MAX(qcs.scanned_at) as last_scanned_at,
    qc.created_at,
    qc.created_by
FROM qr_codes qc
LEFT JOIN qr_code_scans qcs ON qc.id = qcs.qr_code_id
GROUP BY qc.id;

-- ============================================
-- VIEW: Department Statistics
-- ============================================
CREATE OR REPLACE VIEW v_department_stats AS
SELECT 
    d.id,
    d.name,
    d.manager_name,
    COUNT(DISTINCT e.id) as employee_count,
    COUNT(DISTINCT bc.id) as business_card_count,
    d.created_at
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id AND e.status = 'active'
LEFT JOIN business_cards bc ON e.id = bc.employee_id AND bc.is_active = TRUE
GROUP BY d.id;

-- ============================================
-- STORED PROCEDURE: Record QR Code Scan
-- ============================================
DELIMITER //

CREATE PROCEDURE sp_record_qr_scan(
    IN p_qr_code_id INT,
    IN p_ip_address VARCHAR(45),
    IN p_user_agent TEXT,
    IN p_country VARCHAR(100),
    IN p_city VARCHAR(100),
    IN p_device_type VARCHAR(50)
)
BEGIN
    -- Insert scan record
    INSERT INTO qr_code_scans (qr_code_id, ip_address, user_agent, country, city, device_type)
    VALUES (p_qr_code_id, p_ip_address, p_user_agent, p_country, p_city, p_device_type);
    
    -- Update scan count
    UPDATE qr_codes 
    SET scan_count = scan_count + 1 
    WHERE id = p_qr_code_id;
    
    -- Log audit
    INSERT INTO audit_log (table_name, record_id, action, ip_address)
    VALUES ('qr_codes', p_qr_code_id, 'scan', p_ip_address);
END //

DELIMITER ;

-- ============================================
-- STORED PROCEDURE: Increment Download Count
-- ============================================
DELIMITER //

CREATE PROCEDURE sp_increment_download(
    IN p_table_name VARCHAR(100),
    IN p_record_id INT,
    IN p_ip_address VARCHAR(45)
)
BEGIN
    IF p_table_name = 'business_cards' THEN
        UPDATE business_cards 
        SET download_count = download_count + 1 
        WHERE id = p_record_id;
    ELSEIF p_table_name = 'qr_codes' THEN
        UPDATE qr_codes 
        SET download_count = download_count + 1 
        WHERE id = p_record_id;
    END IF;
    
    -- Log audit
    INSERT INTO audit_log (table_name, record_id, action, ip_address)
    VALUES (p_table_name, p_record_id, 'download', p_ip_address);
END //

DELIMITER ;
