CREATE TABLE patients (
    patient_id INT PRIMARY KEY AUTO_INCREMENT,
    national_id VARCHAR(20) UNIQUE NOT NULL,
    file_number VARCHAR(50) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    birth_date DATE NOT NULL,
    gender ENUM('Male', 'Female', 'Other') NOT NULL,
    phone VARCHAR(15),
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(100),
    address TEXT,
    city VARCHAR(100),
    province VARCHAR(100),
    postal_code VARCHAR(10),
    emergency_contact_name VARCHAR(100),
    emergency_contact_phone VARCHAR(15),
    emergency_contact_relation VARCHAR(50),
    marital_status ENUM('Single', 'Married', 'Divorced', 'Widowed'),
    occupation VARCHAR(100),
    blood_type ENUM('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-'),
    allergies TEXT,
    medical_conditions TEXT,
    current_medications TEXT,
    smoking_status ENUM('Non-smoker', 'Smoker', 'Ex-smoker'),
    dental_anxiety_level ENUM('None', 'Low', 'Medium', 'High'),
    referred_by VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_national_id (national_id),
    INDEX idx_file_number (file_number),
    INDEX idx_full_name (first_name, last_name),
    INDEX idx_mobile (mobile),
    INDEX idx_created_at (created_at)
);

CREATE TABLE dentists (
    dentist_id INT PRIMARY KEY AUTO_INCREMENT,
    medical_system_number VARCHAR(50) UNIQUE NOT NULL,
    national_id VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    specialization ENUM('General', 'Orthodontist', 'Periodontist', 'Endodontist', 'OralSurgeon', 'Prosthodontist', 'Pediatric') NOT NULL,
    degree ENUM('DDS', 'DMD', 'BDS', 'MDS', 'PhD'),
    license_number VARCHAR(100) UNIQUE NOT NULL,
    license_expiry DATE,
    phone VARCHAR(15),
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(100),
    address TEXT,
    office_phone VARCHAR(15),
    office_address TEXT,
    visit_fee DECIMAL(12,2) DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    hire_date DATE,
    contract_type ENUM('FullTime', 'PartTime', 'Contractual', 'Visiting'),
    bank_account_number VARCHAR(50),
    bank_name VARCHAR(100),
    expertise_areas JSON,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_medical_number (medical_system_number),
    INDEX idx_specialization (specialization),
    INDEX idx_license (license_number)
);

CREATE TABLE staff (
    staff_id INT PRIMARY KEY AUTO_INCREMENT,
    national_id VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    position ENUM('Receptionist', 'DentalAssistant', 'DentalHygienist', 'SterilizationTech', 'LabTechnician', 'OfficeManager', 'Accountant', 'IT', 'Other') NOT NULL,
    department VARCHAR(100),
    phone VARCHAR(15),
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(100),
    address TEXT,
    hire_date DATE NOT NULL,
    salary DECIMAL(12,2) DEFAULT 0,
    bank_account_number VARCHAR(50),
    bank_name VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    qualifications TEXT,
    emergency_contact_name VARCHAR(100),
    emergency_contact_phone VARCHAR(15),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_position (position),
    INDEX idx_department (department),
    INDEX idx_active (is_active)
);

CREATE TABLE insurances (
    insurance_id INT PRIMARY KEY AUTO_INCREMENT,
    insurance_name VARCHAR(100) NOT NULL,
    insurance_type ENUM('SocialSecurity', 'Supplementary', 'Military', 'Other') NOT NULL,
    company_name VARCHAR(100),
    contract_number VARCHAR(100),
    contract_start_date DATE,
    contract_end_date DATE,
    discount_percentage DECIMAL(5,2) DEFAULT 0,
    max_annual_coverage DECIMAL(12,2) DEFAULT 0,
    dental_coverage_details JSON,
    contact_person VARCHAR(100),
    contact_phone VARCHAR(15),
    contact_email VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_insurance_name (insurance_name),
    INDEX idx_insurance_type (insurance_type),
    INDEX idx_active (is_active)
);

