-- Migration script to update existing database with new fields
-- Run this if you already have an existing database

USE student_management;

-- Add new columns to students table
ALTER TABLE students 
ADD COLUMN IF NOT EXISTS reference_name VARCHAR(100) NULL AFTER status,
ADD COLUMN IF NOT EXISTS exit_date DATE NULL AFTER reference_name,
ADD COLUMN IF NOT EXISTS document_name ENUM('10th Marksheet', '12th Marksheet', 'Aadhar Card', 'Pan Card', 'Voter ID') NULL AFTER exit_date;

-- Remove fees_deposit column if it exists (we'll calculate from fee_history)
ALTER TABLE students DROP COLUMN IF EXISTS fees_deposit;

-- Add next_payment_date to fee_history table
ALTER TABLE fee_history 
ADD COLUMN IF NOT EXISTS next_payment_date DATE NULL AFTER payment_date;

-- Update existing students to have fees_deposit calculated from fee_history
UPDATE students s 
SET fees_deposit = (
    SELECT COALESCE(SUM(amount), 0) 
    FROM fee_history 
    WHERE student_id = s.id
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_fee_history_student ON fee_history(student_id);
CREATE INDEX IF NOT EXISTS idx_fee_history_date ON fee_history(payment_date);
CREATE INDEX IF NOT EXISTS idx_students_reference ON students(reference_name);
CREATE INDEX IF NOT EXISTS idx_students_document ON students(document_name);
CREATE INDEX IF NOT EXISTS idx_students_exit_date ON students(exit_date);
