-- =============================================================================
-- Lukenya Premier Academy — ONE-TIME full demo data clean
-- =============================================================================
-- ⚠️  BACKUP YOUR DATABASE BEFORE RUNNING THIS SCRIPT.
--
-- In phpMyAdmin: select your database first (e.g. xjumypsj_educore_erp), then Import.
--
-- KEEPS (system foundation):
--   roles, permissions, role_permissions
--   school_levels, grade_levels, learning_areas, strands, sub_strands, competencies
--   performance_levels, cbc_remark_templates, talents (catalog)
--   payment_methods, fee_categories, system_settings, schema_migrations
--   residence_areas (bus route catalog)
--
-- CLEARS (all operational / demo records):
--   users, staff, students, parents, classes, finance, marks, messages, etc.
--
-- AFTER RUNNING — rebuild in this order:
--   1. php database/seed_cbc_classes_subjects.sql  OR re-import classes via Academics
--   2. php database/seed_staff_logins.php
--   3. Import database/export/import_all_students.sql  (207 learners)
--   4. php database/seed_residence_areas.php
--   5. php database/assign_student_fees.php
--   6. php database/seed_parent_logins.php
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Logs & communication
TRUNCATE TABLE activity_logs;
TRUNCATE TABLE login_logs;
TRUNCATE TABLE announcement_reads;
TRUNCATE TABLE announcements;
TRUNCATE TABLE messages;
TRUNCATE TABLE notifications;

-- Class teachers
TRUNCATE TABLE class_teacher_assignments;

-- Exam engine
TRUNCATE TABLE exam_result_audit_logs;
TRUNCATE TABLE exam_results;
TRUNCATE TABLE exam_subjects;
TRUNCATE TABLE exams;

-- CBC learner records (keeps curriculum catalog)
TRUNCATE TABLE talent_assessments;
TRUNCATE TABLE student_talents;
TRUNCATE TABLE parent_remarks;
TRUNCATE TABLE headteacher_remarks;
TRUNCATE TABLE teacher_remarks;
TRUNCATE TABLE cbc_reports;
TRUNCATE TABLE cbc_evidence;
TRUNCATE TABLE portfolio_files;
TRUNCATE TABLE learner_portfolios;
TRUNCATE TABLE cbc_observations;
TRUNCATE TABLE cbc_scores;
TRUNCATE TABLE cbc_assessments;

-- Academics & attendance
TRUNCATE TABLE marks;
TRUNCATE TABLE remarks;
TRUNCATE TABLE assessments;
TRUNCATE TABLE attendance;
TRUNCATE TABLE learning_materials;
TRUNCATE TABLE grade_scales;
TRUNCATE TABLE timetables;
TRUNCATE TABLE teacher_subjects;
TRUNCATE TABLE subjects;

-- Enrolments & class structure
TRUNCATE TABLE class_grade_levels;
TRUNCATE TABLE student_enrollments;
TRUNCATE TABLE streams;
TRUNCATE TABLE classes;
TRUNCATE TABLE academic_terms;
TRUNCATE TABLE academic_years;

-- Finance
TRUNCATE TABLE receipts;
TRUNCATE TABLE payments;
TRUNCATE TABLE invoice_items;
TRUNCATE TABLE invoices;
TRUNCATE TABLE fee_structure_items;
TRUNCATE TABLE fee_structures;
TRUNCATE TABLE expenses;

-- People
TRUNCATE TABLE parent_student;
TRUNCATE TABLE parents;
TRUNCATE TABLE students;
TRUNCATE TABLE staff;
TRUNCATE TABLE departments;
TRUNCATE TABLE user_roles;
TRUNCATE TABLE users;

-- HR & support
TRUNCATE TABLE appraisals;
TRUNCATE TABLE payroll;
TRUNCATE TABLE leaves;
TRUNCATE TABLE staff_attendance;
TRUNCATE TABLE contracts;
TRUNCATE TABLE support_tasks;
TRUNCATE TABLE duty_rosters;

-- Library
TRUNCATE TABLE book_fines;
TRUNCATE TABLE book_loans;
TRUNCATE TABLE books;

-- Transport assignments (keeps residence_areas route list)
TRUNCATE TABLE transport_fees;
TRUNCATE TABLE transport_assignments;
TRUNCATE TABLE routes;
TRUNCATE TABLE buses;

-- Health
TRUNCATE TABLE clinic_visits;
TRUNCATE TABLE medical_records;

-- Term control & exports
TRUNCATE TABLE report_exports;
TRUNCATE TABLE term_locks;

SET FOREIGN_KEY_CHECKS = 1;

SELECT 'Demo data cleared. Re-seed staff, classes, and learners before logging in.' AS status;