CREATE TABLE patient_insurances (
    patient_insurance_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    insurance_id INT NOT NULL,
    insurance_number VARCHAR(100) NOT NULL,
    group_number VARCHAR(100),
    relationship ENUM('Self', 'Spouse', 'Child', 'Other'),
    effective_date DATE,
    expiration_date DATE,
    is_primary BOOLEAN DEFAULT TRUE,
    verification_status ENUM('Pending', 'Verified', 'Rejected') DEFAULT 'Pending',
    verified_by INT,
    verified_at TIMESTAMP NULL,
    annual_used_amount DECIMAL(12,2) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (insurance_id) REFERENCES insurances(insurance_id),
    FOREIGN KEY (verified_by) REFERENCES staff(staff_id),
    
    UNIQUE KEY unique_patient_insurance (patient_id, insurance_id),
    INDEX idx_insurance_number (insurance_number),
    INDEX idx_expiration_date (expiration_date),
    INDEX idx_verification_status (verification_status)
);

CREATE TABLE teeth (
    tooth_id INT PRIMARY KEY AUTO_INCREMENT,
    tooth_number TINYINT NOT NULL, -- FDI numbering system (11-18, 21-28, 31-38, 41-48)
    tooth_name VARCHAR(50) NOT NULL,
    tooth_type ENUM('Incisor', 'Canine', 'Premolar', 'Molar', 'Wisdom'),
    quadrant ENUM('UpperRight', 'UpperLeft', 'LowerLeft', 'LowerRight'),
    position ENUM('Anterior', 'Posterior'),
    description TEXT,
    
    UNIQUE KEY unique_tooth_number (tooth_number),
    INDEX idx_quadrant (quadrant),
    INDEX idx_tooth_type (tooth_type)
);

CREATE TABLE dental_visits (
    visit_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    dentist_id INT NOT NULL,
    dental_assistant_id INT,
    visit_date DATETIME NOT NULL,
    visit_type ENUM('Examination', 'Consultation', 'Treatment', 'Emergency', 'FollowUp', 'Hygiene') DEFAULT 'Examination',
    visit_status ENUM('Scheduled', 'InProgress', 'Completed', 'Cancelled', 'NoShow') DEFAULT 'Scheduled',
    chief_complaint TEXT,
    clinical_notes TEXT,
    diagnosis TEXT,
    treatment_plan TEXT,
    next_visit_date DATE,
    next_visit_purpose TEXT,
    anesthesia_used VARCHAR(100),
    anesthesia_amount VARCHAR(50),
    complications TEXT,
    recommendations TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (dentist_id) REFERENCES dentists(dentist_id),
    FOREIGN KEY (dental_assistant_id) REFERENCES staff(staff_id),
    FOREIGN KEY (created_by) REFERENCES staff(staff_id),
    
    INDEX idx_visit_date (visit_date),
    INDEX idx_patient_dentist (patient_id, dentist_id),
    INDEX idx_visit_status (visit_status),
    INDEX idx_visit_type (visit_type)
);

