DELIMITER $$
CREATE PROCEDURE sp_GetDentalPatients(
    IN p_SearchTerm NVARCHAR(200),
    IN p_Gender ENUM('Male', 'Female', 'Other'),
    IN p_Status ENUM('Active', 'Inactive'),
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalPages INT
)
BEGIN
    DECLARE offset_val INT;
    SET offset_val = (p_Page - 1) * p_PageSize;
    
    -- محاسبه کل رکوردها
    SELECT COUNT(*) INTO p_TotalRecords
    FROM patients 
    WHERE (p_SearchTerm IS NULL OR 
           first_name LIKE CONCAT('%', p_SearchTerm, '%') OR 
           last_name LIKE CONCAT('%', p_SearchTerm, '%') OR
           national_id LIKE CONCAT('%', p_SearchTerm, '%') OR
           file_number LIKE CONCAT('%', p_SearchTerm, '%') OR
           mobile LIKE CONCAT('%', p_SearchTerm, '%'))
    AND (p_Gender IS NULL OR gender = p_Gender);
    
    SET p_TotalPages = CEIL(p_TotalRecords / p_PageSize);
    
    -- گرفتن داده‌ها
    SELECT 
        patient_id,
        national_id,
        file_number,
        first_name,
        last_name,
        birth_date,
        gender,
        mobile,
        email,
        city,
        dental_anxiety_level,
        TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) as age,
        DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') as created_at,
        (SELECT COUNT(*) FROM dental_visits WHERE patient_id = patients.patient_id) as total_visits,
        (SELECT COUNT(*) FROM treatment_plans WHERE patient_id = patients.patient_id AND status = 'InProgress') as active_treatment_plans,
        (SELECT MAX(visit_date) FROM dental_visits WHERE patient_id = patients.patient_id) as last_visit_date
    FROM patients
    WHERE (p_SearchTerm IS NULL OR 
           first_name LIKE CONCAT('%', p_SearchTerm, '%') OR 
           last_name LIKE CONCAT('%', p_SearchTerm, '%') OR
           national_id LIKE CONCAT('%', p_SearchTerm, '%') OR
           file_number LIKE CONCAT('%', p_SearchTerm, '%') OR
           mobile LIKE CONCAT('%', p_SearchTerm, '%'))
    AND (p_Gender IS NULL OR gender = p_Gender)
    ORDER BY first_name, last_name
    LIMIT p_PageSize OFFSET offset_val;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_GetDentalPatientInfo(IN p_PatientID INT)
