CREATE TABLE "migrations" ("id" integer primary key autoincrement not null, "migration" varchar not null, "batch" integer not null);
CREATE TABLE "cache" ("key" varchar not null, "value" text not null, "expiration" integer not null, primary key ("key"));
CREATE TABLE "cache_locks" ("key" varchar not null, "owner" varchar not null, "expiration" integer not null, primary key ("key"));
CREATE TABLE "jobs" ("id" integer primary key autoincrement not null, "queue" varchar not null, "payload" text not null, "attempts" integer not null, "reserved_at" integer, "available_at" integer not null, "created_at" integer not null);
CREATE TABLE "job_batches" ("id" varchar not null, "name" varchar not null, "total_jobs" integer not null, "pending_jobs" integer not null, "failed_jobs" integer not null, "failed_job_ids" text not null, "options" text, "cancelled_at" integer, "created_at" integer not null, "finished_at" integer, primary key ("id"));
CREATE TABLE "failed_jobs" ("id" integer primary key autoincrement not null, "uuid" varchar not null, "connection" text not null, "queue" text not null, "payload" text not null, "exception" text not null, "failed_at" datetime not null default CURRENT_TIMESTAMP);
CREATE TABLE "plans" ("id" integer primary key autoincrement not null, "name" varchar not null, "slug" varchar not null, "description" text, "price" numeric not null default '0', "billing_period" varchar not null default 'monthly', "features" text, "max_students" integer not null default '50', "max_teachers" integer not null default '10', "max_classes" integer not null default '10', "is_active" tinyint(1) not null default '1', "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, "billing_type" varchar, "per_student_price" numeric, "trial_days" integer);
CREATE TABLE "users" ("id" integer primary key autoincrement not null, "school_id" integer, "name" varchar not null, "email" varchar not null, "email_verified_at" datetime, "password" varchar not null, "role" varchar not null default 'student', "avatar" varchar, "phone" varchar, "is_active" tinyint(1) not null default '1', "remember_token" varchar, "created_at" datetime, "updated_at" datetime, "two_factor_secret" text, "two_factor_recovery_codes" text, "two_factor_confirmed_at" datetime, "must_change_password" tinyint(1) not null default '0', "provider" varchar, "provider_id" varchar, foreign key("school_id") references "schools"("id") on delete set null);
CREATE TABLE "sessions" ("id" varchar not null, "user_id" integer, "ip_address" varchar, "user_agent" text, "payload" text not null, "last_activity" integer not null, primary key ("id"));
CREATE TABLE "subscriptions" ("id" integer primary key autoincrement not null, "school_id" integer not null, "plan_id" integer not null, "status" varchar not null default 'active', "starts_at" datetime, "ends_at" datetime, "payment_amount" numeric not null default '0', "payment_method" varchar, "transaction_ref" varchar, "created_at" datetime, "updated_at" datetime, "billing_type" varchar, "per_student_price" numeric, "trial_days" integer, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("plan_id") references "plans"("id") on delete cascade);
CREATE TABLE "platform_admins" ("id" integer primary key autoincrement not null, "user_id" integer not null, "permissions" text, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade);
CREATE TABLE "activity_logs" ("id" integer primary key autoincrement not null, "school_id" integer, "user_id" integer, "action" varchar not null, "subject_type" varchar, "subject_id" integer, "data" text, "ip_address" varchar, "user_agent" text, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete set null, foreign key("user_id") references "users"("id") on delete set null);
CREATE TABLE "academic_years" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "start_date" date not null, "end_date" date not null, "is_current" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "terms" ("id" integer primary key autoincrement not null, "school_id" integer not null, "academic_year_id" integer not null, "name" varchar not null, "start_date" date not null, "end_date" date not null, "is_current" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("academic_year_id") references "academic_years"("id") on delete cascade);
CREATE TABLE "sections" ("id" integer primary key autoincrement not null, "school_id" integer not null, "class_id" integer not null, "name" varchar not null, "teacher_id" integer, "capacity" integer not null default '40', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("class_id") references "classes"("id") on delete cascade, foreign key("teacher_id") references "users"("id") on delete set null);
CREATE TABLE "subjects" ("id" integer primary key autoincrement not null, "school_id" integer not null, "class_id" integer not null, "name" varchar not null, "slug" varchar not null, "code" varchar, "teacher_id" integer, "description" text, "max_marks" integer not null default '100', "pass_marks" integer not null default '40', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("class_id") references "classes"("id") on delete cascade, foreign key("teacher_id") references "users"("id") on delete set null);
CREATE TABLE "enrollments" ("id" integer primary key autoincrement not null, "school_id" integer not null, "student_id" integer not null, "class_id" integer not null, "section_id" integer, "academic_year_id" integer not null, "status" varchar not null default 'active', "roll_no" varchar, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade, foreign key("class_id") references "classes"("id") on delete cascade, foreign key("section_id") references "sections"("id") on delete set null, foreign key("academic_year_id") references "academic_years"("id") on delete cascade);
CREATE TABLE "parents" ("id" integer primary key autoincrement not null, "school_id" integer not null, "user_id" integer, "first_name" varchar not null, "last_name" varchar not null, "email" varchar, "phone" varchar, "alternate_phone" varchar, "occupation" varchar, "relationship" varchar not null, "address" text, "city" varchar, "state" varchar, "notes" text, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("user_id") references "users"("id") on delete set null);
CREATE TABLE "attendances" ("id" integer primary key autoincrement not null, "school_id" integer not null, "student_id" integer not null, "class_id" integer not null, "date" date not null, "status" varchar not null default 'present', "time_in" varchar, "time_out" varchar, "marked_by" integer, "remarks" text, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade, foreign key("class_id") references "classes"("id") on delete cascade, foreign key("marked_by") references "users"("id") on delete set null);
CREATE TABLE "timetables" ("id" integer primary key autoincrement not null, "school_id" integer not null, "class_id" integer not null, "subject_id" integer not null, "teacher_id" integer, "day_of_week" varchar not null, "start_time" time not null, "end_time" time not null, "room" varchar, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("class_id") references "classes"("id") on delete cascade, foreign key("subject_id") references "subjects"("id") on delete cascade, foreign key("teacher_id") references "users"("id") on delete set null);
CREATE TABLE "exams" ("id" integer primary key autoincrement not null, "school_id" integer not null, "class_id" integer not null, "term_id" integer, "name" varchar not null, "type" varchar not null default 'exam', "start_date" date, "end_date" date, "total_marks" integer not null default '100', "pass_marks" integer not null default '40', "is_published" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("class_id") references "classes"("id") on delete cascade, foreign key("term_id") references "terms"("id") on delete set null);
CREATE TABLE "exam_results" ("id" integer primary key autoincrement not null, "school_id" integer not null, "exam_id" integer not null, "student_id" integer not null, "subject_id" integer not null, "marks_obtained" numeric, "max_marks" numeric not null default '100', "grade" varchar, "remark" varchar, "entered_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("exam_id") references "exams"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade, foreign key("subject_id") references "subjects"("id") on delete cascade, foreign key("entered_by") references "users"("id") on delete set null);
CREATE TABLE "grading_systems" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "grade_letter" varchar not null, "min_marks" numeric not null, "max_marks" numeric not null, "remark" varchar, "grade_point" integer, "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "fee_categories" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "description" text, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, "code" varchar, "is_optional" tinyint(1) not null default '0', "is_one_time" tinyint(1) not null default '0', "archived_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "applications" ("id" integer primary key autoincrement not null, "school_id" integer not null, "application_no" varchar not null, "first_name" varchar not null, "last_name" varchar not null, "email" varchar not null, "phone" varchar, "date_of_birth" date, "gender" varchar, "class_applied_for" integer, "previous_school" varchar, "parent_name" varchar, "parent_email" varchar, "parent_phone" varchar, "parent_occupation" varchar, "address" text, "documents" varchar, "status" varchar not null default 'pending', "admin_notes" text, "created_at" datetime, "updated_at" datetime, "consent_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("class_applied_for") references "classes"("id") on delete set null);
CREATE TABLE "announcements" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "body" text not null, "audience" varchar not null default 'all', "is_published" tinyint(1) not null default '0', "published_at" datetime, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("created_by") references "users"("id") on delete set null);
CREATE TABLE "messages" ("id" integer primary key autoincrement not null, "school_id" integer not null, "sender_id" integer not null, "receiver_id" integer not null, "subject" varchar not null, "body" text not null, "is_read" tinyint(1) not null default '0', "read_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("sender_id") references "users"("id") on delete cascade, foreign key("receiver_id") references "users"("id") on delete cascade);
CREATE TABLE "events" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "slug" varchar not null, "description" text, "event_date" date not null, "event_time" time, "location" varchar, "type" varchar not null default 'general', "image" varchar, "created_at" datetime, "updated_at" datetime, "end_date" date, "end_time" time, "is_published" tinyint(1) not null default '1', "is_featured" tinyint(1) not null default '0', "cta_label" varchar, "cta_url" varchar, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "news" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "slug" varchar not null, "content" text not null, "featured_image" varchar, "is_published" tinyint(1) not null default '0', "views" integer not null default '0', "meta_title" varchar, "meta_description" text, "created_at" datetime, "updated_at" datetime, "category" varchar, "author" varchar, "published_at" datetime, "is_featured" tinyint(1) not null default '0', foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "gallery_albums" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "slug" varchar not null, "cover_image" varchar, "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, "description" text, "is_published" tinyint(1) not null default '1', foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "gallery_images" ("id" integer primary key autoincrement not null, "school_id" integer not null, "album_id" integer not null, "filename" varchar not null, "caption" varchar, "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, "alt_text" varchar, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("album_id") references "gallery_albums"("id") on delete cascade);
CREATE TABLE "testimonials" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "role" varchar, "type" varchar not null default 'student', "message" text not null, "rating" integer not null default '5', "photo" varchar, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, "is_featured" tinyint(1) not null default '0', foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "faqs" ("id" integer primary key autoincrement not null, "school_id" integer not null, "question" varchar not null, "answer" text not null, "sort_order" integer not null default '0', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, "category" varchar not null default 'General', foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "downloads" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "category" varchar, "filename" varchar not null, "original_name" varchar, "file_type" varchar, "file_size" integer, "download_count" integer not null default '0', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, "description" text, "published_at" date, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "contact_messages" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "email" varchar not null, "phone" varchar, "subject" varchar, "message" text not null, "is_read" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, "type" varchar not null default 'contact', foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "site_settings" ("id" integer primary key autoincrement not null, "school_id" integer not null, "key" varchar not null, "value" text, "group" varchar not null default 'general', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "permissions" ("id" integer primary key autoincrement not null, "name" varchar not null, "group" varchar not null default 'general', "description" text, "created_at" datetime, "updated_at" datetime);
CREATE TABLE "role_permissions" ("id" integer primary key autoincrement not null, "role" varchar not null, "permission_id" integer not null, "created_at" datetime, "updated_at" datetime, foreign key("permission_id") references "permissions"("id") on delete cascade);
CREATE TABLE "campuses" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "code" varchar, "address" text, "phone" varchar, "email" varchar, "is_main" tinyint(1) not null default '0', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "classes" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "slug" varchar not null, "section" varchar, "academic_year_id" integer not null, "class_teacher_id" integer, "capacity" integer not null default ('40'), "created_at" datetime, "updated_at" datetime, "campus_id" integer, foreign key("class_teacher_id") references users("id") on delete set null on update no action, foreign key("academic_year_id") references academic_years("id") on delete cascade on update no action, foreign key("school_id") references schools("id") on delete cascade on update no action, foreign key("campus_id") references "campuses"("id") on delete set null);
CREATE TABLE "teachers" ("id" integer primary key autoincrement not null, "school_id" integer not null, "user_id" integer, "employee_id" varchar, "first_name" varchar not null, "last_name" varchar not null, "email" varchar not null, "phone" varchar, "qualification" varchar, "experience" varchar, "department" varchar, "designation" varchar, "join_date" date, "salary" numeric, "specialization" text, "bio" text, "photo" varchar, "is_active" tinyint(1) not null default ('1'), "created_at" datetime, "updated_at" datetime, "campus_id" integer, "middle_name" varchar, "gender" varchar check ("gender" in ('male', 'female', 'other')), "date_of_birth" date, "address" text, "state_of_origin" varchar, "lga" varchar, "nationality" varchar, "employment_status" varchar not null default 'active', "employment_type" varchar, "permissions" text, foreign key("user_id") references users("id") on delete set null on update no action, foreign key("school_id") references schools("id") on delete cascade on update no action, foreign key("campus_id") references "campuses"("id") on delete set null);
CREATE TABLE "staff" ("id" integer primary key autoincrement not null, "school_id" integer not null, "user_id" integer, "employee_id" varchar, "first_name" varchar not null, "last_name" varchar not null, "email" varchar not null, "phone" varchar, "department" varchar, "designation" varchar, "qualification" varchar, "join_date" date, "salary" numeric, "is_active" tinyint(1) not null default ('1'), "created_at" datetime, "updated_at" datetime, "campus_id" integer, foreign key("user_id") references users("id") on delete set null on update no action, foreign key("school_id") references schools("id") on delete cascade on update no action, foreign key("campus_id") references "campuses"("id") on delete set null);
CREATE TABLE "assessment_types" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "code" varchar, "max_score" numeric not null default '100', "weight" numeric not null default '0', "sort_order" integer not null default '0', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "assessment_scores" ("id" integer primary key autoincrement not null, "school_id" integer not null, "student_id" integer not null, "subject_id" integer not null, "assessment_type_id" integer not null, "term_id" integer not null, "score" numeric, "entered_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade, foreign key("subject_id") references "subjects"("id") on delete cascade, foreign key("assessment_type_id") references "assessment_types"("id") on delete cascade, foreign key("term_id") references "terms"("id") on delete cascade, foreign key("entered_by") references "users"("id") on delete set null);
CREATE TABLE "behaviour_traits" ("id" integer primary key autoincrement not null, "school_id" integer not null, "category" varchar not null, "name" varchar not null, "sort_order" integer not null default '0', "show_on_report" tinyint(1) not null default '1', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "behaviour_scores" ("id" integer primary key autoincrement not null, "school_id" integer not null, "student_id" integer not null, "trait_id" integer not null, "term_id" integer not null, "rating" varchar not null, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade, foreign key("trait_id") references "behaviour_traits"("id") on delete cascade, foreign key("term_id") references "terms"("id") on delete cascade);
CREATE TABLE "notifications" ("id" integer primary key autoincrement not null, "school_id" integer not null, "user_id" integer, "audience" varchar not null default 'staff', "type" varchar not null default 'general', "title" varchar not null, "body" text, "is_read" tinyint(1) not null default '0', "read_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("user_id") references "users"("id") on delete set null);
CREATE TABLE "pages" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "slug" varchar not null, "content" text, "meta_title" varchar, "meta_description" text, "is_published" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "media_files" ("id" integer primary key autoincrement not null, "school_id" integer not null, "uploaded_by" integer, "filename" varchar not null, "original_name" varchar not null, "mime_type" varchar, "size" integer not null default '0', "kind" varchar not null default 'document', "alt_text" varchar, "caption" varchar, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("uploaded_by") references "users"("id") on delete set null);
CREATE TABLE "website_slides" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "subtitle" varchar, "description" text, "image" varchar, "button_text" varchar, "button_url" varchar, "sort_order" integer not null default '0', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, "button2_text" varchar, "button2_url" varchar, "text_position" varchar not null default 'left', "overlay_enabled" tinyint(1) not null default '1', "overlay_opacity" integer not null default '55', foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "students" ("id" integer primary key autoincrement not null, "school_id" integer not null, "user_id" integer, "class_id" integer, "section_id" integer, "parent_id" integer, "admission_no" varchar, "roll_no" varchar, "first_name" varchar not null, "last_name" varchar not null, "middle_name" varchar, "email" varchar, "phone" varchar, "date_of_birth" date, "gender" varchar, "blood_group" varchar, "address" text, "city" varchar, "state" varchar, "photo" varchar, "national_id_no" varchar, "previous_school" varchar, "height" numeric, "weight" numeric, "medical_conditions" text, "allergies" text, "notes" text, "is_active" tinyint(1) not null default ('1'), "admitted_at" datetime, "created_at" datetime, "updated_at" datetime, "campus_id" integer, "nationality" varchar, "state_of_origin" varchar, "lga" varchar, "admission_date" date, "status" varchar not null default 'active', foreign key("campus_id") references campuses("id") on delete set null on update no action, foreign key("school_id") references schools("id") on delete cascade on update no action, foreign key("user_id") references users("id") on delete set null on update no action, foreign key("class_id") references classes("id") on delete set null on update no action, foreign key("section_id") references sections("id") on delete set null on update no action, foreign key("parent_id") references users("id") on delete set null on update no action, foreign key("parent_id") references "parents"("id") on delete set null);
CREATE TABLE "student_documents" ("id" integer primary key autoincrement not null, "school_id" integer not null, "student_id" integer not null, "uploaded_by" integer, "title" varchar not null, "file_path" varchar not null, "mime_type" varchar, "size_bytes" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade, foreign key("uploaded_by") references "users"("id") on delete set null);
CREATE TABLE "enquiries" ("id" integer primary key autoincrement not null, "school_id" integer not null, "student_first_name" varchar not null, "student_last_name" varchar not null, "date_of_birth" date, "gender" varchar, "guardian_name" varchar, "guardian_phone" varchar, "guardian_email" varchar, "interested_class_id" integer, "source" varchar, "note" text, "status" varchar not null default 'new', "converted_student_id" integer, "handled_by" integer, "follow_up_at" datetime, "created_at" datetime, "updated_at" datetime, "student_middle_name" varchar, "current_school" varchar, "guardian_address" text, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("interested_class_id") references "classes"("id") on delete set null, foreign key("converted_student_id") references "students"("id") on delete set null, foreign key("handled_by") references "users"("id") on delete set null);
CREATE TABLE "schools" ("id" integer primary key autoincrement not null, "name" varchar not null, "slug" varchar not null, "domain" varchar, "email" varchar, "phone" varchar, "address" text, "city" varchar, "state" varchar, "country" varchar not null default ('Nigeria'), "logo" varchar, "favicon" varchar, "timezone" varchar not null default ('Africa/Lagos'), "status" varchar not null default ('trial'), "plan_id" integer, "settings" text, "trial_ends_at" datetime, "created_at" datetime, "updated_at" datetime, "owner_id" integer, "trial_warning_sent_at" datetime, "trial_days" integer, foreign key("plan_id") references plans("id") on delete set null on update no action, foreign key("owner_id") references "users"("id") on delete set null);
CREATE TABLE "platform_settings" ("id" integer primary key autoincrement not null, "key" varchar not null, "value" text, "created_at" datetime, "updated_at" datetime);
CREATE TABLE "result_sheets" ("id" integer primary key autoincrement not null, "school_id" integer not null, "student_id" integer not null, "class_id" integer not null, "term_id" integer not null, "total_score" numeric, "average" numeric, "grade" varchar, "position" integer, "students_in_class" integer, "class_teacher_remark" text, "principal_remark" text, "status" varchar not null default ('draft'), "published_at" datetime, "verification_code" varchar, "created_at" datetime, "updated_at" datetime, "gpa" numeric, "subjects_offered" integer, "overall_remark" text, "submitted_at" datetime, "reviewed_at" datetime, "approved_at" datetime, "submitted_by" integer, "reviewed_by" integer, "approved_by" integer, foreign key("term_id") references terms("id") on delete cascade on update no action, foreign key("class_id") references classes("id") on delete cascade on update no action, foreign key("student_id") references students("id") on delete cascade on update no action, foreign key("school_id") references schools("id") on delete cascade on update no action, foreign key("submitted_by") references "users"("id") on delete set null, foreign key("reviewed_by") references "users"("id") on delete set null, foreign key("approved_by") references "users"("id") on delete set null);
CREATE TABLE "result_signatures" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "name" varchar, "signature" varchar, "show_on_report" tinyint(1) not null default '1', "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "staff_categories" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "slug" varchar not null, "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "departments" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "code" varchar, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "designations" ("id" integer primary key autoincrement not null, "school_id" integer not null, "category_id" integer, "name" varchar not null, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("category_id") references "staff_categories"("id") on delete set null);
CREATE TABLE "staff_members" ("id" integer primary key autoincrement not null, "school_id" integer not null, "campus_id" integer, "user_id" integer, "teacher_id" integer, "staff_no" varchar, "first_name" varchar not null, "middle_name" varchar, "last_name" varchar not null, "gender" varchar check ("gender" in ('male', 'female', 'other')), "date_of_birth" date, "phone" varchar, "email" varchar, "photo" varchar, "address" text, "city" varchar, "state" varchar, "lga" varchar, "nationality" varchar, "staff_category_id" integer, "department_id" integer, "designation_id" integer, "employed_on" date, "employment_type" varchar not null default 'full_time', "status" varchar not null default 'active', "qualification" varchar, "specialization" text, "notes" text, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("campus_id") references "campuses"("id") on delete set null, foreign key("user_id") references "users"("id") on delete set null, foreign key("teacher_id") references "teachers"("id") on delete set null, foreign key("staff_category_id") references "staff_categories"("id") on delete set null, foreign key("department_id") references "departments"("id") on delete set null, foreign key("designation_id") references "designations"("id") on delete set null);
CREATE TABLE "staff_attendance" ("id" integer primary key autoincrement not null, "school_id" integer not null, "staff_member_id" integer not null, "date" date not null, "status" varchar check ("status" in ('present', 'absent', 'late', 'excused')) not null default 'present', "note" varchar, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("staff_member_id") references "staff_members"("id") on delete cascade);
CREATE TABLE "email_logs" ("id" integer primary key autoincrement not null, "school_id" integer, "user_id" integer, "recipient_email" varchar not null, "recipient_name" varchar, "notification_type" varchar not null, "subject" varchar not null, "status" varchar not null default 'pending', "attempts" integer not null default '0', "error" text, "entity_type" varchar, "entity_id" integer, "sent_at" datetime, "failed_at" datetime, "created_at" datetime, "updated_at" datetime, "payload" text, foreign key("school_id") references "schools"("id") on delete set null, foreign key("user_id") references "users"("id") on delete set null);
CREATE TABLE "notification_preferences" ("id" integer primary key autoincrement not null, "school_id" integer not null, "user_id" integer not null, "category" varchar not null, "email_enabled" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("user_id") references "users"("id") on delete cascade);
CREATE TABLE "password_reset_tokens" ("email" varchar not null, "token" varchar not null, "created_at" datetime, "user_id" integer, foreign key("user_id") references "users"("id") on delete set null, primary key ("email"));
CREATE TABLE "bank_accounts" ("id" integer primary key autoincrement not null, "school_id" integer not null, "account_name" varchar not null, "bank_name" varchar not null, "account_number" varchar not null, "account_type" varchar not null default 'current', "purpose" varchar, "is_default" tinyint(1) not null default '0', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "invoice_items" ("id" integer primary key autoincrement not null, "school_id" integer not null, "invoice_id" integer not null, "fee_structure_id" integer, "fee_category_id" integer, "description" varchar not null, "quantity" integer not null default '1', "unit_amount" numeric not null, "amount" numeric not null, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("invoice_id") references "invoices"("id") on delete cascade, foreign key("fee_structure_id") references "fee_structures"("id") on delete set null, foreign key("fee_category_id") references "fee_categories"("id") on delete set null);
CREATE TABLE "finance_adjustments" ("id" integer primary key autoincrement not null, "school_id" integer not null, "invoice_id" integer not null, "student_id" integer not null, "type" varchar not null, "basis" varchar not null, "value" numeric not null, "amount" numeric not null, "reason" varchar, "academic_year_id" integer, "term_id" integer, "approved_by" integer, "approved_at" datetime, "created_by" integer, "reversed_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("invoice_id") references "invoices"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade);
CREATE TABLE "student_credits" ("id" integer primary key autoincrement not null, "school_id" integer not null, "student_id" integer not null, "parent_id" integer, "source_payment_id" integer, "amount" numeric not null, "remaining" numeric not null, "reason" varchar, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade, foreign key("parent_id") references "parents"("id") on delete set null, foreign key("source_payment_id") references "payments"("id") on delete set null);
CREATE TABLE "refunds" ("id" integer primary key autoincrement not null, "school_id" integer not null, "payment_id" integer not null, "invoice_id" integer, "student_id" integer not null, "amount" numeric not null, "reason" varchar, "method" varchar, "reference" varchar, "authorized_by" integer, "created_by" integer, "refunded_at" datetime not null, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("payment_id") references "payments"("id") on delete cascade, foreign key("invoice_id") references "invoices"("id") on delete set null, foreign key("student_id") references "students"("id") on delete cascade);
CREATE TABLE "expense_categories" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "description" text, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "recurring_expenses" ("id" integer primary key autoincrement not null, "school_id" integer not null, "expense_category_id" integer, "title" varchar not null, "payee" varchar, "amount" numeric not null, "frequency" varchar not null, "start_date" date not null, "end_date" date, "next_due_date" date not null, "last_generated_at" datetime, "bank_account_id" integer, "status" varchar not null default 'active', "auto_create" tinyint(1) not null default '0', "notes" text, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("expense_category_id") references "expense_categories"("id") on delete set null, foreign key("bank_account_id") references "bank_accounts"("id") on delete set null);
CREATE TABLE "expenses" ("id" integer primary key autoincrement not null, "school_id" integer not null, "expense_category_id" integer, "title" varchar not null, "description" text, "amount" numeric not null, "expense_date" date not null, "vendor" varchar, "payment_method" varchar not null default 'cash', "reference" varchar, "bank_account_id" integer, "document_path" varchar, "academic_year_id" integer, "term_id" integer, "recurring_expense_id" integer, "status" varchar not null default 'pending_approval', "recorded_by" integer, "approved_by" integer, "approved_at" datetime, "paid_at" datetime, "notes" text, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("expense_category_id") references "expense_categories"("id") on delete set null, foreign key("bank_account_id") references "bank_accounts"("id") on delete set null, foreign key("recurring_expense_id") references "recurring_expenses"("id") on delete set null);
CREATE TABLE "income_categories" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "description" text, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "other_income" ("id" integer primary key autoincrement not null, "school_id" integer not null, "income_category_id" integer, "source" varchar not null, "amount" numeric not null, "income_date" date not null, "payment_method" varchar not null default 'cash', "reference" varchar, "bank_account_id" integer, "description" text, "academic_year_id" integer, "term_id" integer, "recorded_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("income_category_id") references "income_categories"("id") on delete set null, foreign key("bank_account_id") references "bank_accounts"("id") on delete set null);
CREATE TABLE "payment_transactions" ("id" integer primary key autoincrement not null, "school_id" integer not null, "reference" varchar not null, "user_id" integer, "student_id" integer, "invoice_id" integer, "provider" varchar not null, "provider_reference" varchar, "amount" numeric not null, "currency" varchar not null default 'NGN', "status" varchar not null default 'initialized', "channel" varchar, "meta" text, "paid_at" datetime, "payment_id" integer, "created_at" datetime, "updated_at" datetime, "intent" varchar not null default 'payment', "webhook_signature_verified" tinyint(1) not null default '0', "processed_at" datetime, "attempts" integer not null default '0', foreign key("school_id") references "schools"("id") on delete cascade, foreign key("user_id") references "users"("id") on delete set null, foreign key("student_id") references "students"("id") on delete set null, foreign key("invoice_id") references "invoices"("id") on delete set null, foreign key("payment_id") references "payments"("id") on delete set null);
CREATE TABLE "payment_reminders" ("id" integer primary key autoincrement not null, "school_id" integer not null, "invoice_id" integer not null, "student_id" integer not null, "reminder_type" varchar not null, "period_key" varchar not null, "recipient_email" varchar, "sent_at" datetime not null, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("invoice_id") references "invoices"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade);
CREATE TABLE "fee_structures" ("id" integer primary key autoincrement not null, "school_id" integer not null, "fee_category_id" integer not null, "class_id" integer not null, "academic_year_id" integer not null, "amount" numeric not null, "due_date" varchar, "is_active" tinyint(1) not null default ('1'), "created_at" datetime, "updated_at" datetime, "term_id" integer, "section_id" integer, "student_category" varchar, "student_id" integer, foreign key("academic_year_id") references academic_years("id") on delete cascade on update no action, foreign key("class_id") references classes("id") on delete cascade on update no action, foreign key("fee_category_id") references fee_categories("id") on delete cascade on update no action, foreign key("school_id") references schools("id") on delete cascade on update no action, foreign key("term_id") references "terms"("id") on delete set null, foreign key("section_id") references "sections"("id") on delete set null, foreign key("student_id") references "students"("id") on delete set null);
CREATE TABLE "invoices" ("id" integer primary key autoincrement not null, "school_id" integer not null, "invoice_no" varchar not null, "student_id" integer not null, "fee_structure_id" integer, "amount" numeric not null, "discount" numeric not null default ('0'), "total_paid" numeric not null default ('0'), "balance" numeric as (amount - discount - total_paid) stored, "due_date" date not null, "status" varchar not null default ('pending'), "notes" text, "created_at" datetime, "updated_at" datetime, "academic_year_id" integer, "term_id" integer, "description" varchar, "previous_balance" numeric not null default '0', "refund_total" numeric not null default '0', "cancelled_at" datetime, "created_by" integer, foreign key("fee_structure_id") references fee_structures("id") on delete cascade on update no action, foreign key("student_id") references students("id") on delete cascade on update no action, foreign key("school_id") references schools("id") on delete cascade on update no action, foreign key("academic_year_id") references "academic_years"("id") on delete set null, foreign key("term_id") references "terms"("id") on delete set null);
CREATE TABLE "payments" ("id" integer primary key autoincrement not null, "school_id" integer not null, "invoice_id" integer not null, "student_id" integer not null, "amount" numeric not null, "payment_method" varchar not null default ('cash'), "receipt_no" varchar, "transaction_ref" varchar, "payment_date" date not null, "received_by" varchar, "notes" text, "created_at" datetime, "updated_at" datetime, "status" varchar not null default 'confirmed', "allocated_amount" numeric not null default '0', "refunded_amount" numeric not null default '0', "payer_name" varchar, "method_detail" varchar, "bank_account_id" integer, "proof_path" varchar, "recorded_by" integer, "verified_by" integer, "verified_at" datetime, "rejection_reason" varchar, "reversed_at" datetime, "academic_year_id" integer, "term_id" integer, foreign key("student_id") references students("id") on delete cascade on update no action, foreign key("invoice_id") references invoices("id") on delete cascade on update no action, foreign key("school_id") references schools("id") on delete cascade on update no action, foreign key("bank_account_id") references "bank_accounts"("id") on delete set null, foreign key("academic_year_id") references "academic_years"("id") on delete set null, foreign key("term_id") references "terms"("id") on delete set null);
CREATE TABLE "academic_programs" ("id" integer primary key autoincrement not null, "school_id" integer not null, "title" varchar not null, "description" text, "icon" varchar, "image" varchar, "link" varchar, "display_order" integer not null default '0', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "admission_programmes" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "code" varchar, "description" text, "status" varchar not null default 'draft', "application_deadline" date, "results_date" date, "academic_year_id" integer, "max_applications" integer, "fee_amount" numeric, "admission_type" varchar not null default 'new', "eligibility" text, "documents_required" text, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("academic_year_id") references "academic_years"("id") on delete set null, foreign key("created_by") references "users"("id") on delete set null);
CREATE TABLE "admission_forms" ("id" integer primary key autoincrement not null, "school_id" integer not null, "programme_id" integer, "title" varchar not null, "description" text, "status" varchar not null default 'draft', "version" integer not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("programme_id") references "admission_programmes"("id") on delete cascade);
CREATE TABLE "admission_form_sections" ("id" integer primary key autoincrement not null, "admission_form_id" integer not null, "title" varchar not null, "description" text, "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("admission_form_id") references "admission_forms"("id") on delete cascade);
CREATE TABLE "admission_form_fields" ("id" integer primary key autoincrement not null, "admission_form_section_id" integer not null, "label" varchar not null, "name" varchar not null, "type" varchar not null, "placeholder" varchar, "help_text" text, "is_required" tinyint(1) not null default '0', "is_core" tinyint(1) not null default '0', "options" text, "validation" text, "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("admission_form_section_id") references "admission_form_sections"("id") on delete cascade);
CREATE TABLE "admission_applications" ("id" integer primary key autoincrement not null, "school_id" integer not null, "programme_id" integer, "admission_form_id" integer, "application_no" varchar not null, "status" varchar not null default 'submitted', "first_name" varchar not null, "last_name" varchar not null, "email" varchar not null, "phone" varchar, "date_of_birth" date, "gender" varchar, "desired_class_id" integer, "previous_school" varchar, "parent_name" varchar, "parent_email" varchar, "parent_phone" varchar, "parent_occupation" varchar, "address" text, "data" text, "form_snapshot" text, "correction_notes" text, "admin_notes" text, "decision_notes" text, "reviewed_by" integer, "reviewed_at" datetime, "decided_by" integer, "decided_at" datetime, "converted_student_id" integer, "converted_at" datetime, "submitted_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("programme_id") references "admission_programmes"("id") on delete set null, foreign key("admission_form_id") references "admission_forms"("id") on delete set null, foreign key("desired_class_id") references "classes"("id") on delete set null, foreign key("reviewed_by") references "users"("id") on delete set null, foreign key("decided_by") references "users"("id") on delete set null, foreign key("converted_student_id") references "students"("id") on delete set null);
CREATE TABLE "admission_application_documents" ("id" integer primary key autoincrement not null, "school_id" integer not null, "application_id" integer not null, "field_name" varchar not null, "original_name" varchar not null, "file_name" varchar not null, "mime" varchar, "size" integer, "path" varchar not null, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("application_id") references "admission_applications"("id") on delete cascade);
CREATE TABLE "admission_status_history" ("id" integer primary key autoincrement not null, "school_id" integer not null, "application_id" integer not null, "from_status" varchar, "to_status" varchar not null, "note" text, "changed_by" integer, "actor_type" varchar not null default 'staff', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("application_id") references "admission_applications"("id") on delete cascade, foreign key("changed_by") references "users"("id") on delete set null);
CREATE TABLE "admission_letters" ("id" integer primary key autoincrement not null, "school_id" integer not null, "application_id" integer not null, "student_id" integer, "letter_no" varchar not null, "title" varchar not null, "content" text not null, "signed_by" varchar, "signed_by_title" varchar, "status" varchar not null default 'draft', "issued_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("application_id") references "admission_applications"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete set null);
CREATE TABLE "assessment_weights" ("id" integer primary key autoincrement not null, "school_id" integer not null, "exam_id" integer not null, "subject_id" integer not null, "weight" integer not null, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("exam_id") references "exams"("id") on delete cascade, foreign key("subject_id") references "subjects"("id") on delete cascade);
CREATE TABLE "exam_candidates" ("id" integer primary key autoincrement not null, "school_id" integer not null, "exam_id" integer not null, "student_id" integer not null, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("exam_id") references "exams"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade);
CREATE TABLE "exam_timetable_entries" ("id" integer primary key autoincrement not null, "school_id" integer not null, "exam_id" integer not null, "subject_id" integer not null, "exam_date" date not null, "start_time" time not null, "end_time" time not null, "room" varchar, "supervisor" varchar, "notes" text, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("exam_id") references "exams"("id") on delete cascade, foreign key("subject_id") references "subjects"("id") on delete cascade);
CREATE TABLE "exam_attendance" ("id" integer primary key autoincrement not null, "school_id" integer not null, "exam_id" integer not null, "student_id" integer not null, "subject_id" integer not null, "status" varchar check ("status" in ('present', 'absent', 'late', 'excused')) not null default 'present', "remark" text, "marked_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("exam_id") references "exams"("id") on delete cascade, foreign key("student_id") references "students"("id") on delete cascade, foreign key("subject_id") references "subjects"("id") on delete cascade, foreign key("marked_by") references "users"("id") on delete set null);
CREATE TABLE "payment_gateway_configs" ("id" integer primary key autoincrement not null, "school_id" integer not null, "provider" varchar not null, "mode" varchar not null default 'test', "public_key" varchar, "secret_key" text, "webhook_secret" text, "is_active" tinyint(1) not null default '0', "settings" text, "created_at" datetime, "updated_at" datetime, "gateway_mode" varchar not null default 'own', foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "school_settlement_profiles" ("id" integer primary key autoincrement not null, "school_id" integer not null, "bank_account_id" integer not null, "settlement_currency" varchar not null default 'NGN', "is_default" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("bank_account_id") references "bank_accounts"("id") on delete cascade);
CREATE TABLE "service_charge_rules" ("id" integer primary key autoincrement not null, "school_id" integer not null, "name" varchar not null, "charge_type" varchar not null default 'percent', "charge_to" varchar not null default 'parent', "charge_value" numeric not null default '0', "gateway_fee_handling" varchar not null default 'charge_parent', "applies_to" varchar not null default 'online', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, "charge_value_percent" numeric not null default '0', foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "subscription_invoices" ("id" integer primary key autoincrement not null, "school_id" integer not null, "subscription_id" integer not null, "invoice_ref" varchar not null, "period_start" date not null, "period_end" date not null, "line_items" text, "base_amount" numeric not null default '0', "service_charge" numeric not null default '0', "gateway_fee" numeric not null default '0', "total" numeric not null default '0', "currency" varchar not null default 'NGN', "status" varchar not null default 'issued', "paid_at" datetime, "provider" varchar, "transaction_ref" varchar, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("subscription_id") references "subscriptions"("id") on delete cascade);
CREATE TABLE "platform_transactions" ("id" integer primary key autoincrement not null, "reference" varchar not null, "subscription_invoice_id" integer, "provider" varchar not null, "provider_reference" varchar, "amount" numeric not null default '0', "currency" varchar not null default 'NGN', "status" varchar not null default 'pending', "meta" text, "paid_at" datetime, "processed_at" datetime, "attempts" integer not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("subscription_invoice_id") references "subscription_invoices"("id") on delete set null);
CREATE TABLE "platform_ledger_entries" ("id" integer primary key autoincrement not null, "platform_transaction_id" integer not null, "account" varchar not null, "type" varchar not null, "amount" numeric not null default '0', "currency" varchar not null default 'NGN', "description" varchar, "posted_at" datetime not null default CURRENT_TIMESTAMP, "created_at" datetime, "updated_at" datetime, foreign key("platform_transaction_id") references "platform_transactions"("id") on delete cascade);
CREATE TABLE "subscription_entitlements" ("id" integer primary key autoincrement not null, "subscription_id" integer not null, "module" varchar not null, "source" varchar not null default 'initial', "granted_at" datetime, "period_start" date, "period_end" date, "meta" text, "created_at" datetime, "updated_at" datetime, foreign key("subscription_id") references "subscriptions"("id") on delete cascade);
CREATE TABLE "license_units" ("id" integer primary key autoincrement not null, "subscription_id" integer not null, "unit_type" varchar not null, "quantity_granted" integer not null default '0', "period_start" date, "period_end" date, "meta" text, "created_at" datetime, "updated_at" datetime, foreign key("subscription_id") references "subscriptions"("id") on delete cascade);
CREATE TABLE "cashbook_entries" ("id" integer primary key autoincrement not null, "school_id" integer not null, "entry_date" date not null, "account" varchar not null, "type" varchar not null, "amount" numeric not null, "currency" varchar not null default 'NGN', "reference" varchar, "source" varchar not null default 'payment', "document_type" varchar, "document_id" integer, "description" varchar, "meta" text, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "payroll_profiles" ("id" integer primary key autoincrement not null, "school_id" integer not null, "staff_member_id" integer, "full_name" varchar not null, "employee_no" varchar, "designation" varchar, "basic_salary" numeric not null, "allowances" text, "deductions" text, "payment_method" varchar not null default 'bank_transfer', "bank_name" varchar, "account_name" varchar, "account_number" varchar, "effective_from" date, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("staff_member_id") references "staff_members"("id") on delete set null);
CREATE TABLE "payroll_runs" ("id" integer primary key autoincrement not null, "school_id" integer not null, "period_start" date not null, "period_end" date not null, "status" varchar not null default 'draft', "total_gross" numeric not null default '0', "total_deductions" numeric not null default '0', "total_net" numeric not null default '0', "approved_at" datetime, "paid_at" datetime, "cancelled_at" datetime, "cancel_reason" varchar, "created_by" integer, "notes" varchar, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "payroll_items" ("id" integer primary key autoincrement not null, "school_id" integer not null, "payroll_run_id" integer not null, "payroll_profile_id" integer, "staff_member_id" integer, "full_name" varchar not null, "basic_salary" numeric not null, "allowances_total" numeric not null default '0', "deductions_total" numeric not null default '0', "gross" numeric not null, "net" numeric not null, "allowances" text, "deductions" text, "payment_method" varchar, "bank_name" varchar, "account_name" varchar, "account_number" varchar, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade, foreign key("payroll_run_id") references "payroll_runs"("id") on delete cascade, foreign key("payroll_profile_id") references "payroll_profiles"("id") on delete set null, foreign key("staff_member_id") references "staff_members"("id") on delete set null);
CREATE TABLE "cbt_installations" ("id" integer primary key autoincrement not null, "school_id" integer not null, "status" varchar not null default 'not_entitled', "engine_version" varchar, "provisioned_at" datetime, "activated_at" datetime, "last_check_at" datetime, "meta" text, "error" text, "created_at" datetime, "updated_at" datetime, foreign key("school_id") references "schools"("id") on delete cascade);
CREATE TABLE "backup_runs" ("id" integer primary key autoincrement not null, "label" varchar, "status" varchar not null default 'running', "path" varchar, "size_bytes" integer not null default '0', "tables" text, "meta" text, "error" text, "completed_at" datetime, "created_at" datetime, "updated_at" datetime);
CREATE TABLE "landing_features" ("id" integer primary key autoincrement not null, "title" varchar not null, "slug" varchar, "description" text, "icon" varchar, "image_path" varchar, "link" varchar, "sort_order" integer not null default '0', "is_active" tinyint(1) not null default '1', "is_highlighted" tinyint(1) not null default '0', "in_orbit" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime);
CREATE TABLE "social_accounts" ("id" integer primary key autoincrement not null, "user_id" integer not null, "provider" varchar not null, "provider_id" varchar not null, "provider_email" varchar, "provider_nickname" varchar, "access_token" text, "refresh_token" text, "token_expires_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade);
INSERT INTO `activity_logs` (`id`,`school_id`,`user_id`,`action`,`subject_type`,`subject_id`,`data`,`ip_address`,`user_agent`,`created_at`,`updated_at`) VALUES (1,NULL,NULL,'created','App\\Models\\User',1,'{\"model\":\"User\",\"attributes\":{\"name\":\"Super Admin\",\"email\":\"sys@test.com\",\"password\":\"$2y$04$hOJ6.RAEU8ytTMTWleLOg.hcWcRdmE5VWZdi.\\/1Ser8fk37X58vma\",\"role\":\"platform_admin\",\"is_active\":true,\"email_verified_at\":\"2026-09-29 17:08:33\",\"updated_at\":\"2026-09-29 17:08:33\",\"created_at\":\"2026-09-29 17:08:33\",\"id\":1}}',NULL,NULL,'2026-09-29 17:08:33','2026-09-29 17:08:33');
INSERT INTO `backup_runs` (`id`,`label`,`status`,`path`,`size_bytes`,`tables`,`meta`,`error`,`completed_at`,`created_at`,`updated_at`) VALUES (1,'Fresh','running',NULL,0,NULL,NULL,NULL,NULL,'2026-09-29 17:08:33','2026-09-29 17:08:33');
INSERT INTO `email_logs` (`id`,`school_id`,`user_id`,`recipient_email`,`recipient_name`,`notification_type`,`subject`,`status`,`attempts`,`error`,`entity_type`,`entity_id`,`sent_at`,`failed_at`,`created_at`,`updated_at`,`payload`) VALUES (1,NULL,1,'sys@test.com','Super Admin','auth_welcome','Welcome to Pishon Educational Management','sent',1,NULL,NULL,NULL,'2026-09-29 17:08:33',NULL,'2026-09-29 17:08:33','2026-09-29 17:08:33',NULL);
INSERT INTO `landing_features` (`id`,`title`,`slug`,`description`,`icon`,`image_path`,`link`,`sort_order`,`is_active`,`is_highlighted`,`in_orbit`,`created_at`,`updated_at`) VALUES (1,'Attendance','attendance','Track daily attendance with ease.','calendar-days',NULL,NULL,1,1,1,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(2,'Result Management','result-management','Generate professional results and report cards.','document-text',NULL,NULL,2,1,1,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(3,'Admissions','admissions','Manage applications and student admission workflows.','building-library',NULL,NULL,4,1,1,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(4,'Finance','finance','Manage fees, payments and school finances.','banknotes',NULL,NULL,5,1,1,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(5,'Student Management','student-management','Keep every student record organised in one place.','users',NULL,NULL,6,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(6,'Parent Portal','parent-portal','Keep parents informed, engaged and up to date.','user-group',NULL,NULL,7,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(7,'Teacher Portal','teacher-portal','Empower teachers to teach, mark and plan.','pencil-square',NULL,NULL,8,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(8,'Communication','communication','Keep parents, students and staff connected.','chat-bubble',NULL,NULL,9,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(9,'Timetable','timetable','Build term timetables in minutes.','clock',NULL,NULL,10,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(10,'School Website','school-website','A beautiful public website on its own subdomain.','computer-desktop',NULL,NULL,11,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(11,'Reports','reports','Comprehensive administrative reports on demand.','chart-bar',NULL,NULL,12,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(12,'Payroll','payroll','Run staff payroll, allowances and deductions.','calculator',NULL,NULL,13,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22'),(13,'Notifications','notifications','Instant alerts for results, fees and announcements.','bell',NULL,NULL,14,1,0,1,'2026-09-29 17:07:22','2026-09-29 17:07:22');
INSERT INTO `migrations` (`id`,`migration`,`batch`) VALUES (1,'0001_01_01_000001_create_cache_table',1),(2,'0001_01_01_000002_create_jobs_table',1),(3,'2024_01_01_000001_create_plans_table',1),(4,'2024_01_01_000002_create_schools_table',1),(5,'2024_01_01_000003_create_users_table',1),(6,'2024_01_01_000004_create_subscriptions_table',1),(7,'2024_01_01_000005_create_platform_admins_table',1),(8,'2024_01_01_000006_create_activity_logs_table',1),(9,'2024_01_01_000010_create_academic_years_table',1),(10,'2024_01_01_000011_create_classes_table',1),(11,'2024_01_01_000012_create_subjects_table',1),(12,'2024_01_01_000020_create_students_table',1),(13,'2024_01_01_000021_create_teachers_table',1),(14,'2024_01_01_000022_create_parents_staff_table',1),(15,'2024_01_01_000030_create_attendance_table',1),(16,'2024_01_01_000031_create_timetables_table',1),(17,'2024_01_01_000032_create_exams_table',1),(18,'2024_01_01_000040_create_finance_tables',1),(19,'2024_01_01_000050_create_applications_table',1),(20,'2024_01_01_000050_create_communications_tables',1),(21,'2024_01_01_000060_create_cms_tables',1),(22,'2024_01_01_000070_create_permissions_tables',1),(23,'2024_01_01_000080_create_campuses_table',1),(24,'2024_01_01_000090_create_assessment_tables',1),(25,'2024_01_01_000100_create_notifications_table',1),(26,'2024_01_01_000101_create_website_system_tables',1),(27,'2024_01_01_000102_add_is_featured_to_news',1),(28,'2024_01_01_000103_create_website_slides',1),(29,'2024_01_01_000104_add_overlay_fields_to_website_slides',1),(30,'2024_01_01_000110_link_students_parent_to_parents_table',1),(31,'2024_01_01_000120_expand_students_and_portals',1),(32,'2024_01_01_000121_enquiry_extra_fields',1),(33,'2024_01_01_000122_teacher_expansion',1),(34,'2026_01_01_000001_add_owner_id_to_schools_table',1),(35,'2026_01_01_000001_create_platform_settings_table',1),(36,'2026_08_23_000002_add_result_workflow_fields_to_result_sheets_table',1),(37,'2026_08_23_000003_create_result_signatures_table',1),(38,'2026_08_23_000100_create_staff_management_tables',1),(39,'2026_08_24_000001_create_email_logs_table',1),(40,'2026_08_24_000002_create_notification_preferences_table',1),(41,'2026_08_24_000003_add_user_id_to_password_reset_tokens_table',1),(42,'2026_08_24_000004_add_payload_to_email_logs_table',1),(43,'2026_08_24_100001_expand_finance_system',1),(44,'2026_08_27_000001_add_type_to_contact_messages',1),(45,'2026_08_28_000001_create_academic_programs_table',1),(46,'2026_09_10_000001_create_admission_management_tables',1),(47,'2026_09_11_000001_create_exam_management_tables',1),(48,'2026_09_11_000001_create_payment_gateway_configs_table',1),(49,'2026_09_11_000002_extend_payment_transactions_for_webhooks',1),(50,'2026_09_11_000003_create_school_settlement_profiles_table',1),(51,'2026_09_11_000004_create_service_charge_rules_table',1),(52,'2026_09_11_000005_add_combined_support_to_service_charge_rules',1),(53,'2026_09_12_000001_create_platform_billing_tables',1),(54,'2026_09_12_000002_create_entitlements_and_license_units_tables',1),(55,'2026_09_12_000003_create_cashbook_entries_table',1),(56,'2026_09_12_000004_create_payroll_tables',1),(57,'2026_09_12_000005_backfill_role_permissions_for_builtin_roles',1),(58,'2026_09_14_000001_add_two_factor_columns_to_users_table',1),(59,'2026_09_14_000002_add_trial_warning_sent_at_to_schools_table',1),(60,'2026_09_15_000001_add_commercial_billing_to_plans_and_subscriptions_table',1),(61,'2026_09_15_000002_create_cbt_installations_table',1),(62,'2026_09_15_000003_add_query_indexes_table',1),(63,'2026_09_15_000004_create_backup_runs_table',1),(64,'2026_09_15_011602_add_unique_transaction_ref_to_payments_table',1),(65,'2026_09_15_011808_add_platform_ledger_idempotency_index',1),(66,'2026_09_15_012905_add_must_change_password_to_users_table',1),(67,'2026_09_15_020001_add_performance_indexes',1),(68,'2026_09_15_030001_add_data_consent_to_applications_table',1),(69,'2026_09_15_040001_create_landing_features_table',1),(70,'2026_09_16_000001_add_social_auth_columns',1),(71,'2026_09_17_000001_remove_cbt_marketing_exposure',1),(72,'2026_09_17_000002_add_gateway_mode_to_payment_gateway_configs',1);
INSERT INTO `users` (`id`,`school_id`,`name`,`email`,`email_verified_at`,`password`,`role`,`avatar`,`phone`,`is_active`,`remember_token`,`created_at`,`updated_at`,`two_factor_secret`,`two_factor_recovery_codes`,`two_factor_confirmed_at`,`must_change_password`,`provider`,`provider_id`) VALUES (1,NULL,'Super Admin','sys@test.com','2026-09-29 17:08:33','$2y$04$hOJ6.RAEU8ytTMTWleLOg.hcWcRdmE5VWZdi./1Ser8fk37X58vma','platform_admin',NULL,NULL,1,NULL,'2026-09-29 17:08:33','2026-09-29 17:08:33',NULL,NULL,NULL,0,NULL,NULL);