CREATE TABLE patient_teeth (
    patient_tooth_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    tooth_id INT NOT NULL,
    visit_id INT NOT NULL,
    condition_status ENUM('Healthy', 'Caries', 'Filled', 'Crowned', 'RootCanal', 'Missing', 'Impacted', 'Fractured', 'Discolored', 'Hypersensitive', 'Other'),
    condition_details TEXT,
    pocket_depth DECIMAL(4,2), -- in mm
    mobility_level ENUM('None', 'Grade1', 'Grade2', 'Grade3'),
    gingival_status ENUM('Healthy', 'Gingivitis', 'Periodontitis', 'Recession'),
    notes TEXT,
    examined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (tooth_id) REFERENCES teeth(tooth_id),
    FOREIGN KEY (visit_id) REFERENCES dental_visits(visit_id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_patient_tooth_visit (patient_id, tooth_id, visit_id),
    INDEX idx_condition_status (condition_status)
);

CREATE TABLE dental_services (
    service_id INT PRIMARY KEY AUTO_INCREMENT,
    service_code VARCHAR(50) UNIQUE NOT NULL,
    service_name VARCHAR(200) NOT NULL,
    service_category ENUM('Examination', 'Preventive', 'Restorative', 'Endodontic', 'Periodontic', 'Prosthetic', 'Orthodontic', 'OralSurgery', 'Radiology', 'Whitening', 'Other') NOT NULL,
    description TEXT,
    complexity_level ENUM('Simple', 'Moderate', 'Complex'),
    default_duration_minutes INT DEFAULT 30,
    base_price DECIMAL(12,2) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    requires_specialist BOOLEAN DEFAULT FALSE,
    pre_requirements TEXT,
    post_care_instructions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_service_code (service_code),
    INDEX idx_service_category (service_category),
    INDEX idx_complexity (complexity_level)
);

CREATE TABLE dental_treatments (
    treatment_id INT PRIMARY KEY AUTO_INCREMENT,
    visit_id INT NOT NULL,
    patient_id INT NOT NULL,
    dentist_id INT NOT NULL,
    service_id INT NOT NULL,
    tooth_id INT, -- NULL for non-tooth specific treatments
    surface ENUM('Occlusal', 'Mesial', 'Distal', 'Buccal', 'Lingual', 'Labial', 'Palatal', 'Multiple'),
    material_used VARCHAR(100),
    shade VARCHAR(20), -- For restorative materials
    quantity INT DEFAULT 1,
    unit_price DECIMAL(12,2) NOT NULL,
    discount_percentage DECIMAL(5,2) DEFAULT 0,
    discount_amount DECIMAL(12,2) DEFAULT 0,
    insurance_coverage DECIMAL(12,2) DEFAULT 0,
    patient_share DECIMAL(12,2) DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    treatment_date DATETIME NOT NULL,
    treatment_notes TEXT,
    complications TEXT,
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (visit_id) REFERENCES dental_visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (dentist_id) REFERENCES dentists(dentist_id),
    FOREIGN KEY (service_id) REFERENCES dental_services(service_id),
    FOREIGN KEY (tooth_id) REFERENCES teeth(tooth_id),
    
    INDEX idx_treatment_date (treatment_date),
    INDEX idx_tooth_service (tooth_id, service_id),
    INDEX idx_patient_treatment (patient_id, treatment_date)
);

CREATE TABLE dental_radiographs (
    radiograph_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    visit_id INT,
    dentist_id INT NOT NULL,
    radiograph_type ENUM('Bitewing', 'Periapical', 'Panoramic', 'Cephalometric', 'CBCT', 'OPG', 'Other') NOT NULL,
    film_size VARCHAR(50),
    exposure_settings VARCHAR(100),
    radiograph_date DATETIME NOT NULL,
    findings TEXT,
    interpretation TEXT,
    recommendations TEXT,
    file_path VARCHAR(500) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size INT,
    file_type VARCHAR(50),
    is_archived BOOLEAN DEFAULT FALSE,
    taken_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (visit_id) REFERENCES dental_visits(visit_id),
    FOREIGN KEY (dentist_id) REFERENCES dentists(dentist_id),
    FOREIGN KEY (taken_by) REFERENCES staff(staff_id),
    
    INDEX idx_radiograph_date (radiograph_date),
    INDEX idx_radiograph_type (radiograph_type),
    INDEX idx_patient_radiographs (patient_id, radiograph_date)
);

CREATE TABLE dental_images (
    image_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    visit_id INT,
    tooth_id INT,
    image_type ENUM('ClinicalPhoto', 'Intraoral', 'Extraoral', 'Microscopic', 'Other') NOT NULL,
    image_purpose ENUM('Diagnosis', 'TreatmentPlanning', 'BeforeAfter', 'Documentation', 'Education'),
    description TEXT,
    image_date DATETIME NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size INT,
    file_type VARCHAR(50),
    is_archived BOOLEAN DEFAULT FALSE,
    taken_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (visit_id) REFERENCES dental_visits(visit_id),
    FOREIGN KEY (tooth_id) REFERENCES teeth(tooth_id),
    FOREIGN KEY (taken_by) REFERENCES staff(staff_id),
    
    INDEX idx_image_date (image_date),
    INDEX idx_image_type (image_type),
    INDEX idx_patient_images (patient_id, image_date)
);

CREATE TABLE insurance_contracts (
    contract_id INT PRIMARY KEY AUTO_INCREMENT,
    insurance_id INT NOT NULL,
    service_id INT NOT NULL,
    contracted_price DECIMAL(12,2) NOT NULL,
    insurance_share_percentage DECIMAL(5,2) NOT NULL,
    patient_share_percentage DECIMAL(5,2) NOT NULL,
    annual_limit DECIMAL(12,2),
    frequency_limit VARCHAR(100), -- e.g., "2 per year"
    pre_authorization_required BOOLEAN DEFAULT FALSE,
    start_date DATE NOT NULL,
    end_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (insurance_id) REFERENCES insurances(insurance_id),
    FOREIGN KEY (service_id) REFERENCES dental_services(service_id),
    
    UNIQUE KEY unique_insurance_service (insurance_id, service_id),
    INDEX idx_contract_dates (start_date, end_date),
    INDEX idx_active (is_active)
);

CREATE TABLE invoices (
    invoice_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    visit_id INT,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    invoice_date DATETIME NOT NULL,
    due_date DATE NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    total_discount DECIMAL(12,2) DEFAULT 0,
    total_insurance_coverage DECIMAL(12,2) DEFAULT 0,
    total_patient_share DECIMAL(12,2) NOT NULL,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    remaining_amount DECIMAL(12,2) NOT NULL,
    payment_status ENUM('Pending', 'Partial', 'Paid', 'Cancelled') DEFAULT 'Pending',
    insurance_claim_status ENUM('NotSubmitted', 'Submitted', 'Approved', 'Rejected', 'Paid') DEFAULT 'NotSubmitted',
    insurance_claim_number VARCHAR(100),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (visit_id) REFERENCES dental_visits(visit_id),
    FOREIGN KEY (created_by) REFERENCES staff(staff_id),
    
    INDEX idx_invoice_number (invoice_number),
    INDEX idx_invoice_date (invoice_date),
    INDEX idx_payment_status (payment_status),
    INDEX idx_claim_status (insurance_claim_status)
);

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_id INT NOT NULL,
    patient_id INT NOT NULL,
    payment_date DATETIME NOT NULL,
    payment_method ENUM('Cash', 'Card', 'Cheque', 'BankTransfer', 'Online', 'Insurance') NOT NULL,
    payment_amount DECIMAL(12,2) NOT NULL,
    reference_number VARCHAR(100),
    bank_name VARCHAR(100),
    cheque_number VARCHAR(100),
    card_last_four VARCHAR(4),
    insurance_claim_id VARCHAR(100),
    notes TEXT,
    received_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id),
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (received_by) REFERENCES staff(staff_id),
    
    INDEX idx_payment_date (payment_date),
    INDEX idx_payment_method (payment_method),
    INDEX idx_reference (reference_number)
);