BEGIN
    -- اطلاعات اصلی بیمار
    SELECT * FROM patients WHERE patient_id = p_PatientID;
    
    -- بیمه‌های بیمار
    SELECT 
        pi.*,
        i.insurance_name,
        i.insurance_type,
        i.company_name,
        i.max_annual_coverage
    FROM patient_insurances pi
    INNER JOIN insurances i ON pi.insurance_id = i.insurance_id
    WHERE pi.patient_id = p_PatientID
    ORDER BY pi.is_primary DESC;
    
    -- آخرین ویزیت‌ها
    SELECT 
        v.visit_id,
        v.visit_date,
        v.visit_type,
        v.visit_status,
        v.chief_complaint,
        CONCAT(d.first_name, ' ', d.last_name) as dentist_name,
        d.specialization
    FROM dental_visits v
    INNER JOIN dentists d ON v.dentist_id = d.dentist_id
    WHERE v.patient_id = p_PatientID
    ORDER BY v.visit_date DESC
    LIMIT 10;
    
    -- وضعیت دندان‌ها (آخرین معاینه)
    SELECT 
        pt.*,
        t.tooth_number,
        t.tooth_name,
        t.quadrant,
        t.tooth_type
    FROM patient_teeth pt
    INNER JOIN teeth t ON pt.tooth_id = t.tooth_id
    WHERE pt.patient_id = p_PatientID 
    AND pt.visit_id = (
        SELECT MAX(visit_id) 
        FROM dental_visits 
        WHERE patient_id = p_PatientID
    );
    
    -- طرح‌های درمان فعال
    SELECT 
        tp.*,
        CONCAT(d.first_name, ' ', d.last_name) as dentist_name
    FROM treatment_plans tp
    INNER JOIN dentists d ON tp.dentist_id = d.dentist_id
    WHERE tp.patient_id = p_PatientID 
    AND tp.status IN ('Draft', 'Presented', 'Accepted', 'InProgress');
    
    -- آمار مالی
    SELECT 
        COUNT(*) as total_invoices,
        SUM(total_patient_share) as total_charges,
        SUM(paid_amount) as total_paid,
        SUM(remaining_amount) as total_remaining
    FROM invoices 
    WHERE patient_id = p_PatientID;
    
    -- عکس‌های رادیولوژی
    SELECT 
        radiograph_id,
        radiograph_type,
        radiograph_date,
        findings,
        file_name
    FROM dental_radiographs 
    WHERE patient_id = p_PatientID 
    ORDER BY radiograph_date DESC
    LIMIT 5;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_CreateDentalPatient(
    IN p_NationalID VARCHAR(20),
    IN p_FirstName VARCHAR(100),
    IN p_LastName VARCHAR(100),
    IN p_BirthDate DATE,
    IN p_Gender ENUM('Male', 'Female', 'Other'),
    IN p_Mobile VARCHAR(15),
    IN p_Email VARCHAR(100),
    IN p_Address TEXT,
    IN p_City VARCHAR(100),
    IN p_EmergencyContactName VARCHAR(100),
    IN p_EmergencyContactPhone VARCHAR(15),
    IN p_Allergies TEXT,
    IN p_MedicalConditions TEXT,
    IN p_CurrentMedications TEXT,
    IN p_SmokingStatus ENUM('Non-smoker', 'Smoker', 'Ex-smoker'),
    IN p_DentalAnxietyLevel ENUM('None', 'Low', 'Medium', 'High'),
    IN p_ReferredBy VARCHAR(100),
    IN p_Notes TEXT,
    IN p_CreatedBy INT,
    OUT p_PatientID INT,
    OUT p_FileNumber VARCHAR(50),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_FileCounter INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد بیمار';
        SET p_PatientID = -1;
        SET p_FileNumber = NULL;
    END;
    
    START TRANSACTION;
    
    -- بررسی تکراری نبودن کد ملی
    IF EXISTS(SELECT 1 FROM patients WHERE national_id = p_NationalID) THEN
        SET p_ResultMessage = 'کد ملی تکراری است';
        SET p_PatientID = -1;
        SET p_FileNumber = NULL;
        ROLLBACK;
    ELSE
        -- تولید شماره پرونده دندانپزشکی
        SELECT COALESCE(MAX(CAST(SUBSTRING(file_number, 4) AS UNSIGNED)), 0) + 1 INTO v_FileCounter 
        FROM patients 
        WHERE file_number LIKE 'DEN%';
        
        SET p_FileNumber = CONCAT('DEN', LPAD(v_FileCounter, 6, '0'));
        
        -- ایجاد بیمار
        INSERT INTO patients (
            national_id, file_number, first_name, last_name, birth_date, gender, 
            mobile, email, address, city, emergency_contact_name, emergency_contact_phone,
            allergies, medical_conditions, current_medications, smoking_status, 
            dental_anxiety_level, referred_by, notes
        ) VALUES (
            p_NationalID, p_FileNumber, p_FirstName, p_LastName, p_BirthDate, p_Gender,
            p_Mobile, p_Email, p_Address, p_City, p_EmergencyContactName, p_EmergencyContactPhone,
            p_Allergies, p_MedicalConditions, p_CurrentMedications, p_SmokingStatus,
            p_DentalAnxietyLevel, p_ReferredBy, p_Notes
        );
        
        SET p_PatientID = LAST_INSERT_ID();
        
        -- ثبت در لاگ
        INSERT INTO system_logs (user_id, user_type, action, table_name, record_id, description)
        VALUES (p_CreatedBy, 'Staff', 'CREATE', 'patients', p_PatientID, 
                CONCAT('ایجاد بیمار دندانپزشکی جدید: ', p_FirstName, ' ', p_LastName));
        
        SET p_ResultMessage = 'بیمار دندانپزشکی با موفقیت ایجاد شد';
        COMMIT;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_GetDentists(
    IN p_Specialization ENUM('General', 'Orthodontist', 'Periodontist', 'Endodontist', 'OralSurgeon', 'Prosthodontist', 'Pediatric'),
    IN p_IsActive BOOLEAN,
    IN p_SearchTerm NVARCHAR(200),
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalPages INT
)
BEGIN
    DECLARE offset_val INT;
    SET offset_val = (p_Page - 1) * p_PageSize;
    
    SELECT COUNT(*) INTO p_TotalRecords
    FROM dentists 
    WHERE (p_Specialization IS NULL OR specialization = p_Specialization)
    AND (p_IsActive IS NULL OR is_active = p_IsActive)
    AND (p_SearchTerm IS NULL OR 
         first_name LIKE CONCAT('%', p_SearchTerm, '%') OR 
         last_name LIKE CONCAT('%', p_SearchTerm, '%') OR
         medical_system_number LIKE CONCAT('%', p_SearchTerm, '%'));
    
    SET p_TotalPages = CEIL(p_TotalRecords / p_PageSize);
    
    SELECT 
        dentist_id,
        medical_system_number,
        national_id,
        first_name,
        last_name,
        specialization,
        degree,
        license_number,
        mobile,
        email,
        visit_fee,
        is_active,
        hire_date,
        contract_type,
        (SELECT COUNT(*) FROM dental_visits WHERE dentist_id = dentists.dentist_id AND visit_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as monthly_visits,
        (SELECT COUNT(*) FROM appointments WHERE dentist_id = dentists.dentist_id AND status = 'Scheduled') as pending_appointments,
        (SELECT COUNT(*) FROM treatment_plans WHERE dentist_id = dentists.dentist_id AND status = 'InProgress') as active_treatment_plans
    FROM dentists
    WHERE (p_Specialization IS NULL OR specialization = p_Specialization)
    AND (p_IsActive IS NULL OR is_active = p_IsActive)
    AND (p_SearchTerm IS NULL OR 
         first_name LIKE CONCAT('%', p_SearchTerm, '%') OR 
         last_name LIKE CONCAT('%', p_SearchTerm, '%') OR
         medical_system_number LIKE CONCAT('%', p_SearchTerm, '%'))
    ORDER BY first_name, last_name
    LIMIT p_PageSize OFFSET offset_val;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_CreateDentalVisit(
    IN p_PatientID INT,
    IN p_DentistID INT,
    IN p_DentalAssistantID INT,
    IN p_VisitType ENUM('Examination', 'Consultation', 'Treatment', 'Emergency', 'FollowUp', 'Hygiene'),
    IN p_ChiefComplaint TEXT,
    IN p_ClinicalNotes TEXT,
    IN p_Diagnosis TEXT,
    IN p_TreatmentPlan TEXT,
    IN p_AnesthesiaUsed VARCHAR(100),
    IN p_AnesthesiaAmount VARCHAR(50),
    IN p_Complications TEXT,
    IN p_Recommendations TEXT,
    IN p_NextVisitDate DATE,
    IN p_NextVisitPurpose TEXT,
    IN p_CreatedBy INT,
    OUT p_VisitID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ثبت ویزیت دندانپزشکی';
        SET p_VisitID = -1;
    END;
    
    START TRANSACTION;
    
    -- ایجاد ویزیت
    INSERT INTO dental_visits (
        patient_id, dentist_id, dental_assistant_id, visit_type, chief_complaint,
        clinical_notes, diagnosis, treatment_plan, anesthesia_used, anesthesia_amount,
        complications, recommendations, next_visit_date, next_visit_purpose, created_by
    ) VALUES (
        p_PatientID, p_DentistID, p_DentalAssistantID, p_VisitType, p_ChiefComplaint,
        p_ClinicalNotes, p_Diagnosis, p_TreatmentPlan, p_AnesthesiaUsed, p_AnesthesiaAmount,
        p_Complications, p_Recommendations, p_NextVisitDate, p_NextVisitPurpose, p_CreatedBy
    );
    
    SET p_VisitID = LAST_INSERT_ID();
    
    -- ثبت در لاگ
    INSERT INTO system_logs (user_id, user_type, action, table_name, record_id, description)
    VALUES (p_CreatedBy, 'Staff', 'CREATE', 'dental_visits', p_VisitID, 
            CONCAT('ایجاد ویزیت دندانپزشکی برای بیمار ID: ', p_PatientID));
    
    SET p_ResultMessage = 'ویزیت دندانپزشکی با موفقیت ثبت شد';
    COMMIT;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_RecordPatientTeeth(
    IN p_PatientID INT,
    IN p_VisitID INT,
    IN p_TeethData JSON,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_ToothCount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_ToothID INT;
    DECLARE v_ConditionStatus ENUM('Healthy', 'Caries', 'Filled', 'Crowned', 'RootCanal', 'Missing', 'Impacted', 'Fractured', 'Discolored', 'Hypersensitive', 'Other');
    DECLARE v_ConditionDetails TEXT;
    DECLARE v_PocketDepth DECIMAL(4,2);
    DECLARE v_MobilityLevel ENUM('None', 'Grade1', 'Grade2', 'Grade3');
    DECLARE v_GingivalStatus ENUM('Healthy', 'Gingivitis', 'Periodontitis', 'Recession');
    DECLARE v_Notes TEXT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ثبت وضعیت دندان‌ها';
    END;
    
    START TRANSACTION;
    
    -- حذف رکوردهای قبلی برای این ویزیت
    DELETE FROM patient_teeth WHERE patient_id = p_PatientID AND visit_id = p_VisitID;
    
    -- افزودن دندان‌های جدید
    SET v_ToothCount = JSON_LENGTH(p_TeethData);
    SET i = 0;
    
    WHILE i < v_ToothCount DO
        SET v_ToothID = JSON_UNQUOTE(JSON_EXTRACT(p_TeethData, CONCAT('$[', i, '].tooth_id')));
        SET v_ConditionStatus = JSON_UNQUOTE(JSON_EXTRACT(p_TeethData, CONCAT('$[', i, '].condition_status')));
        SET v_ConditionDetails = JSON_UNQUOTE(JSON_EXTRACT(p_TeethData, CONCAT('$[', i, '].condition_details')));
        SET v_PocketDepth = JSON_UNQUOTE(JSON_EXTRACT(p_TeethData, CONCAT('$[', i, '].pocket_depth')));
        SET v_MobilityLevel = JSON_UNQUOTE(JSON_EXTRACT(p_TeethData, CONCAT('$[', i, '].mobility_level')));
        SET v_GingivalStatus = JSON_UNQUOTE(JSON_EXTRACT(p_TeethData, CONCAT('$[', i, '].gingival_status')));
        SET v_Notes = JSON_UNQUOTE(JSON_EXTRACT(p_TeethData, CONCAT('$[', i, '].notes')));
        
        INSERT INTO patient_teeth (
            patient_id, tooth_id, visit_id, condition_status, condition_details,
            pocket_depth, mobility_level, gingival_status, notes
        ) VALUES (
            p_PatientID, v_ToothID, p_VisitID, v_ConditionStatus, v_ConditionDetails,
            v_PocketDepth, v_MobilityLevel, v_GingivalStatus, v_Notes
        );
        
        SET i = i + 1;
    END WHILE;
    
    SET p_ResultMessage = 'وضعیت دندان‌ها با موفقیت ثبت شد';
    COMMIT;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_RecordDentalTreatment(
    IN p_VisitID INT,
    IN p_PatientID INT,
    IN p_DentistID INT,
    IN p_ServiceID INT,
    IN p_ToothID INT,
    IN p_Surface ENUM('Occlusal', 'Mesial', 'Distal', 'Buccal', 'Lingual', 'Labial', 'Palatal', 'Multiple'),
    IN p_MaterialUsed VARCHAR(100),
    IN p_Shade VARCHAR(20),
    IN p_Quantity INT,
    IN p_UnitPrice DECIMAL(12,2),
    IN p_DiscountPercentage DECIMAL(5,2),
    IN p_InsuranceCoverage DECIMAL(12,2),
    IN p_PatientShare DECIMAL(12,2),
    IN p_TotalAmount DECIMAL(12,2),
    IN p_TreatmentNotes TEXT,
    IN p_Complications TEXT,
    IN p_FollowUpRequired BOOLEAN,
    IN p_FollowUpDate DATE,
    OUT p_TreatmentID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ثبت درمان دندانپزشکی';
        SET p_TreatmentID = -1;
    END;
    
    START TRANSACTION;
    
    -- ثبت درمان
    INSERT INTO dental_treatments (
        visit_id, patient_id, dentist_id, service_id, tooth_id, surface,
        material_used, shade, quantity, unit_price, discount_percentage,
        insurance_coverage, patient_share, total_amount, treatment_notes,
        complications, follow_up_required, follow_up_date
    ) VALUES (
        p_VisitID, p_PatientID, p_DentistID, p_ServiceID, p_ToothID, p_Surface,
        p_MaterialUsed, p_Shade, p_Quantity, p_UnitPrice, p_DiscountPercentage,
        p_InsuranceCoverage, p_PatientShare, p_TotalAmount, p_TreatmentNotes,
        p_Complications, p_FollowUpRequired, p_FollowUpDate
    );
    
    SET p_TreatmentID = LAST_INSERT_ID();
    
    SET p_ResultMessage = 'درمان دندانپزشکی با موفقیت ثبت شد';
    COMMIT;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_CreateTreatmentPlan(
    IN p_PatientID INT,
    IN p_DentistID INT,
    IN p_PlanName VARCHAR(200),
    IN p_PlanDescription TEXT,
    IN p_TotalEstimatedCost DECIMAL(12,2),
    IN p_InsuranceEstimatedCoverage DECIMAL(12,2),
    IN p_PatientEstimatedShare DECIMAL(12,2),
    IN p_Priority ENUM('Low', 'Medium', 'High', 'Emergency'),
    IN p_EstimatedCompletionDate DATE,
    IN p_Notes TEXT,
    IN p_PlanItems JSON,
    OUT p_PlanID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_ItemCount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_ServiceID INT;
    DECLARE v_ToothID INT;
    DECLARE v_Surface ENUM('Occlusal', 'Mesial', 'Distal', 'Buccal', 'Lingual', 'Labial', 'Palatal', 'Multiple');
    DECLARE v_EstimatedCost DECIMAL(12,2);
    DECLARE v_InsuranceCoverage DECIMAL(12,2);
    DECLARE v_PatientShare DECIMAL(12,2);
    DECLARE v_SequenceOrder INT;
    DECLARE v_ItemNotes TEXT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد طرح درمان';
        SET p_PlanID = -1;
    END;
    
    START TRANSACTION;
    
    -- ایجاد طرح درمان
    INSERT INTO treatment_plans (
        patient_id, dentist_id, plan_name, plan_description, total_estimated_cost,
        insurance_estimated_coverage, patient_estimated_share, priority,
        estimated_completion_date, notes
    ) VALUES (
        p_PatientID, p_DentistID, p_PlanName, p_PlanDescription, p_TotalEstimatedCost,
        p_InsuranceEstimatedCoverage, p_PatientEstimatedShare, p_Priority,
        p_EstimatedCompletionDate, p_Notes
    );
    
    SET p_PlanID = LAST_INSERT_ID();
    
    -- افزودن آیتم‌های طرح درمان
    SET v_ItemCount = JSON_LENGTH(p_PlanItems);
    SET i = 0;
    
    WHILE i < v_ItemCount DO
        SET v_ServiceID = JSON_UNQUOTE(JSON_EXTRACT(p_PlanItems, CONCAT('$[', i, '].service_id')));
        SET v_ToothID = JSON_UNQUOTE(JSON_EXTRACT(p_PlanItems, CONCAT('$[', i, '].tooth_id')));
        SET v_Surface = JSON_UNQUOTE(JSON_EXTRACT(p_PlanItems, CONCAT('$[', i, '].surface')));
        SET v_EstimatedCost = JSON_UNQUOTE(JSON_EXTRACT(p_PlanItems, CONCAT('$[', i, '].estimated_cost')));
        SET v_InsuranceCoverage = JSON_UNQUOTE(JSON_EXTRACT(p_PlanItems, CONCAT('$[', i, '].insurance_coverage')));
        SET v_PatientShare = JSON_UNQUOTE(JSON_EXTRACT(p_PlanItems, CONCAT('$[', i, '].patient_share')));
        SET v_SequenceOrder = JSON_UNQUOTE(JSON_EXTRACT(p_PlanItems, CONCAT('$[', i, '].sequence_order')));
        SET v_ItemNotes = JSON_UNQUOTE(JSON_EXTRACT(p_PlanItems, CONCAT('$[', i, '].notes')));
        
        INSERT INTO treatment_plan_items (
            plan_id, service_id, tooth_id, surface, estimated_cost,
            insurance_coverage, patient_share, sequence_order, notes
        ) VALUES (
            p_PlanID, v_ServiceID, v_ToothID, v_Surface, v_EstimatedCost,
            v_InsuranceCoverage, v_PatientShare, v_SequenceOrder, v_ItemNotes
        );
        
        SET i = i + 1;
    END WHILE;
    
    SET p_ResultMessage = 'طرح درمان با موفقیت ایجاد شد';
    COMMIT;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_CalculateDentalInsuranceShare(
    IN p_PatientID INT,
    IN p_ServiceID INT,
    OUT p_InsuranceCoverage DECIMAL(12,2),
    OUT p_PatientShare DECIMAL(12,2),
    OUT p_InsuranceName VARCHAR(100),
    OUT p_AnnualUsed DECIMAL(12,2),
    OUT p_AnnualRemaining DECIMAL(12,2),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_BasePrice DECIMAL(12,2);
    DECLARE v_InsuranceID INT;
    DECLARE v_ContractedPrice DECIMAL(12,2);
    DECLARE v_InsuranceShare DECIMAL(5,2);
    DECLARE v_PatientSharePercent DECIMAL(5,2);
    DECLARE v_DiscountPercentage DECIMAL(5,2);
    DECLARE v_MaxAnnualCoverage DECIMAL(12,2);
    DECLARE v_AnnualUsedAmount DECIMAL(12,2);
    
    -- گرفتن بیمه اصلی بیمار
    SELECT pi.insurance_id, i.insurance_name, i.discount_percentage, i.max_annual_coverage,
           pi.annual_used_amount
    INTO v_InsuranceID, p_InsuranceName, v_DiscountPercentage, v_MaxAnnualCoverage, v_AnnualUsedAmount
    FROM patient_insurances pi
    INNER JOIN insurances i ON pi.insurance_id = i.insurance_id
    WHERE pi.patient_id = p_PatientID AND pi.is_primary = TRUE
    AND pi.verification_status = 'Verified'
    AND (pi.expiration_date IS NULL OR pi.expiration_date >= CURDATE());
    
    IF v_InsuranceID IS NULL THEN
        -- بیمار بیمه ندارد
        SELECT base_price INTO v_BasePrice FROM dental_services WHERE service_id = p_ServiceID;
        SET p_InsuranceCoverage = 0;
        SET p_PatientShare = v_BasePrice;
        SET p_InsuranceName = 'بدون بیمه';
        SET p_AnnualUsed = 0;
        SET p_AnnualRemaining = 0;
        SET p_ResultMessage = 'بیمار بیمه فعال دندانپزشکی ندارد';
    ELSE
        -- بررسی قرارداد بیمه
        SELECT contracted_price, insurance_share_percentage, patient_share_percentage
        INTO v_ContractedPrice, v_InsuranceShare, v_PatientSharePercent
        FROM insurance_contracts
        WHERE insurance_id = v_InsuranceID AND service_id = p_ServiceID
        AND is_active = TRUE
        AND (end_date IS NULL OR end_date >= CURDATE());
        
        IF v_ContractedPrice IS NULL THEN
            -- خدمت تحت پوشش بیمه نیست
            SELECT base_price INTO v_BasePrice FROM dental_services WHERE service_id = p_ServiceID;
            SET p_InsuranceCoverage = 0;
            SET p_PatientShare = v_BasePrice;
            SET p_ResultMessage = 'خدمت دندانپزشکی تحت پوشش بیمه نیست';
        ELSE
            -- محاسبه سهم بیمه و بیمار
            SET p_InsuranceCoverage = v_ContractedPrice * (v_InsuranceShare / 100);
            SET p_PatientShare = v_ContractedPrice * (v_PatientSharePercent / 100);
            
            -- بررسی سقف سالانه
            SET p_AnnualUsed = v_AnnualUsedAmount;
            SET p_AnnualRemaining = GREATEST(0, v_MaxAnnualCoverage - v_AnnualUsedAmount);
            
            IF p_InsuranceCoverage > p_AnnualRemaining THEN
                SET p_PatientShare = p_PatientShare + (p_InsuranceCoverage - p_AnnualRemaining);
                SET p_InsuranceCoverage = p_AnnualRemaining;
                SET p_ResultMessage = 'سهم بیمه به دلیل سقف سالانه محدود شد';
            ELSE
                SET p_ResultMessage = 'محاسبه با موفقیت انجام شد';
            END IF;
        END IF;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_CreateDentalInvoice(
    IN p_PatientID INT,
    IN p_VisitID INT,
    IN p_DueDate DATE,
    IN p_Notes TEXT,
    IN p_CreatedBy INT,
    OUT p_InvoiceID INT,
    OUT p_InvoiceNumber VARCHAR(100),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_InvoiceCounter INT;
    DECLARE v_TotalAmount DECIMAL(12,2);
    DECLARE v_TotalDiscount DECIMAL(12,2);
    DECLARE v_TotalInsurance DECIMAL(12,2);
    DECLARE v_TotalPatientShare DECIMAL(12,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد صورتحساب دندانپزشکی';
        SET p_InvoiceID = -1;
        SET p_InvoiceNumber = NULL;
    END;
    
    START TRANSACTION;
    
    -- تولید شماره صورتحساب دندانپزشکی
    SELECT COALESCE(MAX(CAST(SUBSTRING(invoice_number, 4) AS UNSIGNED)), 0) + 1 INTO v_InvoiceCounter 
    FROM invoices 
    WHERE invoice_number LIKE 'DEN%';
    
    SET p_InvoiceNumber = CONCAT('DEN', LPAD(v_InvoiceCounter, 6, '0'));
    
    -- محاسبه مبالغ از درمان‌های ویزیت
    IF p_VisitID IS NOT NULL THEN
        SELECT 
            SUM(total_amount),
            SUM(discount_amount),
            SUM(insurance_coverage),
            SUM(patient_share)
        INTO v_TotalAmount, v_TotalDiscount, v_TotalInsurance, v_TotalPatientShare
        FROM dental_treatments 
        WHERE visit_id = p_VisitID;
    ELSE
        SET v_TotalAmount = 0;
        SET v_TotalDiscount = 0;
        SET v_TotalInsurance = 0;
        SET v_TotalPatientShare = 0;
    END IF;
    
    -- ایجاد صورتحساب
    INSERT INTO invoices (
        patient_id, visit_id, invoice_number, due_date, 
        total_amount, total_discount, total_insurance_coverage,
        total_patient_share, remaining_amount, notes, created_by
    ) VALUES (
        p_PatientID, p_VisitID, p_InvoiceNumber, p_DueDate, 
        v_TotalAmount, v_TotalDiscount, v_TotalInsurance,
        v_TotalPatientShare, v_TotalPatientShare, p_Notes, p_CreatedBy
    );
    
    SET p_InvoiceID = LAST_INSERT_ID();
    
    -- به‌روزرسانی مبلغ استفاده شده از بیمه
    UPDATE patient_insurances 
    SET annual_used_amount = annual_used_amount + v_TotalInsurance
    WHERE patient_id = p_PatientID AND is_primary = TRUE;
    
    SET p_ResultMessage = 'صورتحساب دندانپزشکی با موفقیت ایجاد شد';
    COMMIT;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_RecordDentalRadiograph(
    IN p_PatientID INT,
    IN p_VisitID INT,
    IN p_DentistID INT,
    IN p_RadiographType ENUM('Bitewing', 'Periapical', 'Panoramic', 'Cephalometric', 'CBCT', 'OPG', 'Other'),
    IN p_FilmSize VARCHAR(50),
    IN p_ExposureSettings VARCHAR(100),
    IN p_Findings TEXT,
    IN p_Interpretation TEXT,
    IN p_Recommendations TEXT,
    IN p_FilePath VARCHAR(500),
    IN p_FileName VARCHAR(255),
    IN p_FileSize INT,
    IN p_FileType VARCHAR(50),
    IN p_TakenBy INT,
    OUT p_RadiographID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ثبت عکس رادیولوژی';
        SET p_RadiographID = -1;
    END;
    
    START TRANSACTION;
    
    -- ثبت عکس رادیولوژی
    INSERT INTO dental_radiographs (
        patient_id, visit_id, dentist_id, radiograph_type, film_size,
        exposure_settings, findings, interpretation, recommendations,
        file_path, file_name, file_size, file_type, taken_by
    ) VALUES (
        p_PatientID, p_VisitID, p_DentistID, p_RadiographType, p_FilmSize,
        p_ExposureSettings, p_Findings, p_Interpretation, p_Recommendations,
        p_FilePath, p_FileName, p_FileSize, p_FileType, p_TakenBy
    );
    
    SET p_RadiographID = LAST_INSERT_ID();
    
    SET p_ResultMessage = 'عکس رادیولوژی با موفقیت ثبت شد';
    COMMIT;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_CreateDentalAppointment(
    IN p_PatientID INT,
    IN p_DentistID INT,
    IN p_AppointmentDate DATETIME,
    IN p_DurationMinutes INT,
    IN p_AppointmentType ENUM('Examination', 'Consultation', 'Treatment', 'Emergency', 'FollowUp', 'Hygiene'),
    IN p_Reason TEXT,
    IN p_Notes TEXT,
    IN p_CreatedBy INT,
    OUT p_AppointmentID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_ExistingAppointment INT;
    DECLARE v_DentistSchedule INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ثبت قرارملاقات دندانپزشکی';
        SET p_AppointmentID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی تداخل زمانی با قرارملاقات‌های دیگر
    SELECT COUNT(*) INTO v_ExistingAppointment
    FROM appointments 
    WHERE dentist_id = p_DentistID 
    AND appointment_date = p_AppointmentDate
    AND status IN ('Scheduled', 'Confirmed');
    
    -- بررسی برنامه زمانی دندانپزشک
    SELECT COUNT(*) INTO v_DentistSchedule
    FROM schedules 
    WHERE dentist_id = p_DentistID 
    AND schedule_date = DATE(p_AppointmentDate)
    AND is_working_day = TRUE
    AND TIME(p_AppointmentDate) BETWEEN start_time AND end_time;
    
    IF v_ExistingAppointment > 0 THEN
        SET p_ResultMessage = 'زمان انتخاب شده قبلاً رزرو شده است';
        SET p_AppointmentID = -1;
        ROLLBACK;
    ELSEIF v_DentistSchedule = 0 THEN
        SET p_ResultMessage = 'دندانپزشک در این زمان برنامه کاری ندارد';
        SET p_AppointmentID = -1;
        ROLLBACK;
    ELSE
        -- ایجاد قرارملاقات
        INSERT INTO appointments (
            patient_id, dentist_id, appointment_date, duration_minutes,
            appointment_type, reason, notes, created_by
        ) VALUES (
            p_PatientID, p_DentistID, p_AppointmentDate, p_DurationMinutes,
            p_AppointmentType, p_Reason, p_Notes, p_CreatedBy
        );
        
        SET p_AppointmentID = LAST_INSERT_ID();
        
        SET p_ResultMessage = 'قرارملاقات دندانپزشکی با موفقیت ثبت شد';
        COMMIT;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_GetDentalClinicStatistics(
    IN p_StartDate DATE,
    IN p_EndDate DATE
)
BEGIN
    -- آمار کلی
    SELECT 
        COUNT(*) as total_patients,
        COUNT(CASE WHEN created_at >= p_StartDate AND created_at <= p_EndDate THEN 1 END) as new_patients,
        (SELECT COUNT(*) FROM dental_visits WHERE visit_date BETWEEN p_StartDate AND p_EndDate) as total_visits,
        (SELECT COUNT(*) FROM appointments WHERE appointment_date BETWEEN p_StartDate AND p_EndDate) as total_appointments,
        (SELECT COUNT(*) FROM dental_treatments WHERE treatment_date BETWEEN p_StartDate AND p_EndDate) as total_treatments,
        (SELECT SUM(total_patient_share) FROM invoices WHERE invoice_date BETWEEN p_StartDate AND p_EndDate) as total_revenue,
        (SELECT SUM(paid_amount) FROM payments WHERE payment_date BETWEEN p_StartDate AND p_EndDate) as total_collected,
        (SELECT COUNT(*) FROM dental_radiographs WHERE radiograph_date BETWEEN p_StartDate AND p_EndDate) as total_radiographs
    FROM patients;
    
    -- دندانپزشکان پرکار
    SELECT 
        d.dentist_id,
        CONCAT(d.first_name, ' ', d.last_name) as dentist_name,
        d.specialization,
        COUNT(v.visit_id) as visit_count,
        COUNT(t.treatment_id) as treatment_count,
        SUM(t.total_amount) as total_earnings
    FROM dentists d
    LEFT JOIN dental_visits v ON d.dentist_id = v.dentist_id AND v.visit_date BETWEEN p_StartDate AND p_EndDate
    LEFT JOIN dental_treatments t ON v.visit_id = t.visit_id
    GROUP BY d.dentist_id
    ORDER BY visit_count DESC
    LIMIT 10;
    
    -- خدمات پرطرفدار
    SELECT 
        ds.service_id,
        ds.service_name,
        ds.service_category,
        COUNT(t.service_id) as service_count,
        SUM(t.total_amount) as total_revenue
    FROM dental_services ds
    LEFT JOIN dental_treatments t ON ds.service_id = t.service_id 
    AND t.treatment_date BETWEEN p_StartDate AND p_EndDate
    GROUP BY ds.service_id
    ORDER BY service_count DESC
    LIMIT 10;
    
    -- وضعیت مالی
    SELECT 
        payment_status,
        COUNT(*) as invoice_count,
        SUM(total_patient_share) as total_amount,
        SUM(paid_amount) as paid_amount,
        SUM(remaining_amount) as remaining_amount
    FROM invoices
    WHERE invoice_date BETWEEN p_StartDate AND p_EndDate
    GROUP BY payment_status;
    
    -- وضعیت دندان‌های بیماران (آمار)
    SELECT 
        condition_status,
        COUNT(*) as tooth_count
    FROM patient_teeth pt
    INNER JOIN dental_visits v ON pt.visit_id = v.visit_id
    WHERE v.visit_date BETWEEN p_StartDate AND p_EndDate
    GROUP BY condition_status;
    
    -- انواع ویزیت‌ها
    SELECT 
        visit_type,
        COUNT(*) as visit_count
    FROM dental_visits
    WHERE visit_date BETWEEN p_StartDate AND p_EndDate
    GROUP BY visit_type;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_UpdateDentalInventory(
    IN p_InventoryID INT,
    IN p_QuantityChange INT,
    IN p_UnitCost DECIMAL(12,2),
    IN p_BatchNumber VARCHAR(100),
    IN p_ExpirationDate DATE,
    IN p_ReferenceNumber VARCHAR(100),
    IN p_Notes TEXT,
    IN p_PerformedBy INT,
    OUT p_TransactionID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_NewStock INT;
    DECLARE v_CurrentStock INT;
    DECLARE v_ItemName VARCHAR(200);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در به‌روزرسانی موجودی';
        SET p_TransactionID = -1;
    END;
    
    START TRANSACTION;
    
    -- گرفتن موجودی فعلی و نام آیتم
    SELECT stock_quantity, item_name INTO v_CurrentStock, v_ItemName 
    FROM inventory WHERE inventory_id = p_InventoryID;
    
    IF v_CurrentStock IS NULL THEN
        SET p_ResultMessage = 'آیتم موجودی یافت نشد';
        SET p_TransactionID = -1;
        ROLLBACK;
    ELSE
        SET v_NewStock = v_CurrentStock + p_QuantityChange;
        
        -- بررسی موجودی منفی
        IF v_NewStock < 0 THEN
            SET p_ResultMessage = 'موجودی نمی‌تواند منفی باشد';
            SET p_TransactionID = -1;
            ROLLBACK;
        ELSE
            -- ثبت تراکنش انبار
            INSERT INTO inventory_transactions (
                inventory_id, transaction_type, quantity_change, unit_cost,
                total_cost, batch_number, expiration_date, reference_number, 
                notes, performed_by
            ) VALUES (
                p_InventoryID, 
                CASE WHEN p_QuantityChange > 0 THEN 'Purchase' ELSE 'Usage' END,
                p_QuantityChange, p_UnitCost,
                ABS(p_QuantityChange) * p_UnitCost, p_BatchNumber, p_ExpirationDate, 
                p_ReferenceNumber, p_Notes, p_PerformedBy
            );
            
            SET p_TransactionID = LAST_INSERT_ID();
            
            -- به‌روزرسانی موجودی
            UPDATE inventory 
            SET stock_quantity = v_NewStock,
                last_restocked = CASE WHEN p_QuantityChange > 0 THEN CURDATE() ELSE last_restocked END
            WHERE inventory_id = p_InventoryID;
            
            SET p_ResultMessage = CONCAT('موجودی ', v_ItemName, ' با موفقیت به‌روزرسانی شد');
            COMMIT;
        END IF;
    END IF;
END$$
DELIMITER ;