<?php
/**
 * Result Management System - Database Schema Setup
 * BS Public School - bspspublicschool.in
 * Auto-creates all required tables for Result Management
 */

function setupResultTables($conn) {

    // 1. Academic Sessions
    $conn->query("CREATE TABLE IF NOT EXISTS result_sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        session_name VARCHAR(20) NOT NULL COMMENT '2025-2026',
        start_date DATE,
        end_date DATE,
        is_active TINYINT(1) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // Insert default session
    $chk = $conn->query("SELECT id FROM result_sessions LIMIT 1");
    if ($chk && $chk->num_rows == 0) {
        $conn->query("INSERT INTO result_sessions (session_name, start_date, end_date, is_active) VALUES ('2025-2026','2025-04-01','2026-03-31',1)");
    }

    // 2. Classes & Sections
    $conn->query("CREATE TABLE IF NOT EXISTS result_classes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        session_id INT NOT NULL,
        class_name VARCHAR(30) NOT NULL COMMENT 'Nursery, LKG, UKG, Class 1 etc',
        numeric_level TINYINT NOT NULL COMMENT '0=Nur,1=LKG,2=UKG,3=Cls1..11=Cls8',
        section VARCHAR(5) DEFAULT 'A',
        class_teacher VARCHAR(100),
        FOREIGN KEY (session_id) REFERENCES result_sessions(id) ON DELETE CASCADE
    )");

    $cc = $conn->query("SELECT id FROM result_classes LIMIT 1");
    if ($cc && $cc->num_rows == 0) {
        $sid = $conn->query("SELECT id FROM result_sessions WHERE is_active=1 LIMIT 1")->fetch_assoc()['id'];
        $classes = [
            [0,'Nursery','A'],[1,'LKG','A'],[2,'UKG','A'],
            [3,'Class 1','A'],[3,'Class 1','B'],
            [4,'Class 2','A'],[4,'Class 2','B'],
            [5,'Class 3','A'],[6,'Class 4','A'],[7,'Class 5','A'],
            [8,'Class 6','A'],[9,'Class 7','A'],[10,'Class 8','A']
        ];
        $st = $conn->prepare("INSERT INTO result_classes (session_id,numeric_level,class_name,section) VALUES (?,?,?,?)");
        foreach($classes as $c) {
            $st->bind_param("iiss",$sid,$c[0],$c[1],$c[2]);
            $st->execute();
        }
    }

    // 3. Students
    $conn->query("CREATE TABLE IF NOT EXISTS result_students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        admission_no VARCHAR(30) NOT NULL UNIQUE,
        roll_no INT,
        first_name VARCHAR(100) NOT NULL,
        last_name VARCHAR(100) DEFAULT '',
        dob DATE,
        gender ENUM('Male','Female','Other') DEFAULT 'Male',
        father_name VARCHAR(150),
        mother_name VARCHAR(150),
        parent_mobile VARCHAR(15),
        photo VARCHAR(255) DEFAULT '',
        class_id INT NOT NULL,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (class_id) REFERENCES result_classes(id) ON DELETE CASCADE
    )");

    // 4. Subjects Master
    $conn->query("CREATE TABLE IF NOT EXISTS result_subjects (
        id INT AUTO_INCREMENT PRIMARY KEY,
        subject_name VARCHAR(100) NOT NULL,
        subject_code VARCHAR(20) NOT NULL,
        is_scholastic TINYINT(1) DEFAULT 1 COMMENT '0=co-scholastic',
        grading_type ENUM('NUMERIC','GRADE') DEFAULT 'NUMERIC',
        applicable_from TINYINT DEFAULT 0 COMMENT 'numeric_level from',
        applicable_to TINYINT DEFAULT 10,
        is_active TINYINT(1) DEFAULT 1
    )");

    $sc = $conn->query("SELECT id FROM result_subjects LIMIT 1");
    if ($sc && $sc->num_rows == 0) {
        $subjects = [
            // Pre-Primary (0-2)
            ['Rhymes & Oral Work','RHY','GRADE',0,2],
            ['Drawing & Art','ART1','GRADE',0,2],
            ['English (Oral)','ENO','GRADE',0,2],
            ['English (Written)','ENW','GRADE',0,2],
            ['Maths (Oral)','MTO','GRADE',0,2],
            ['Maths (Written)','MTW','GRADE',0,2],
            ['Cleanliness & Etiquette','CLN','GRADE',0,2],
            // Primary (3-7)
            ['English','ENG','NUMERIC',3,10],
            ['Hindi','HIN','NUMERIC',3,10],
            ['Mathematics','MATH','NUMERIC',3,10],
            ['Environmental Studies','EVS','NUMERIC',3,5],
            ['Science','SCI','NUMERIC',6,10],
            ['Social Studies','SST','NUMERIC',3,7],
            ['Social Science','SOC','NUMERIC',8,10],
            ['General Knowledge','GK','NUMERIC',3,7],
            ['Computer','COMP','NUMERIC',3,10],
            ['Art & Craft','ART','NUMERIC',3,7],
            ['Sanskrit','SAN','NUMERIC',6,10],
            // Co-Scholastic
            ['Art Education','ARTE','GRADE',3,10],
            ['Health & Physical Education','PHY','GRADE',3,10],
            ['Work Education','WRK','GRADE',3,10],
            ['Discipline','DISC','GRADE',3,10],
        ];
        $ss = $conn->prepare("INSERT INTO result_subjects (subject_name,subject_code,grading_type,applicable_from,applicable_to) VALUES (?,?,?,?,?)");
        foreach($subjects as $s) {
            $ss->bind_param("sssii",$s[0],$s[1],$s[2],$s[3],$s[4]);
            $ss->execute();
        }
    }

    // 5. Class-Subject Mapping
    $conn->query("CREATE TABLE IF NOT EXISTS result_class_subject (
        id INT AUTO_INCREMENT PRIMARY KEY,
        class_id INT NOT NULL,
        subject_id INT NOT NULL,
        teacher_name VARCHAR(100),
        max_marks INT DEFAULT 100,
        pass_marks INT DEFAULT 33,
        is_optional TINYINT(1) DEFAULT 0,
        UNIQUE KEY uniq_cs (class_id, subject_id),
        FOREIGN KEY (class_id) REFERENCES result_classes(id) ON DELETE CASCADE,
        FOREIGN KEY (subject_id) REFERENCES result_subjects(id) ON DELETE CASCADE
    )");

    // 6. Terms
    $conn->query("CREATE TABLE IF NOT EXISTS result_terms (
        id INT AUTO_INCREMENT PRIMARY KEY,
        session_id INT NOT NULL,
        term_name VARCHAR(50) NOT NULL COMMENT 'Term 1, Term 2, Half Yearly, Annual',
        term_code VARCHAR(20) NOT NULL,
        display_order INT DEFAULT 1,
        is_active TINYINT(1) DEFAULT 1,
        FOREIGN KEY (session_id) REFERENCES result_sessions(id) ON DELETE CASCADE
    )");

    $tc = $conn->query("SELECT id FROM result_terms LIMIT 1");
    if ($tc && $tc->num_rows == 0) {
        $sid = $conn->query("SELECT id FROM result_sessions WHERE is_active=1 LIMIT 1")->fetch_assoc()['id'];
        $terms = [
            ['Term 1 (April-September)','TERM1',1],
            ['Term 2 (October-March)','TERM2',2],
            ['Half Yearly Exam','HALF',3],
            ['Annual Exam','ANNUAL',4]
        ];
        $ts = $conn->prepare("INSERT INTO result_terms (session_id,term_name,term_code,display_order) VALUES (?,?,?,?)");
        foreach($terms as $t) {
            $ts->bind_param("issi",$sid,$t[0],$t[1],$t[2]);
            $ts->execute();
        }
    }

    // 7. Exam Components/Structure
    $conn->query("CREATE TABLE IF NOT EXISTS result_exam_structure (
        id INT AUTO_INCREMENT PRIMARY KEY,
        term_id INT NOT NULL,
        class_id INT NOT NULL,
        subject_id INT NOT NULL,
        component_name VARCHAR(50) COMMENT 'Theory, Practical, Oral, Notebook, SA',
        max_marks DECIMAL(6,2) DEFAULT 100,
        pass_marks DECIMAL(6,2) DEFAULT 33,
        weightage DECIMAL(5,2) DEFAULT 100 COMMENT 'out of 100 final',
        FOREIGN KEY (term_id) REFERENCES result_terms(id) ON DELETE CASCADE,
        FOREIGN KEY (class_id) REFERENCES result_classes(id) ON DELETE CASCADE,
        FOREIGN KEY (subject_id) REFERENCES result_subjects(id) ON DELETE CASCADE
    )");

    // 8. Student Marks
    $conn->query("CREATE TABLE IF NOT EXISTS result_marks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        exam_structure_id INT NOT NULL,
        marks_obtained DECIMAL(6,2) DEFAULT NULL,
        status ENUM('present','absent','medical','exempted') DEFAULT 'present',
        entered_by VARCHAR(100),
        remarks VARCHAR(255),
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_sm (student_id, exam_structure_id),
        FOREIGN KEY (student_id) REFERENCES result_students(id) ON DELETE CASCADE,
        FOREIGN KEY (exam_structure_id) REFERENCES result_exam_structure(id) ON DELETE CASCADE
    )");

    // 9. Attendance per Term
    $conn->query("CREATE TABLE IF NOT EXISTS result_attendance (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        term_id INT NOT NULL,
        total_days INT DEFAULT 0,
        attended_days INT DEFAULT 0,
        UNIQUE KEY uniq_sa (student_id, term_id),
        FOREIGN KEY (student_id) REFERENCES result_students(id) ON DELETE CASCADE,
        FOREIGN KEY (term_id) REFERENCES result_terms(id) ON DELETE CASCADE
    )");

    // 10. Co-Scholastic Assessments
    $conn->query("CREATE TABLE IF NOT EXISTS result_coscholastic (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        term_id INT NOT NULL,
        activity_name VARCHAR(100) NOT NULL,
        grade VARCHAR(5),
        remarks VARCHAR(255),
        UNIQUE KEY uniq_sco (student_id, term_id, activity_name),
        FOREIGN KEY (student_id) REFERENCES result_students(id) ON DELETE CASCADE,
        FOREIGN KEY (term_id) REFERENCES result_terms(id) ON DELETE CASCADE
    )");

    // 11. Grading Rules
    $conn->query("CREATE TABLE IF NOT EXISTS result_grading (
        id INT AUTO_INCREMENT PRIMARY KEY,
        grade_type ENUM('PRIMARY','PREPRIMARY') DEFAULT 'PRIMARY',
        min_percentage DECIMAL(5,2),
        max_percentage DECIMAL(5,2),
        grade_letter VARCHAR(5),
        grade_point DECIMAL(4,2),
        description VARCHAR(100)
    )");

    $gc = $conn->query("SELECT id FROM result_grading LIMIT 1");
    if ($gc && $gc->num_rows == 0) {
        $grades = [
            ['PRIMARY',91,100,'A1',10.0,'Outstanding'],
            ['PRIMARY',81,90,'A2',9.0,'Excellent'],
            ['PRIMARY',71,80,'B1',8.0,'Very Good'],
            ['PRIMARY',61,70,'B2',7.0,'Good'],
            ['PRIMARY',51,60,'C1',6.0,'Average'],
            ['PRIMARY',41,50,'C2',5.0,'Satisfactory'],
            ['PRIMARY',33,40,'D',4.0,'Needs Improvement'],
            ['PRIMARY',0,32,'E',0.0,'Fail'],
            ['PREPRIMARY',90,100,'A+',0,'Outstanding'],
            ['PREPRIMARY',75,89,'A',0,'Very Good'],
            ['PREPRIMARY',60,74,'B',0,'Good'],
            ['PREPRIMARY',40,59,'C',0,'Satisfactory'],
            ['PREPRIMARY',0,39,'D',0,'Needs Improvement'],
        ];
        $gs = $conn->prepare("INSERT INTO result_grading (grade_type,min_percentage,max_percentage,grade_letter,grade_point,description) VALUES (?,?,?,?,?,?)");
        foreach($grades as $g) {
            $gs->bind_param("sddsds",$g[0],$g[1],$g[2],$g[3],$g[4],$g[5]);
            $gs->execute();
        }
    }

    // 12. Teacher remarks per term
    $conn->query("CREATE TABLE IF NOT EXISTS result_remarks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        term_id INT NOT NULL,
        class_teacher_remarks TEXT,
        principal_remarks TEXT,
        result_status ENUM('Pass','Fail','Promoted','Detained','--') DEFAULT '--',
        is_published TINYINT(1) DEFAULT 0,
        UNIQUE KEY uniq_sr (student_id, term_id),
        FOREIGN KEY (student_id) REFERENCES result_students(id) ON DELETE CASCADE,
        FOREIGN KEY (term_id) REFERENCES result_terms(id) ON DELETE CASCADE
    )");

    return true;
}

// Helper: Calculate Grade from percentage
function getGrade($conn, $percentage, $gradeType = 'PRIMARY') {
    $pct = floatval($percentage);
    $gradeType = $conn->real_escape_string($gradeType);
    $res = $conn->query("SELECT grade_letter, grade_point, description FROM result_grading 
                         WHERE grade_type='$gradeType' AND $pct >= min_percentage AND $pct <= max_percentage LIMIT 1");
    if ($res && $res->num_rows > 0) return $res->fetch_assoc();
    return ['grade_letter'=>'--','grade_point'=>0,'description'=>'Not Graded'];
}

// Helper: Calculate student result summary for a term
function calculateStudentResult($conn, $student_id, $term_id) {
    $sid = intval($student_id);
    $tid = intval($term_id);

    $marks = $conn->query("
        SELECT m.marks_obtained, m.status, es.max_marks, es.weightage,
               s.grading_type, s.subject_name, s.is_scholastic
        FROM result_marks m
        JOIN result_exam_structure es ON m.exam_structure_id = es.id
        JOIN result_subjects s ON es.subject_id = s.id
        WHERE m.student_id=$sid AND es.term_id=$tid AND s.is_scholastic=1
    ");

    $totalObtained = 0; $totalMax = 0; $subjectCount = 0; $failCount = 0;
    $subjectResults = [];

    while ($row = $marks->fetch_assoc()) {
        if ($row['grading_type'] == 'NUMERIC' && $row['status'] == 'present') {
            $obtained = floatval($row['marks_obtained']);
            $max = floatval($row['max_marks']);
            $totalObtained += $obtained;
            $totalMax += $max;
            $subjectCount++;
            $pct = $max > 0 ? ($obtained / $max) * 100 : 0;
            if ($pct < 33) $failCount++;
        }
    }

    $overallPct = $totalMax > 0 ? ($totalObtained / $totalMax) * 100 : 0;
    $grade = getGrade($conn, $overallPct, 'PRIMARY');

    return [
        'total_obtained' => $totalObtained,
        'total_max' => $totalMax,
        'percentage' => round($overallPct, 2),
        'grade' => $grade['grade_letter'],
        'grade_point' => $grade['grade_point'],
        'description' => $grade['description'],
        'fail_subjects' => $failCount,
        'result' => ($failCount == 0 && $subjectCount > 0) ? 'Pass' : 'Fail'
    ];
}
?>