CREATE TABLE attendance (
    attendance_id INT PRIMARY KEY AUTO_INCREMENT,
    staff_id INT,
    dentist_id INT,
    attendance_date DATE NOT NULL,
    shift_start TIME,
    shift_end TIME,
    actual_start TIME,
    actual_end TIME,
    status ENUM('Present', 'Absent', 'Late', 'Leave', 'Sick', 'Holiday') DEFAULT 'Present',
    leave_type ENUM('None', 'Annual', 'Sick', 'Maternity', 'Emergency', 'Other'),
    overtime_hours DECIMAL(4,2) DEFAULT 0,
    notes TEXT,
    recorded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
    FOREIGN KEY (dentist_id) REFERENCES dentists(dentist_id),
    FOREIGN KEY (recorded_by) REFERENCES staff(staff_id),
    
    CHECK (staff_id IS NOT NULL OR dentist_id IS NOT NULL),
    
    UNIQUE KEY unique_attendance (COALESCE(staff_id, dentist_id), attendance_date),
    INDEX idx_attendance_date (attendance_date),
    INDEX idx_status (status)
);

CREATE TABLE schedules (
    schedule_id INT PRIMARY KEY AUTO_INCREMENT,
    dentist_id INT NOT NULL,
    schedule_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    slot_duration_minutes INT DEFAULT 30,
    break_start TIME,
    break_end TIME,
    max_patients INT DEFAULT 10,
    is_working_day BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (dentist_id) REFERENCES dentists(dentist_id),
    
    UNIQUE KEY unique_dentist_schedule (dentist_id, schedule_date),
    INDEX idx_schedule_date (schedule_date),
    INDEX idx_dentist_date (dentist_id, schedule_date)
);

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    dentist_id INT NOT NULL,
    appointment_date DATETIME NOT NULL,
    duration_minutes INT DEFAULT 30,
    appointment_type ENUM('Examination', 'Consultation', 'Treatment', 'Emergency', 'FollowUp', 'Hygiene') DEFAULT 'Examination',
    status ENUM('Scheduled', 'Confirmed', 'InProgress', 'Completed', 'Cancelled', 'NoShow') DEFAULT 'Scheduled',
    reason TEXT,
    notes TEXT,
    reminder_sent BOOLEAN DEFAULT FALSE,
    reminder_sent_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (dentist_id) REFERENCES dentists(dentist_id),
    FOREIGN KEY (created_by) REFERENCES staff(staff_id),
    
    INDEX idx_appointment_date (appointment_date),
    INDEX idx_dentist_date (dentist_id, appointment_date),
    INDEX idx_status (status),
    INDEX idx_patient_appointments (patient_id, appointment_date)
);

CREATE TABLE payroll (
    payroll_id INT PRIMARY KEY AUTO_INCREMENT,
    staff_id INT,
    dentist_id INT,
    payroll_date DATE NOT NULL,
    base_salary DECIMAL(12,2) NOT NULL,
    overtime_amount DECIMAL(12,2) DEFAULT 0,
    bonus_amount DECIMAL(12,2) DEFAULT 0,
    commission_amount DECIMAL(12,2) DEFAULT 0,
    deduction_amount DECIMAL(12,2) DEFAULT 0,
    net_salary DECIMAL(12,2) NOT NULL,
    payment_status ENUM('Pending', 'Paid', 'Cancelled') DEFAULT 'Pending',
    paid_date DATE NULL,
    payment_method ENUM('Cash', 'BankTransfer', 'Cheque'),
    bank_reference VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
    FOREIGN KEY (dentist_id) REFERENCES dentists(dentist_id),
    
    CHECK (staff_id IS NOT NULL OR dentist_id IS NOT NULL),
    
    INDEX idx_payroll_date (payroll_date),
    INDEX idx_payment_status (payment_status)
);

CREATE TABLE inventory (
    inventory_id INT PRIMARY KEY AUTO_INCREMENT,
    item_code VARCHAR(50) NOT NULL,
    item_name VARCHAR(200) NOT NULL,
    item_category ENUM('DentalMaterial', 'Medication', 'Disposable', 'Equipment', 'Consumable', 'Other') NOT NULL,
    description TEXT,
    unit VARCHAR(50) NOT NULL,
    unit_cost DECIMAL(12,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    min_stock_level INT DEFAULT 10,
    max_stock_level INT DEFAULT 100,
    supplier VARCHAR(200),
    supplier_contact VARCHAR(100),
    storage_location VARCHAR(100),
    expiration_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    last_restocked DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_item_code (item_code),
    INDEX idx_item_category (item_category),
    INDEX idx_stock_level (stock_quantity),
    INDEX idx_expiration (expiration_date)
);

CREATE TABLE inventory_transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    inventory_id INT NOT NULL,
    transaction_type ENUM('Purchase', 'Usage', 'Adjustment', 'Return', 'Waste', 'Transfer') NOT NULL,
    quantity_change INT NOT NULL,
    unit_cost DECIMAL(12,2) NOT NULL,
    total_cost DECIMAL(12,2) NOT NULL,
    batch_number VARCHAR(100),
    expiration_date DATE,
    reference_number VARCHAR(100),
    notes TEXT,
    performed_by INT,
    transaction_date DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id),
    FOREIGN KEY (performed_by) REFERENCES staff(staff_id),
    
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_batch_number (batch_number)
);

CREATE TABLE clinic_expenses (
    expense_id INT PRIMARY KEY AUTO_INCREMENT,
    expense_date DATE NOT NULL,
    expense_category ENUM('Rent', 'Utilities', 'Salaries', 'Supplies', 'Equipment', 'Maintenance', 'Insurance', 'Marketing', 'ProfessionalFees', 'Other') NOT NULL,
    description VARCHAR(500) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_method ENUM('Cash', 'Card', 'BankTransfer', 'Cheque'),
    reference_number VARCHAR(100),
    vendor VARCHAR(200),
    approved_by INT,
    paid_by INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (approved_by) REFERENCES staff(staff_id),
    FOREIGN KEY (paid_by) REFERENCES staff(staff_id),
    
    INDEX idx_expense_date (expense_date),
    INDEX idx_expense_category (expense_category),
    INDEX idx_vendor (vendor)
);

CREATE TABLE treatment_plans (
    plan_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    dentist_id INT NOT NULL,
    plan_name VARCHAR(200) NOT NULL,
    plan_description TEXT,
    total_estimated_cost DECIMAL(12,2) NOT NULL,
    insurance_estimated_coverage DECIMAL(12,2) DEFAULT 0,
    patient_estimated_share DECIMAL(12,2) NOT NULL,
    status ENUM('Draft', 'Presented', 'Accepted', 'Rejected', 'InProgress', 'Completed') DEFAULT 'Draft',
    priority ENUM('Low', 'Medium', 'High', 'Emergency'),
    estimated_completion_date DATE,
    actual_completion_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (dentist_id) REFERENCES dentists(dentist_id),
    
    INDEX idx_patient_plans (patient_id, status),
    INDEX idx_status (status),
    INDEX idx_priority (priority)
);

CREATE TABLE treatment_plan_items (
    plan_item_id INT PRIMARY KEY AUTO_INCREMENT,
    plan_id INT NOT NULL,
    service_id INT NOT NULL,
    tooth_id INT,
    surface ENUM('Occlusal', 'Mesial', 'Distal', 'Buccal', 'Lingual', 'Labial', 'Palatal', 'Multiple'),
    estimated_cost DECIMAL(12,2) NOT NULL,
    insurance_coverage DECIMAL(12,2) DEFAULT 0,
    patient_share DECIMAL(12,2) NOT NULL,
    sequence_order INT NOT NULL,
    status ENUM('Pending', 'InProgress', 'Completed', 'Cancelled') DEFAULT 'Pending',
    actual_cost DECIMAL(12,2),
    completed_date DATE,
    completed_by INT,
    notes TEXT,
    
    FOREIGN KEY (plan_id) REFERENCES treatment_plans(plan_id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES dental_services(service_id),
    FOREIGN KEY (tooth_id) REFERENCES teeth(tooth_id),
    FOREIGN KEY (completed_by) REFERENCES dentists(dentist_id),
    
    INDEX idx_plan_sequence (plan_id, sequence_order),
    INDEX idx_status (status)
);

CREATE TABLE system_logs (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    user_type ENUM('Dentist', 'Staff', 'Patient') NOT NULL,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_action (user_id, user_type, action),
    INDEX idx_created_at (created_at),
    INDEX idx_table_record (table_name, record_id)
);

-- درج دندان‌ها (سیستم شماره‌گذاری FDI)
INSERT INTO teeth (tooth_number, tooth_name, tooth_type, quadrant, position) VALUES 
-- Upper Right (1)
(11, 'Central Incisor', 'Incisor', 'UpperRight', 'Anterior'),
(12, 'Lateral Incisor', 'Incisor', 'UpperRight', 'Anterior'),
(13, 'Canine', 'Canine', 'UpperRight', 'Anterior'),
(14, 'First Premolar', 'Premolar', 'UpperRight', 'Posterior'),
(15, 'Second Premolar', 'Premolar', 'UpperRight', 'Posterior'),
(16, 'First Molar', 'Molar', 'UpperRight', 'Posterior'),
(17, 'Second Molar', 'Molar', 'UpperRight', 'Posterior'),
(18, 'Third Molar', 'Wisdom', 'UpperRight', 'Posterior'),

-- Upper Left (2)
(21, 'Central Incisor', 'Incisor', 'UpperLeft', 'Anterior'),
(22, 'Lateral Incisor', 'Incisor', 'UpperLeft', 'Anterior'),
(23, 'Canine', 'Canine', 'UpperLeft', 'Anterior'),
(24, 'First Premolar', 'Premolar', 'UpperLeft', 'Posterior'),
(25, 'Second Premolar', 'Premolar', 'UpperLeft', 'Posterior'),
(26, 'First Molar', 'Molar', 'UpperLeft', 'Posterior'),
(27, 'Second Molar', 'Molar', 'UpperLeft', 'Posterior'),
(28, 'Third Molar', 'Wisdom', 'UpperLeft', 'Posterior'),

-- Lower Left (3)
(31, 'Central Incisor', 'Incisor', 'LowerLeft', 'Anterior'),
(32, 'Lateral Incisor', 'Incisor', 'LowerLeft', 'Anterior'),
(33, 'Canine', 'Canine', 'LowerLeft', 'Anterior'),
(34, 'First Premolar', 'Premolar', 'LowerLeft', 'Posterior'),
(35, 'Second Premolar', 'Premolar', 'LowerLeft', 'Posterior'),
(36, 'First Molar', 'Molar', 'LowerLeft', 'Posterior'),
(37, 'Second Molar', 'Molar', 'LowerLeft', 'Posterior'),
(38, 'Third Molar', 'Wisdom', 'LowerLeft', 'Posterior'),

-- Lower Right (4)
(41, 'Central Incisor', 'Incisor', 'LowerRight', 'Anterior'),
(42, 'Lateral Incisor', 'Incisor', 'LowerRight', 'Anterior'),
(43, 'Canine', 'Canine', 'LowerRight', 'Anterior'),
(44, 'First Premolar', 'Premolar', 'LowerRight', 'Posterior'),
(45, 'Second Premolar', 'Premolar', 'LowerRight', 'Posterior'),
(46, 'First Molar', 'Molar', 'LowerRight', 'Posterior'),
(47, 'Second Molar', 'Molar', 'LowerRight', 'Posterior'),
(48, 'Third Molar', 'Wisdom', 'LowerRight', 'Posterior');

-- درج بیمه‌های اصلی دندانپزشکی
INSERT INTO insurances (insurance_name, insurance_type, company_name, discount_percentage, max_annual_coverage) VALUES 
('تأمین اجتماعی', 'SocialSecurity', 'سازمان تأمین اجتماعی', 70.00, 2000000),
('بیمه پارسیان', 'Supplementary', 'بیمه پارسیان', 50.00, 3000000),
('بیمه دی', 'Supplementary', 'بیمه دی', 45.00, 2500000),
('بیمه سرمد', 'Supplementary', 'بیمه سرمد', 55.00, 2800000),
('بیمه نیروهای مسلح', 'Military', 'بیمه نیروهای مسلح', 80.00, 4000000),
('خدمات درمانی', 'SocialSecurity', 'وزارت بهداشت', 75.00, 1500000);

-- درج خدمات دندانپزشکی پایه
INSERT INTO dental_services (service_code, service_name, service_category, complexity_level, base_price, default_duration_minutes) VALUES 
('EXAM001', 'معاینه اولیه', 'Examination', 'Simple', 50000, 30),
('EXAM002', 'معاینه دوره‌ای', 'Examination', 'Simple', 30000, 20),
('RAD001', 'عکس OPG', 'Radiology', 'Simple', 150000, 15),
('RAD002', 'عکس پری اپیکال', 'Radiology', 'Simple', 40000, 10),
('RAD003', 'عکس بایت وینگ', 'Radiology', 'Simple', 60000, 10),
('PREV001', 'جرم‌گیری کامل', 'Preventive', 'Simple', 200000, 45),
('PREV002', 'فلوراید تراپی', 'Preventive', 'Simple', 80000, 20),
('REST001', 'پرکردن آمالگام', 'Restorative', 'Simple', 150000, 30),
('REST002', 'پرکردن کامپوزیت', 'Restorative', 'Moderate', 300000, 45),
('REST003', 'اینله', 'Restorative', 'Complex', 800000, 60),
('REST004', 'اونله', 'Restorative', 'Complex', 1000000, 75),
('ENDO001', 'عصب‌کشی یک کاناله', 'Endodontic', 'Moderate', 600000, 60),
('ENDO002', 'عصب‌کشی دو کاناله', 'Endodontic', 'Complex', 900000, 90),
('ENDO003', 'عصب‌کشی سه کاناله', 'Endodontic', 'Complex', 1200000, 120),
('PROS001', 'کرون PFM', 'Prosthetic', 'Complex', 1500000, 90),
('PROS002', 'کرون زیرکونیا', 'Prosthetic', 'Complex', 2500000, 90),
('PROS003', 'بریج سه واحدی', 'Prosthetic', 'Complex', 4000000, 120),
('PROS004', 'دندان مصنوعی کامل', 'Prosthetic', 'Complex', 5000000, 180),
('SURG001', 'کشیدن دندان ساده', 'OralSurgery', 'Simple', 200000, 30),
('SURG002', 'کشیدن دندان جراحی', 'OralSurgery', 'Complex', 800000, 60),
('ORTH001', 'مشاوره ارتودنسی', 'Orthodontic', 'Simple', 100000, 30),
('WHIT001', 'بلیچینگ مطب', 'Whitening', 'Moderate', 1500000, 90);

-- درج مواد مصرفی پایه
INSERT INTO inventory (item_code, item_name, item_category, unit, unit_cost, stock_quantity, min_stock_level) VALUES 
('MAT001', 'کامپوزیت A2', 'DentalMaterial', 'Unit', 50000, 50, 10),
('MAT002', 'کامپوزیت A3', 'DentalMaterial', 'Unit', 50000, 50, 10),
('MAT003', 'آمالگام', 'DentalMaterial', 'Gram', 20000, 100, 20),
('MAT004', 'گوتا پرکا', 'DentalMaterial', 'Box', 150000, 20, 5),
('MAT005', 'سیمان glass ionomer', 'DentalMaterial', 'Unit', 40000, 30, 5),
('MED001', 'لیدوکائین 2%', 'Medication', 'Ampoule', 15000, 100, 20),
('MED002', 'آرتیکائین', 'Medication', 'Cartridge', 20000, 200, 50),
('DISP001', 'سرسوزن', 'Disposable', 'Box', 80000, 10, 3),
('DISP002', 'دستکش لاتکس', 'Disposable', 'Box', 120000, 20, 5),
('DISP003', 'ماسک جراحی', 'Disposable', 'Box', 50000, 30, 10),
('DISP004', 'لیوان یکبار مصرف', 'Disposable', 'Pack', 30000, 50, 10),
('CONS001', 'فیلم رادیوگرافی', 'Consumable', 'Box', 300000, 5, 2),
('CONS002', 'کیت ایمپلنت', 'Consumable', 'Set', 5000000, 2, 1);

-- ایندکس‌های ترکیبی برای جستجوی سریع
CREATE INDEX idx_patient_visits ON dental_visits(patient_id, visit_date DESC);
CREATE INDEX idx_dentist_visits ON dental_visits(dentist_id, visit_date DESC);
CREATE INDEX idx_patient_treatments ON dental_treatments(patient_id, treatment_date DESC);
CREATE INDEX idx_invoice_patient_status ON invoices(patient_id, payment_status);
CREATE INDEX idx_appointment_dentist_status ON appointments(dentist_id, status, appointment_date);
CREATE INDEX idx_inventory_stock ON inventory(stock_quantity, min_stock_level);
CREATE INDEX idx_insurance_contract_active ON insurance_contracts(is_active, start_date, end_date);
CREATE INDEX idx_treatment_plan_status ON treatment_plans(status, priority);
CREATE INDEX idx_attendance_staff_date ON attendance(COALESCE(staff_id, dentist_id), attendance_date);