CREATE TABLE IF NOT EXISTS "migrations"(
  "id" integer primary key autoincrement not null,
  "migration" varchar not null,
  "batch" integer not null
);
CREATE TABLE IF NOT EXISTS "users"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "email" varchar not null,
  "email_verified_at" datetime,
  "password" varchar not null,
  "remember_token" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  "phone" varchar,
  "address" varchar,
  "user_type" varchar check("user_type" in('super_admin', 'admin', 'teacher', 'class_teacher', 'academic_coordinator', 'student', 'parent', 'bursar', 'librarian', 'boarding_staff')) not null default 'student',
  "status" varchar check("status" in('active', 'inactive', 'suspended')) not null default 'active',
  "profile_photo" varchar,
  "last_login_at" datetime,
  "deleted_at" datetime
);
CREATE UNIQUE INDEX "users_email_unique" on "users"("email");
CREATE TABLE IF NOT EXISTS "password_reset_tokens"(
  "email" varchar not null,
  "token" varchar not null,
  "created_at" datetime,
  primary key("email")
);
CREATE TABLE IF NOT EXISTS "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 INDEX "sessions_user_id_index" on "sessions"("user_id");
CREATE INDEX "sessions_last_activity_index" on "sessions"("last_activity");
CREATE TABLE IF NOT EXISTS "cache"(
  "key" varchar not null,
  "value" text not null,
  "expiration" integer not null,
  primary key("key")
);
CREATE TABLE IF NOT EXISTS "cache_locks"(
  "key" varchar not null,
  "owner" varchar not null,
  "expiration" integer not null,
  primary key("key")
);
CREATE TABLE IF NOT EXISTS "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 INDEX "jobs_queue_index" on "jobs"("queue");
CREATE TABLE IF NOT EXISTS "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 IF NOT EXISTS "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 UNIQUE INDEX "failed_jobs_uuid_unique" on "failed_jobs"("uuid");
CREATE TABLE IF NOT EXISTS "rooms"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "room_number" varchar not null,
  "room_type" varchar not null default 'classroom',
  "capacity" integer,
  "building" varchar,
  "floor" varchar,
  "facilities" text,
  "is_available" tinyint(1) not null default '1',
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE UNIQUE INDEX "rooms_room_number_unique" on "rooms"("room_number");
CREATE TABLE IF NOT EXISTS "timetable_periods"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "start_time" time not null,
  "end_time" time not null,
  "sequence" integer not null default '0',
  "is_break" tinyint(1) not null default '0',
  "period_type" varchar not null default 'lesson',
  "days_of_week" text not null,
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "timetables"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "effective_from" date not null,
  "effective_to" date not null,
  "status" varchar check("status" in('draft', 'active', 'archived')) not null default 'draft',
  "is_published" tinyint(1) not null default '0',
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "timetable_sessions"(
  "id" integer primary key autoincrement not null,
  "timetable_id" integer not null,
  "class_id" integer not null,
  "subject_id" integer not null,
  "teacher_id" integer not null,
  "room_id" integer,
  "period_id" integer not null,
  "day_of_week" integer not null,
  "session_date" date,
  "notes" text,
  "is_substitution" tinyint(1) not null default '0',
  "original_teacher_id" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("timetable_id") references "timetables"("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 "staff"("id") on delete cascade,
  foreign key("room_id") references "rooms"("id") on delete set null,
  foreign key("period_id") references "timetable_periods"("id") on delete cascade,
  foreign key("original_teacher_id") references "staff"("id") on delete set null
);
CREATE UNIQUE INDEX "unique_class_session" on "timetable_sessions"(
  "timetable_id",
  "class_id",
  "day_of_week",
  "period_id"
);
CREATE TABLE IF NOT EXISTS "site_settings"(
  "id" integer primary key autoincrement not null,
  "key" varchar not null,
  "value" text,
  "type" varchar not null default 'text',
  "category" varchar not null default 'general',
  "label" varchar,
  "description" text,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "site_settings_key_unique" on "site_settings"("key");
CREATE TABLE IF NOT EXISTS "personal_access_tokens"(
  "id" integer primary key autoincrement not null,
  "tokenable_type" varchar not null,
  "tokenable_id" integer not null,
  "name" text not null,
  "token" varchar not null,
  "abilities" text,
  "last_used_at" datetime,
  "expires_at" datetime,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE INDEX "personal_access_tokens_tokenable_type_tokenable_id_index" on "personal_access_tokens"(
  "tokenable_type",
  "tokenable_id"
);
CREATE UNIQUE INDEX "personal_access_tokens_token_unique" on "personal_access_tokens"(
  "token"
);
CREATE INDEX "personal_access_tokens_expires_at_index" on "personal_access_tokens"(
  "expires_at"
);
CREATE TABLE IF NOT EXISTS "permissions"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "guard_name" varchar not null,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "permissions_name_guard_name_unique" on "permissions"(
  "name",
  "guard_name"
);
CREATE TABLE IF NOT EXISTS "roles"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "guard_name" varchar not null,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "roles_name_guard_name_unique" on "roles"(
  "name",
  "guard_name"
);
CREATE TABLE IF NOT EXISTS "model_has_permissions"(
  "permission_id" integer not null,
  "model_type" varchar not null,
  "model_id" integer not null,
  foreign key("permission_id") references "permissions"("id") on delete cascade,
  primary key("permission_id", "model_id", "model_type")
);
CREATE INDEX "model_has_permissions_model_id_model_type_index" on "model_has_permissions"(
  "model_id",
  "model_type"
);
CREATE TABLE IF NOT EXISTS "model_has_roles"(
  "role_id" integer not null,
  "model_type" varchar not null,
  "model_id" integer not null,
  foreign key("role_id") references "roles"("id") on delete cascade,
  primary key("role_id", "model_id", "model_type")
);
CREATE INDEX "model_has_roles_model_id_model_type_index" on "model_has_roles"(
  "model_id",
  "model_type"
);
CREATE TABLE IF NOT EXISTS "role_has_permissions"(
  "permission_id" integer not null,
  "role_id" integer not null,
  foreign key("permission_id") references "permissions"("id") on delete cascade,
  foreign key("role_id") references "roles"("id") on delete cascade,
  primary key("permission_id", "role_id")
);
CREATE TABLE IF NOT EXISTS "audit_logs"(
  "id" integer primary key autoincrement not null,
  "user_id" integer,
  "action" varchar not null,
  "model_type" varchar,
  "model_id" integer,
  "old_values" text,
  "new_values" text,
  "ip_address" varchar,
  "user_agent" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("user_id") references "users"("id") on delete set null
);
CREATE INDEX "audit_logs_user_id_created_at_index" on "audit_logs"(
  "user_id",
  "created_at"
);
CREATE INDEX "audit_logs_model_type_model_id_index" on "audit_logs"(
  "model_type",
  "model_id"
);
CREATE TABLE IF NOT EXISTS "academic_years"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "start_date" date not null,
  "end_date" date not null,
  "is_current" tinyint(1) not null default '0',
  "status" varchar check("status" in('active', 'completed', 'upcoming')) not null default 'upcoming',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE TABLE IF NOT EXISTS "terms"(
  "id" integer primary key autoincrement not null,
  "academic_year_id" integer not null,
  "name" varchar not null,
  "term_number" integer not null,
  "start_date" date not null,
  "end_date" date not null,
  "is_current" tinyint(1) not null default '0',
  "status" varchar check("status" in('active', 'completed', 'upcoming')) not null default 'upcoming',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "streams"(
  "id" integer primary key autoincrement not null,
  "class_id" integer not null,
  "name" varchar not null,
  "code" varchar not null,
  "capacity" integer not null default '40',
  "class_teacher_id" integer,
  "classroom" varchar,
  "status" varchar check("status" in('active', 'inactive')) not null default 'active',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("class_teacher_id") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "streams_class_id_name_unique" on "streams"(
  "class_id",
  "name"
);
CREATE TABLE IF NOT EXISTS "subjects"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "category" varchar check("category" in('core', 'elective', 'optional')) not null default 'core',
  "description" text,
  "has_practical" tinyint(1) not null default '0',
  "credit_hours" integer,
  "status" varchar check("status" in('active', 'inactive')) not null default 'active',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  "pass_mark" integer
);
CREATE UNIQUE INDEX "subjects_code_unique" on "subjects"("code");
CREATE TABLE IF NOT EXISTS "students"(
  "id" integer primary key autoincrement not null,
  "user_id" integer not null,
  "admission_number" varchar not null,
  "admission_date" date not null,
  "current_class_id" integer,
  "current_stream_id" integer,
  "first_name" varchar not null,
  "middle_name" varchar,
  "last_name" varchar not null,
  "date_of_birth" date not null,
  "gender" varchar check("gender" in('male', 'female', 'other')) not null,
  "birth_certificate_number" varchar,
  "national_id" varchar,
  "religion" varchar,
  "nationality" varchar not null default 'Kenyan',
  "phone" varchar,
  "email" varchar,
  "physical_address" text,
  "county" varchar,
  "sub_county" varchar,
  "medical_conditions" text,
  "allergies" text,
  "blood_group" varchar,
  "special_needs" text,
  "previous_school" varchar,
  "previous_class" varchar,
  "transfer_reason" text,
  "status" varchar check("status" in('active', 'transferred', 'graduated', 'suspended', 'expelled', 'deceased')) not null default 'active',
  "status_date" date,
  "status_reason" text,
  "photo" varchar,
  "documents" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("user_id") references "users"("id") on delete cascade,
  foreign key("current_class_id") references "classes"("id") on delete set null,
  foreign key("current_stream_id") references "streams"("id") on delete set null
);
CREATE INDEX "students_admission_number_status_index" on "students"(
  "admission_number",
  "status"
);
CREATE INDEX "students_current_class_id_current_stream_id_index" on "students"(
  "current_class_id",
  "current_stream_id"
);
CREATE UNIQUE INDEX "students_admission_number_unique" on "students"(
  "admission_number"
);
CREATE TABLE IF NOT EXISTS "parents"(
  "id" integer primary key autoincrement not null,
  "user_id" integer,
  "first_name" varchar not null,
  "middle_name" varchar,
  "last_name" varchar not null,
  "national_id" varchar,
  "gender" varchar check("gender" in('male', 'female', 'other')) not null,
  "phone" varchar not null,
  "phone_secondary" varchar,
  "email" varchar,
  "physical_address" text,
  "county" varchar,
  "sub_county" varchar,
  "occupation" varchar,
  "employer" varchar,
  "work_phone" varchar,
  "is_emergency_contact" tinyint(1) not null default '1',
  "status" varchar check("status" in('active', 'inactive')) not null default 'active',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("user_id") references "users"("id") on delete set null
);
CREATE INDEX "parents_phone_index" on "parents"("phone");
CREATE INDEX "parents_email_index" on "parents"("email");
CREATE TABLE IF NOT EXISTS "student_parent"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "parent_id" integer not null,
  "relationship" varchar check("relationship" in('father', 'mother', 'guardian', 'sibling', 'other')) not null,
  "is_primary" tinyint(1) not null default '0',
  "financial_responsibility" tinyint(1) not null default '1',
  "can_pick_student" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("parent_id") references "parents"("id") on delete cascade
);
CREATE UNIQUE INDEX "student_parent_student_id_parent_id_unique" on "student_parent"(
  "student_id",
  "parent_id"
);
CREATE TABLE IF NOT EXISTS "staff"(
  "id" integer primary key autoincrement not null,
  "user_id" integer not null,
  "staff_number" varchar not null,
  "first_name" varchar not null,
  "middle_name" varchar,
  "last_name" varchar not null,
  "date_of_birth" date not null,
  "gender" varchar check("gender" in('male', 'female', 'other')) not null,
  "national_id" varchar not null,
  "passport_number" varchar,
  "religion" varchar,
  "nationality" varchar not null default 'Kenyan',
  "marital_status" varchar check("marital_status" in('single', 'married', 'divorced', 'widowed')),
  "phone" varchar not null,
  "phone_secondary" varchar,
  "email" varchar not null,
  "physical_address" text,
  "county" varchar,
  "sub_county" varchar,
  "emergency_contact_name" varchar,
  "emergency_contact_phone" varchar,
  "emergency_contact_relationship" varchar,
  "employment_type" varchar check("employment_type" in('permanent', 'contract', 'part_time', 'temporary')) not null default 'permanent',
  "job_title" varchar not null,
  "staff_type" varchar check("staff_type" in('teaching', 'non_teaching', 'administrative', 'support')) not null,
  "department" varchar,
  "employment_start_date" date not null,
  "employment_end_date" date,
  "probation_end_date" date,
  "contract_number" varchar,
  "highest_qualification" varchar,
  "registration_number" varchar,
  "certifications" text,
  "specializations" text,
  "bank_name" varchar,
  "bank_account_number" varchar,
  "bank_branch" varchar,
  "tax_pin" varchar,
  "nssf_number" varchar,
  "nhif_number" varchar,
  "status" varchar check("status" in('active', 'on_leave', 'suspended', 'terminated', 'retired')) not null default 'active',
  "photo" varchar,
  "documents" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("user_id") references "users"("id") on delete cascade
);
CREATE INDEX "staff_staff_number_status_index" on "staff"(
  "staff_number",
  "status"
);
CREATE INDEX "staff_staff_type_index" on "staff"("staff_type");
CREATE UNIQUE INDEX "staff_staff_number_unique" on "staff"("staff_number");
CREATE UNIQUE INDEX "staff_national_id_unique" on "staff"("national_id");
CREATE UNIQUE INDEX "staff_email_unique" on "staff"("email");
CREATE TABLE IF NOT EXISTS "class_subject"(
  "id" integer primary key autoincrement not null,
  "class_id" integer not null,
  "subject_id" integer not null,
  "is_compulsory" tinyint(1) not null default '1',
  "periods_per_week" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete cascade
);
CREATE UNIQUE INDEX "class_subject_class_id_subject_id_unique" on "class_subject"(
  "class_id",
  "subject_id"
);
CREATE TABLE IF NOT EXISTS "teacher_subject_assignments"(
  "id" integer primary key autoincrement not null,
  "teacher_id" integer not null,
  "subject_id" integer not null,
  "class_id" integer not null,
  "stream_id" integer,
  "academic_year_id" integer not null,
  "term_id" integer,
  "periods_per_week" integer,
  "status" varchar check("status" in('active', 'completed', 'suspended')) not null default 'active',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("teacher_id") references "users"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("stream_id") references "streams"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade
);
CREATE INDEX "teacher_subject_assignments_teacher_id_academic_year_id_index" on "teacher_subject_assignments"(
  "teacher_id",
  "academic_year_id"
);
CREATE INDEX "teacher_subject_assignments_class_id_subject_id_index" on "teacher_subject_assignments"(
  "class_id",
  "subject_id"
);
CREATE TABLE IF NOT EXISTS "attendances"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "class_id" integer not null,
  "stream_id" integer,
  "date" date not null,
  "status" varchar check("status" in('present', 'absent', 'late', 'excused', 'sick')) not null default 'present',
  "time_in" time,
  "time_out" time,
  "reason" text,
  "marked_by" integer not null,
  "remarks" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("stream_id") references "streams"("id") on delete set null,
  foreign key("marked_by") references "users"("id") on delete cascade
);
CREATE UNIQUE INDEX "attendances_student_id_date_unique" on "attendances"(
  "student_id",
  "date"
);
CREATE INDEX "attendances_date_class_id_index" on "attendances"(
  "date",
  "class_id"
);
CREATE INDEX "attendances_student_id_status_index" on "attendances"(
  "student_id",
  "status"
);
CREATE TABLE IF NOT EXISTS "classes"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "level" varchar,
  "capacity" integer,
  "description" text,
  "status" varchar not null default('active'),
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  "room_number" varchar
);
CREATE UNIQUE INDEX "classes_code_unique" on "classes"("code");
CREATE TABLE IF NOT EXISTS "fee_structures"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "class_id" integer not null,
  "academic_year_id" integer not null,
  "term_id" integer,
  "fee_type" varchar check("fee_type" in('tuition', 'boarding', 'transport', 'meals', 'exam', 'activities', 'other')) not null,
  "amount" numeric not null,
  "is_compulsory" tinyint(1) not null default '1',
  "description" text,
  "status" varchar check("status" in('active', 'inactive')) not null default 'active',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  "fee_category" varchar check("fee_category" in('tuition', 'boarding', 'transport', 'meals', 'exam', 'activities', 'uniform', 'books', 'laboratory', 'medical', 'other')) not null default 'tuition',
  "allow_installments" tinyint(1) not null default '0',
  "installment_count" integer,
  "applicability" varchar check("applicability" in('all', 'day_scholars', 'boarders')) not null default 'all',
  "payment_deadline" date,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade
);
CREATE INDEX "fee_structures_class_id_academic_year_id_term_id_index" on "fee_structures"(
  "class_id",
  "academic_year_id",
  "term_id"
);
CREATE TABLE IF NOT EXISTS "fee_invoices"(
  "id" integer primary key autoincrement not null,
  "invoice_number" varchar not null,
  "student_id" integer not null,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "invoice_date" date not null,
  "due_date" date not null,
  "total_amount" numeric not null,
  "paid_amount" numeric not null default '0',
  "balance" numeric not null,
  "status" varchar check("status" in('pending', 'partial', 'paid', 'overdue', 'cancelled')) not null default 'pending',
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade
);
CREATE INDEX "fee_invoices_student_id_status_index" on "fee_invoices"(
  "student_id",
  "status"
);
CREATE INDEX "fee_invoices_academic_year_id_term_id_index" on "fee_invoices"(
  "academic_year_id",
  "term_id"
);
CREATE UNIQUE INDEX "fee_invoices_invoice_number_unique" on "fee_invoices"(
  "invoice_number"
);
CREATE TABLE IF NOT EXISTS "fee_invoice_items"(
  "id" integer primary key autoincrement not null,
  "invoice_id" integer not null,
  "fee_structure_id" integer not null,
  "description" varchar not null,
  "amount" numeric not null,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("invoice_id") references "fee_invoices"("id") on delete cascade,
  foreign key("fee_structure_id") references "fee_structures"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "fee_payments"(
  "id" integer primary key autoincrement not null,
  "receipt_number" varchar not null,
  "invoice_id" integer not null,
  "student_id" integer not null,
  "payment_date" date not null,
  "amount" numeric not null,
  "payment_method" varchar check("payment_method" in('cash', 'bank_transfer', 'cheque', 'mobile_money', 'card', 'online')) not null,
  "transaction_reference" varchar,
  "bank_name" varchar,
  "cheque_number" varchar,
  "remarks" text,
  "received_by" integer not null,
  "status" varchar check("status" in('completed', 'pending', 'failed', 'reversed')) not null default 'completed',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("invoice_id") references "fee_invoices"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("received_by") references "users"("id") on delete cascade
);
CREATE INDEX "fee_payments_student_id_payment_date_index" on "fee_payments"(
  "student_id",
  "payment_date"
);
CREATE INDEX "fee_payments_receipt_number_index" on "fee_payments"(
  "receipt_number"
);
CREATE UNIQUE INDEX "fee_payments_receipt_number_unique" on "fee_payments"(
  "receipt_number"
);
CREATE TABLE IF NOT EXISTS "student_documents"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "document_type" varchar not null,
  "file_name" varchar not null,
  "file_path" varchar not null,
  "mime_type" varchar,
  "file_size" integer,
  "description" text,
  "uploaded_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("uploaded_by") references "users"("id") on delete set null
);
CREATE INDEX "student_documents_student_id_index" on "student_documents"(
  "student_id"
);
CREATE INDEX "student_documents_document_type_index" on "student_documents"(
  "document_type"
);
CREATE TABLE IF NOT EXISTS "assessment_types"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "description" text,
  "max_marks" numeric not null default '100',
  "weight_percentage" numeric not null default '0',
  "category" varchar check("category" in('continuous', 'examination')) not null default 'continuous',
  "academic_year_id" integer not null,
  "term_id" integer,
  "sequence" integer not null default '0',
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade
);
CREATE INDEX "assessment_types_academic_year_id_term_id_index" on "assessment_types"(
  "academic_year_id",
  "term_id"
);
CREATE UNIQUE INDEX "assessment_types_code_unique" on "assessment_types"(
  "code"
);
CREATE TABLE IF NOT EXISTS "examinations"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "assessment_type_id" integer not null,
  "start_date" date not null,
  "end_date" date not null,
  "marks_submission_deadline" date,
  "status" varchar check("status" in('scheduled', 'ongoing', 'completed', 'cancelled')) not null default 'scheduled',
  "instructions" text,
  "is_published" tinyint(1) not null default '0',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade,
  foreign key("assessment_type_id") references "assessment_types"("id") on delete cascade
);
CREATE INDEX "examinations_academic_year_id_term_id_index" on "examinations"(
  "academic_year_id",
  "term_id"
);
CREATE INDEX "examinations_status_index" on "examinations"("status");
CREATE TABLE IF NOT EXISTS "examination_schedules"(
  "id" integer primary key autoincrement not null,
  "examination_id" integer not null,
  "subject_id" integer not null,
  "class_id" integer not null,
  "exam_date" date not null,
  "start_time" time not null,
  "end_time" time not null,
  "venue" varchar,
  "invigilator" varchar,
  "special_instructions" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("examination_id") references "examinations"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade
);
CREATE UNIQUE INDEX "examination_schedules_examination_id_subject_id_class_id_unique" on "examination_schedules"(
  "examination_id",
  "subject_id",
  "class_id"
);
CREATE INDEX "examination_schedules_exam_date_index" on "examination_schedules"(
  "exam_date"
);
CREATE TABLE IF NOT EXISTS "marks"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "subject_id" integer not null,
  "class_id" integer not null,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "assessment_type_id" integer not null,
  "examination_id" integer,
  "marks_obtained" numeric not null,
  "max_marks" numeric not null,
  "grade" varchar,
  "remarks" text,
  "entered_by" integer not null,
  "entered_at" datetime not null default CURRENT_TIMESTAMP,
  "verified_by" integer,
  "verified_at" datetime,
  "status" varchar check("status" in('draft', 'submitted', 'verified', 'approved')) not null default 'draft',
  "approved_by" integer,
  "approved_at" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade,
  foreign key("assessment_type_id") references "assessment_types"("id") on delete cascade,
  foreign key("examination_id") references "examinations"("id") on delete cascade,
  foreign key("entered_by") references "users"("id") on delete cascade,
  foreign key("verified_by") references "users"("id") on delete set null,
  foreign key("approved_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "marks_unique" on "marks"(
  "student_id",
  "subject_id",
  "assessment_type_id",
  "term_id",
  "academic_year_id"
);
CREATE INDEX "marks_student_id_term_id_academic_year_id_index" on "marks"(
  "student_id",
  "term_id",
  "academic_year_id"
);
CREATE INDEX "marks_class_id_subject_id_term_id_index" on "marks"(
  "class_id",
  "subject_id",
  "term_id"
);
CREATE INDEX "marks_status_index" on "marks"("status");
CREATE TABLE IF NOT EXISTS "marks_audit"(
  "id" integer primary key autoincrement not null,
  "mark_id" integer not null,
  "old_marks" numeric,
  "new_marks" numeric not null,
  "old_grade" varchar,
  "new_grade" varchar,
  "changed_by" integer not null,
  "reason" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("mark_id") references "marks"("id") on delete cascade,
  foreign key("changed_by") references "users"("id") on delete cascade
);
CREATE INDEX "marks_audit_mark_id_index" on "marks_audit"("mark_id");
CREATE TABLE IF NOT EXISTS "grading_scales"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "is_default" tinyint(1) not null default '0',
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime,
  "scale_type" varchar not null default 'percentage',
  "grade_point_settings" text
);
CREATE TABLE IF NOT EXISTS "grading_scale_ranges"(
  "id" integer primary key autoincrement not null,
  "grading_scale_id" integer not null,
  "grade" varchar not null,
  "min_marks" numeric not null,
  "max_marks" numeric not null,
  "grade_point" numeric,
  "division" varchar,
  "interpretation" varchar,
  "color_code" varchar,
  "sequence" integer not null default '0',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("grading_scale_id") references "grading_scales"("id") on delete cascade
);
CREATE INDEX "grading_scale_ranges_grading_scale_id_index" on "grading_scale_ranges"(
  "grading_scale_id"
);
CREATE TABLE IF NOT EXISTS "subject_pass_marks"(
  "id" integer primary key autoincrement not null,
  "subject_id" integer not null,
  "class_id" integer not null,
  "academic_year_id" integer not null,
  "pass_mark" numeric not null default '50',
  "grading_scale_id" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("grading_scale_id") references "grading_scales"("id") on delete set null
);
CREATE UNIQUE INDEX "subject_pass_marks_subject_id_class_id_academic_year_id_unique" on "subject_pass_marks"(
  "subject_id",
  "class_id",
  "academic_year_id"
);
CREATE TABLE IF NOT EXISTS "student_results"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "subject_id" integer not null,
  "class_id" integer not null,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "total_marks" numeric not null,
  "max_marks" numeric not null,
  "percentage" numeric not null,
  "grade" varchar not null,
  "grade_point" numeric,
  "class_position" integer,
  "stream_position" integer,
  "class_average" numeric,
  "teacher_remarks" text,
  "is_passed" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade
);
CREATE UNIQUE INDEX "student_subject_result_unique" on "student_results"(
  "student_id",
  "subject_id",
  "term_id",
  "academic_year_id"
);
CREATE INDEX "student_results_student_id_term_id_academic_year_id_index" on "student_results"(
  "student_id",
  "term_id",
  "academic_year_id"
);
CREATE INDEX "student_results_class_id_term_id_index" on "student_results"(
  "class_id",
  "term_id"
);
CREATE TABLE IF NOT EXISTS "student_term_results"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "class_id" integer not null,
  "stream_id" integer,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "total_marks" numeric not null,
  "max_marks" numeric not null,
  "average_marks" numeric not null,
  "percentage" numeric not null,
  "gpa" numeric,
  "division" varchar,
  "class_position" integer,
  "stream_position" integer,
  "total_students_in_class" integer not null,
  "subjects_taken" integer not null,
  "subjects_passed" integer not null,
  "subjects_failed" integer not null,
  "days_present" integer not null default '0',
  "days_absent" integer not null default '0',
  "attendance_percentage" numeric not null default '0',
  "class_teacher_remarks" text,
  "principal_remarks" text,
  "promoted" tinyint(1),
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("stream_id") references "streams"("id") on delete set null,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade
);
CREATE UNIQUE INDEX "student_term_result_unique" on "student_term_results"(
  "student_id",
  "term_id",
  "academic_year_id"
);
CREATE INDEX "student_term_results_class_id_term_id_academic_year_id_index" on "student_term_results"(
  "class_id",
  "term_id",
  "academic_year_id"
);
CREATE TABLE IF NOT EXISTS "report_card_templates"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "type" varchar check("type" in('primary', 'secondary', 'custom')) not null default 'primary',
  "layout_config" text,
  "show_attendance" tinyint(1) not null default '1',
  "show_discipline" tinyint(1) not null default '1',
  "show_positions" tinyint(1) not null default '1',
  "show_class_average" tinyint(1) not null default '1',
  "show_grade_key" tinyint(1) not null default '1',
  "logo_path" varchar,
  "signature_path" varchar,
  "is_default" tinyint(1) not null default '0',
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "report_cards"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "report_card_template_id" integer,
  "student_term_result_id" integer not null,
  "report_number" varchar not null,
  "status" varchar check("status" in('draft', 'pending_approval', 'approved', 'published')) not null default 'draft',
  "generated_by" integer not null,
  "generated_at" datetime not null default CURRENT_TIMESTAMP,
  "approved_by" integer,
  "approved_at" datetime,
  "published_at" datetime,
  "pdf_path" varchar,
  "parent_viewed" tinyint(1) not null default '0',
  "parent_viewed_at" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade,
  foreign key("report_card_template_id") references "report_card_templates"("id") on delete set null,
  foreign key("student_term_result_id") references "student_term_results"("id") on delete cascade,
  foreign key("generated_by") references "users"("id") on delete cascade,
  foreign key("approved_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "report_card_unique" on "report_cards"(
  "student_id",
  "term_id",
  "academic_year_id"
);
CREATE INDEX "report_cards_status_term_id_index" on "report_cards"(
  "status",
  "term_id"
);
CREATE UNIQUE INDEX "report_cards_report_number_unique" on "report_cards"(
  "report_number"
);
CREATE TABLE IF NOT EXISTS "teacher_remarks"(
  "id" integer primary key autoincrement not null,
  "student_result_id" integer not null,
  "teacher_id" integer not null,
  "remarks" text not null,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_result_id") references "student_results"("id") on delete cascade,
  foreign key("teacher_id") references "users"("id") on delete cascade
);
CREATE INDEX "teacher_remarks_student_result_id_index" on "teacher_remarks"(
  "student_result_id"
);
CREATE TABLE IF NOT EXISTS "subject_performance_analytics"(
  "id" integer primary key autoincrement not null,
  "subject_id" integer not null,
  "class_id" integer,
  "stream_id" integer,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "total_students" integer not null default '0',
  "average_score" numeric,
  "highest_score" numeric,
  "lowest_score" numeric,
  "median_score" numeric,
  "standard_deviation" numeric,
  "grade_a_count" integer not null default '0',
  "grade_b_count" integer not null default '0',
  "grade_c_count" integer not null default '0',
  "grade_d_count" integer not null default '0',
  "grade_e_count" integer not null default '0',
  "grade_f_count" integer not null default '0',
  "pass_count" integer not null default '0',
  "fail_count" integer not null default '0',
  "pass_rate" numeric,
  "teacher_id" integer,
  "teacher_effectiveness_score" numeric,
  "improvement_from_previous_term" numeric,
  "trend" varchar check("trend" in('improving', 'declining', 'stable')),
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("stream_id") references "streams"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade,
  foreign key("teacher_id") references "staff"("id") on delete set null
);
CREATE UNIQUE INDEX "subject_analytics_unique" on "subject_performance_analytics"(
  "subject_id",
  "class_id",
  "stream_id",
  "academic_year_id",
  "term_id"
);
CREATE INDEX "subject_performance_analytics_academic_year_id_term_id_index" on "subject_performance_analytics"(
  "academic_year_id",
  "term_id"
);
CREATE INDEX "subject_performance_analytics_class_id_term_id_index" on "subject_performance_analytics"(
  "class_id",
  "term_id"
);
CREATE INDEX "subject_performance_analytics_teacher_id_index" on "subject_performance_analytics"(
  "teacher_id"
);
CREATE TABLE IF NOT EXISTS "class_comparisons"(
  "id" integer primary key autoincrement not null,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "class_id" integer not null,
  "stream_id" integer,
  "total_students" integer not null default '0',
  "class_average" numeric,
  "class_gpa" numeric,
  "division_1_count" integer not null default '0',
  "division_2_count" integer not null default '0',
  "division_3_count" integer not null default '0',
  "division_4_count" integer not null default '0',
  "division_u_count" integer not null default '0',
  "subject_averages" text,
  "top_performing_subjects" text,
  "weak_performing_subjects" text,
  "rank_in_school" integer,
  "improvement_from_previous_term" numeric,
  "trend" varchar check("trend" in('improving', 'declining', 'stable')),
  "average_attendance_rate" numeric,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("stream_id") references "streams"("id") on delete cascade
);
CREATE UNIQUE INDEX "class_comparison_unique" on "class_comparisons"(
  "class_id",
  "stream_id",
  "academic_year_id",
  "term_id"
);
CREATE INDEX "class_comparisons_academic_year_id_term_id_index" on "class_comparisons"(
  "academic_year_id",
  "term_id"
);
CREATE INDEX "class_comparisons_class_id_index" on "class_comparisons"(
  "class_id"
);
CREATE TABLE IF NOT EXISTS "performance_trends"(
  "id" integer primary key autoincrement not null,
  "student_id" integer,
  "class_id" integer,
  "subject_id" integer,
  "trend_type" varchar not null,
  "data_points" text not null,
  "trend_direction" varchar check("trend_direction" in('improving', 'declining', 'stable', 'volatile')),
  "trend_coefficient" numeric,
  "average_growth_rate" numeric,
  "volatility_index" numeric,
  "predicted_next_term" numeric,
  "confidence_level" numeric,
  "analysis_start_date" date not null,
  "analysis_end_date" date not null,
  "data_points_count" integer not null default '0',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete cascade
);
CREATE INDEX "performance_trends_student_id_trend_type_index" on "performance_trends"(
  "student_id",
  "trend_type"
);
CREATE INDEX "performance_trends_class_id_trend_type_index" on "performance_trends"(
  "class_id",
  "trend_type"
);
CREATE INDEX "performance_trends_subject_id_trend_type_index" on "performance_trends"(
  "subject_id",
  "trend_type"
);
CREATE INDEX "performance_trends_trend_type_index" on "performance_trends"(
  "trend_type"
);
CREATE TABLE IF NOT EXISTS "academic_insights"(
  "id" integer primary key autoincrement not null,
  "insight_type" varchar not null,
  "severity" varchar check("severity" in('low', 'medium', 'high', 'critical')) not null,
  "status" varchar check("status" in('active', 'acknowledged', 'resolved')) not null default 'active',
  "student_id" integer,
  "class_id" integer,
  "subject_id" integer,
  "teacher_id" integer,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "title" varchar not null,
  "description" text not null,
  "metrics" text,
  "recommendations" text,
  "action_taken" text,
  "acknowledged_by" integer,
  "acknowledged_at" datetime,
  "resolved_by" integer,
  "resolved_at" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("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 "staff"("id") on delete set null,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade,
  foreign key("acknowledged_by") references "users"("id") on delete set null,
  foreign key("resolved_by") references "users"("id") on delete set null
);
CREATE INDEX "academic_insights_status_severity_index" on "academic_insights"(
  "status",
  "severity"
);
CREATE INDEX "academic_insights_academic_year_id_term_id_index" on "academic_insights"(
  "academic_year_id",
  "term_id"
);
CREATE INDEX "academic_insights_student_id_status_index" on "academic_insights"(
  "student_id",
  "status"
);
CREATE INDEX "academic_insights_class_id_status_index" on "academic_insights"(
  "class_id",
  "status"
);
CREATE TABLE IF NOT EXISTS "exam_seating_arrangements"(
  "id" integer primary key autoincrement not null,
  "examination_id" integer not null,
  "examination_schedule_id" integer not null,
  "student_id" integer not null,
  "room_id" integer,
  "seat_number" varchar not null,
  "row_number" integer,
  "column_number" integer,
  "special_instructions" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("examination_id") references "examinations"("id") on delete cascade,
  foreign key("examination_schedule_id") references "examination_schedules"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("room_id") references "rooms"("id") on delete set null
);
CREATE UNIQUE INDEX "seating_schedule_student_unique" on "exam_seating_arrangements"(
  "examination_schedule_id",
  "student_id"
);
CREATE UNIQUE INDEX "seating_room_seat_unique" on "exam_seating_arrangements"(
  "examination_schedule_id",
  "room_id",
  "seat_number"
);
CREATE INDEX "exam_seating_arrangements_examination_id_room_id_index" on "exam_seating_arrangements"(
  "examination_id",
  "room_id"
);
CREATE TABLE IF NOT EXISTS "payment_gateways"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "type" varchar check("type" in('card', 'mobile_money', 'bank_transfer')) not null,
  "is_active" tinyint(1) not null default '0',
  "credentials" text,
  "settings" text,
  "webhook_url" text,
  "transaction_fee_percentage" numeric not null default '0',
  "transaction_fee_fixed" numeric not null default '0',
  "currency" varchar not null default 'UGX',
  "description" text,
  "display_order" integer not null default '0',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE INDEX "payment_gateways_code_index" on "payment_gateways"("code");
CREATE INDEX "payment_gateways_is_active_index" on "payment_gateways"(
  "is_active"
);
CREATE UNIQUE INDEX "payment_gateways_code_unique" on "payment_gateways"(
  "code"
);
CREATE TABLE IF NOT EXISTS "payment_transactions"(
  "id" integer primary key autoincrement not null,
  "transaction_id" varchar not null,
  "fee_payment_id" integer,
  "student_id" integer not null,
  "invoice_id" integer not null,
  "gateway_id" integer,
  "gateway_transaction_id" varchar,
  "amount" numeric not null,
  "transaction_fee" numeric not null default '0',
  "net_amount" numeric not null,
  "currency" varchar not null default 'UGX',
  "status" varchar check("status" in('pending', 'processing', 'completed', 'failed', 'cancelled', 'refunded')) not null default 'pending',
  "payment_method" varchar check("payment_method" in('cash', 'bank_transfer', 'cheque', 'mobile_money', 'card', 'online')) not null,
  "payment_channel" varchar,
  "phone_number" varchar,
  "account_number" varchar,
  "bank_name" varchar,
  "cheque_number" varchar,
  "gateway_response" text,
  "failure_reason" text,
  "initiated_at" datetime,
  "completed_at" datetime,
  "failed_at" datetime,
  "notes" text,
  "initiated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("fee_payment_id") references "fee_payments"("id") on delete set null,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("invoice_id") references "fee_invoices"("id") on delete cascade,
  foreign key("gateway_id") references "payment_gateways"("id") on delete set null,
  foreign key("initiated_by") references "users"("id") on delete set null
);
CREATE INDEX "payment_transactions_transaction_id_index" on "payment_transactions"(
  "transaction_id"
);
CREATE INDEX "payment_transactions_gateway_transaction_id_index" on "payment_transactions"(
  "gateway_transaction_id"
);
CREATE INDEX "payment_transactions_student_id_status_index" on "payment_transactions"(
  "student_id",
  "status"
);
CREATE INDEX "payment_transactions_invoice_id_status_index" on "payment_transactions"(
  "invoice_id",
  "status"
);
CREATE INDEX "payment_transactions_status_index" on "payment_transactions"(
  "status"
);
CREATE INDEX "payment_transactions_created_at_index" on "payment_transactions"(
  "created_at"
);
CREATE UNIQUE INDEX "payment_transactions_transaction_id_unique" on "payment_transactions"(
  "transaction_id"
);
CREATE TABLE IF NOT EXISTS "fee_arrears"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "invoice_id" integer not null,
  "academic_year_id" integer not null,
  "term_id" integer not null,
  "arrears_amount" numeric not null,
  "due_date" date not null,
  "days_overdue" integer not null default '0',
  "aging_category" varchar check("aging_category" in('current', '1-30', '31-60', '61-90', '90+')) not null default 'current',
  "reminder_sent" tinyint(1) not null default '0',
  "last_reminder_sent_at" datetime,
  "reminder_count" integer not null default '0',
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("invoice_id") references "fee_invoices"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade
);
CREATE INDEX "fee_arrears_student_id_aging_category_index" on "fee_arrears"(
  "student_id",
  "aging_category"
);
CREATE INDEX "fee_arrears_due_date_index" on "fee_arrears"("due_date");
CREATE INDEX "fee_arrears_days_overdue_index" on "fee_arrears"("days_overdue");
CREATE INDEX "fee_arrears_aging_category_index" on "fee_arrears"(
  "aging_category"
);
CREATE TABLE IF NOT EXISTS "announcements"(
  "id" integer primary key autoincrement not null,
  "title" varchar not null,
  "content" text not null,
  "type" varchar check("type" in('general', 'academic', 'fees', 'events', 'urgent', 'maintenance')) not null default 'general',
  "priority" varchar check("priority" in('low', 'normal', 'high', 'urgent')) not null default 'normal',
  "target_audience" varchar check("target_audience" in('all', 'students', 'parents', 'teachers', 'staff', 'specific_class')) not null default 'all',
  "class_id" integer,
  "stream_id" integer,
  "publish_date" date not null default CURRENT_DATE,
  "expire_date" date,
  "is_published" tinyint(1) not null default '0',
  "send_sms" tinyint(1) not null default '0',
  "send_email" tinyint(1) not null default '0',
  "send_push" tinyint(1) not null default '0',
  "attachment_path" varchar,
  "created_by" integer not null,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("stream_id") references "streams"("id") on delete cascade,
  foreign key("created_by") references "users"("id") on delete cascade
);
CREATE INDEX "announcements_is_published_publish_date_index" on "announcements"(
  "is_published",
  "publish_date"
);
CREATE INDEX "announcements_type_index" on "announcements"("type");
CREATE INDEX "announcements_target_audience_index" on "announcements"(
  "target_audience"
);
CREATE TABLE IF NOT EXISTS "messages"(
  "id" integer primary key autoincrement not null,
  "sender_id" integer not null,
  "recipient_id" integer not null,
  "subject" varchar,
  "body" text not null,
  "type" varchar check("type" in('direct', 'teacher_to_parent', 'parent_to_teacher', 'admin_to_user')) not null default 'direct',
  "student_id" integer,
  "is_read" tinyint(1) not null default '0',
  "read_at" datetime,
  "parent_message_id" integer,
  "attachment_path" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("sender_id") references "users"("id") on delete cascade,
  foreign key("recipient_id") references "users"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("parent_message_id") references "messages"("id") on delete cascade
);
CREATE INDEX "messages_recipient_id_is_read_index" on "messages"(
  "recipient_id",
  "is_read"
);
CREATE INDEX "messages_sender_id_created_at_index" on "messages"(
  "sender_id",
  "created_at"
);
CREATE INDEX "messages_student_id_index" on "messages"("student_id");
CREATE TABLE IF NOT EXISTS "sms_logs"(
  "id" integer primary key autoincrement not null,
  "phone_number" varchar not null,
  "message" text not null,
  "type" varchar check("type" in('fee_reminder', 'payment_confirmation', 'attendance_alert', 'exam_notification', 'result_notification', 'general', 'announcement')) not null default 'general',
  "status" varchar check("status" in('pending', 'sent', 'failed', 'delivered', 'undelivered')) not null default 'pending',
  "gateway" varchar not null default 'africastalking',
  "gateway_message_id" varchar,
  "gateway_response" text,
  "failure_reason" text,
  "user_id" integer,
  "student_id" integer,
  "cost" numeric,
  "sent_at" datetime,
  "delivered_at" datetime,
  "sent_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("user_id") references "users"("id") on delete set null,
  foreign key("student_id") references "students"("id") on delete set null,
  foreign key("sent_by") references "users"("id") on delete set null
);
CREATE INDEX "sms_logs_phone_number_status_index" on "sms_logs"(
  "phone_number",
  "status"
);
CREATE INDEX "sms_logs_type_index" on "sms_logs"("type");
CREATE INDEX "sms_logs_status_index" on "sms_logs"("status");
CREATE INDEX "sms_logs_created_at_index" on "sms_logs"("created_at");
CREATE TABLE IF NOT EXISTS "email_logs"(
  "id" integer primary key autoincrement not null,
  "to_email" varchar not null,
  "cc_email" varchar,
  "subject" varchar not null,
  "body" text not null,
  "type" varchar check("type" in('fee_invoice', 'payment_receipt', 'result_notification', 'attendance_report', 'announcement', 'general')) not null default 'general',
  "status" varchar check("status" in('pending', 'sent', 'failed', 'bounced')) not null default 'pending',
  "failure_reason" text,
  "user_id" integer,
  "student_id" integer,
  "attachments" text,
  "sent_at" datetime,
  "sent_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("user_id") references "users"("id") on delete set null,
  foreign key("student_id") references "students"("id") on delete set null,
  foreign key("sent_by") references "users"("id") on delete set null
);
CREATE INDEX "email_logs_to_email_status_index" on "email_logs"(
  "to_email",
  "status"
);
CREATE INDEX "email_logs_type_index" on "email_logs"("type");
CREATE INDEX "email_logs_status_index" on "email_logs"("status");
CREATE INDEX "email_logs_created_at_index" on "email_logs"("created_at");
CREATE TABLE IF NOT EXISTS "push_subscriptions"(
  "id" integer primary key autoincrement not null,
  "user_id" integer not null,
  "endpoint" varchar not null,
  "public_key" varchar,
  "auth_token" varchar,
  "content_encoding" varchar not null default 'aesgcm',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("user_id") references "users"("id") on delete cascade
);
CREATE INDEX "push_subscriptions_user_id_index" on "push_subscriptions"(
  "user_id"
);
CREATE UNIQUE INDEX "push_subscriptions_endpoint_unique" on "push_subscriptions"(
  "endpoint"
);
CREATE TABLE IF NOT EXISTS "notifications"(
  "id" integer primary key autoincrement not null,
  "user_id" integer not null,
  "type" varchar not null,
  "title" varchar not null,
  "message" text not null,
  "data" text,
  "icon" varchar,
  "link" varchar,
  "is_read" tinyint(1) not null default '0',
  "read_at" datetime,
  "priority" varchar check("priority" in('low', 'normal', 'high', 'urgent')) not null default 'normal',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("user_id") references "users"("id") on delete cascade
);
CREATE INDEX "notifications_user_id_is_read_index" on "notifications"(
  "user_id",
  "is_read"
);
CREATE INDEX "notifications_type_index" on "notifications"("type");
CREATE INDEX "notifications_created_at_index" on "notifications"("created_at");
CREATE TABLE IF NOT EXISTS "budgets"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "category" varchar not null,
  "total_amount" numeric not null,
  "allocated_amount" numeric not null default '0',
  "spent_amount" numeric not null default '0',
  "start_date" date not null,
  "end_date" date not null,
  "status" varchar check("status" in('active', 'completed', 'overbudget', 'pending')) not null default 'active',
  "description" text,
  "academic_year_id" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null
);
CREATE INDEX "budgets_category_status_index" on "budgets"(
  "category",
  "status"
);
CREATE INDEX "budgets_start_date_end_date_index" on "budgets"(
  "start_date",
  "end_date"
);
CREATE TABLE IF NOT EXISTS "expenditures"(
  "id" integer primary key autoincrement not null,
  "reference_number" varchar not null,
  "budget_id" integer,
  "category" varchar not null,
  "description" varchar not null,
  "amount" numeric not null,
  "expenditure_date" date not null,
  "payment_method" varchar check("payment_method" in('cash', 'bank_transfer', 'cheque', 'mobile_money', 'card')) not null,
  "vendor_name" varchar,
  "vendor_contact" varchar,
  "invoice_number" varchar,
  "receipt_number" varchar,
  "status" varchar check("status" in('pending', 'approved', 'rejected', 'paid', 'cancelled')) not null default 'pending',
  "requested_by" integer not null,
  "approved_by" integer,
  "approved_at" datetime,
  "approval_notes" text,
  "rejection_reason" text,
  "transaction_reference" varchar,
  "account_id" integer,
  "attachments" text,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("budget_id") references "budgets"("id") on delete set null,
  foreign key("requested_by") references "users"("id"),
  foreign key("approved_by") references "users"("id"),
  foreign key("account_id") references "bank_accounts"("id") on delete set null
);
CREATE INDEX "expenditures_category_status_index" on "expenditures"(
  "category",
  "status"
);
CREATE INDEX "expenditures_expenditure_date_status_index" on "expenditures"(
  "expenditure_date",
  "status"
);
CREATE INDEX "expenditures_budget_id_index" on "expenditures"("budget_id");
CREATE UNIQUE INDEX "expenditures_reference_number_unique" on "expenditures"(
  "reference_number"
);
CREATE TABLE IF NOT EXISTS "bank_accounts"(
  "id" integer primary key autoincrement not null,
  "account_name" varchar not null,
  "account_number" varchar not null,
  "bank_name" varchar not null,
  "branch_name" varchar,
  "account_type" varchar not null default 'current',
  "currency" varchar not null default 'USD',
  "opening_balance" numeric not null default '0',
  "current_balance" numeric not null default '0',
  "is_active" tinyint(1) not null default '1',
  "is_default" tinyint(1) not null default '0',
  "description" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE UNIQUE INDEX "bank_accounts_account_number_unique" on "bank_accounts"(
  "account_number"
);
CREATE TABLE IF NOT EXISTS "bank_transactions"(
  "id" integer primary key autoincrement not null,
  "transaction_number" varchar not null,
  "account_id" integer not null,
  "type" varchar check("type" in('deposit', 'withdrawal', 'transfer')) not null,
  "amount" numeric not null,
  "transaction_date" date not null,
  "reference" varchar,
  "description" varchar not null,
  "category" varchar,
  "related_expenditure_id" integer,
  "related_payment_id" integer,
  "transfer_to_account_id" integer,
  "is_reconciled" tinyint(1) not null default '0',
  "reconciled_date" date,
  "reconciled_by" integer,
  "created_by" integer not null,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("account_id") references "bank_accounts"("id"),
  foreign key("related_expenditure_id") references "expenditures"("id") on delete set null,
  foreign key("related_payment_id") references "fee_payments"("id") on delete set null,
  foreign key("transfer_to_account_id") references "bank_accounts"("id") on delete set null,
  foreign key("reconciled_by") references "users"("id"),
  foreign key("created_by") references "users"("id")
);
CREATE INDEX "bank_transactions_account_id_transaction_date_index" on "bank_transactions"(
  "account_id",
  "transaction_date"
);
CREATE INDEX "bank_transactions_type_is_reconciled_index" on "bank_transactions"(
  "type",
  "is_reconciled"
);
CREATE UNIQUE INDEX "bank_transactions_transaction_number_unique" on "bank_transactions"(
  "transaction_number"
);
CREATE TABLE IF NOT EXISTS "fee_refunds"(
  "id" integer primary key autoincrement not null,
  "refund_number" varchar not null,
  "payment_id" integer not null,
  "invoice_id" integer not null,
  "student_id" integer not null,
  "refund_amount" numeric not null,
  "refund_date" date not null,
  "refund_method" varchar check("refund_method" in('cash', 'bank_transfer', 'cheque', 'mobile_money', 'reversal')) not null,
  "reason" varchar not null,
  "notes" text,
  "status" varchar check("status" in('pending', 'approved', 'rejected', 'processed')) not null default 'pending',
  "requested_by" integer not null,
  "approved_by" integer,
  "approved_at" datetime,
  "processed_by" integer,
  "processed_at" datetime,
  "rejection_reason" text,
  "transaction_reference" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("payment_id") references "fee_payments"("id"),
  foreign key("invoice_id") references "fee_invoices"("id"),
  foreign key("student_id") references "students"("id"),
  foreign key("requested_by") references "users"("id"),
  foreign key("approved_by") references "users"("id"),
  foreign key("processed_by") references "users"("id")
);
CREATE INDEX "fee_refunds_student_id_status_index" on "fee_refunds"(
  "student_id",
  "status"
);
CREATE UNIQUE INDEX "fee_refunds_refund_number_unique" on "fee_refunds"(
  "refund_number"
);
CREATE TABLE IF NOT EXISTS "fee_waivers"(
  "id" integer primary key autoincrement not null,
  "waiver_number" varchar not null,
  "student_id" integer not null,
  "invoice_id" integer,
  "fee_structure_id" integer,
  "academic_year_id" integer not null,
  "term_id" integer,
  "waiver_type" varchar check("waiver_type" in('full', 'partial', 'percentage')) not null,
  "waiver_amount" numeric,
  "waiver_percentage" numeric,
  "category" varchar not null,
  "reason" varchar not null,
  "justification" text,
  "start_date" date not null,
  "end_date" date,
  "status" varchar check("status" in('pending', 'approved', 'rejected', 'active', 'expired')) not null default 'pending',
  "requested_by" integer not null,
  "approved_by" integer,
  "approved_at" datetime,
  "rejection_reason" text,
  "attachments" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id"),
  foreign key("invoice_id") references "fee_invoices"("id") on delete set null,
  foreign key("fee_structure_id") references "fee_structures"("id") on delete set null,
  foreign key("academic_year_id") references "academic_years"("id"),
  foreign key("term_id") references "terms"("id") on delete set null,
  foreign key("requested_by") references "users"("id"),
  foreign key("approved_by") references "users"("id")
);
CREATE INDEX "fee_waivers_student_id_status_index" on "fee_waivers"(
  "student_id",
  "status"
);
CREATE INDEX "fee_waivers_academic_year_id_status_index" on "fee_waivers"(
  "academic_year_id",
  "status"
);
CREATE UNIQUE INDEX "fee_waivers_waiver_number_unique" on "fee_waivers"(
  "waiver_number"
);
CREATE TABLE IF NOT EXISTS "payment_plans"(
  "id" integer primary key autoincrement not null,
  "plan_number" varchar not null,
  "student_id" integer not null,
  "invoice_id" integer not null,
  "academic_year_id" integer not null,
  "term_id" integer,
  "total_amount" numeric not null,
  "paid_amount" numeric not null default '0',
  "number_of_instalments" integer not null,
  "start_date" date not null,
  "frequency" varchar check("frequency" in('weekly', 'biweekly', 'monthly')) not null,
  "status" varchar check("status" in('active', 'completed', 'defaulted', 'cancelled')) not null default 'active',
  "notes" text,
  "created_by" integer not null,
  "approved_by" integer,
  "approved_at" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id"),
  foreign key("invoice_id") references "fee_invoices"("id"),
  foreign key("academic_year_id") references "academic_years"("id"),
  foreign key("term_id") references "terms"("id") on delete set null,
  foreign key("created_by") references "users"("id"),
  foreign key("approved_by") references "users"("id")
);
CREATE INDEX "payment_plans_student_id_status_index" on "payment_plans"(
  "student_id",
  "status"
);
CREATE UNIQUE INDEX "payment_plans_plan_number_unique" on "payment_plans"(
  "plan_number"
);
CREATE TABLE IF NOT EXISTS "payment_plan_instalments"(
  "id" integer primary key autoincrement not null,
  "payment_plan_id" integer not null,
  "instalment_number" integer not null,
  "amount" numeric not null,
  "due_date" date not null,
  "paid_amount" numeric not null default '0',
  "status" varchar check("status" in('pending', 'paid', 'partial', 'overdue')) not null default 'pending',
  "payment_id" integer,
  "paid_date" date,
  "reminder_sent" tinyint(1) not null default '0',
  "reminder_sent_at" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("payment_plan_id") references "payment_plans"("id") on delete cascade,
  foreign key("payment_id") references "fee_payments"("id") on delete set null
);
CREATE INDEX "payment_plan_instalments_payment_plan_id_status_index" on "payment_plan_instalments"(
  "payment_plan_id",
  "status"
);
CREATE INDEX "payment_plan_instalments_due_date_index" on "payment_plan_instalments"(
  "due_date"
);
CREATE TABLE IF NOT EXISTS "approval_workflows"(
  "id" integer primary key autoincrement not null,
  "workflow_name" varchar not null,
  "module" varchar not null,
  "threshold_amount" numeric,
  "required_approvals" integer not null default '1',
  "approver_roles" text not null,
  "is_active" tinyint(1) not null default '1',
  "order" integer not null default '0',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "approval_requests"(
  "id" integer primary key autoincrement not null,
  "request_number" varchar not null,
  "workflow_id" integer not null,
  "approvable_type" varchar not null,
  "approvable_id" integer not null,
  "requested_by" integer not null,
  "status" varchar check("status" in('pending', 'approved', 'rejected', 'cancelled')) not null default 'pending',
  "approvals_received" integer not null default '0',
  "approvals_required" integer not null,
  "request_notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("workflow_id") references "approval_workflows"("id"),
  foreign key("requested_by") references "users"("id")
);
CREATE INDEX "approval_requests_approvable_type_approvable_id_index" on "approval_requests"(
  "approvable_type",
  "approvable_id"
);
CREATE INDEX "approval_requests_status_requested_by_index" on "approval_requests"(
  "status",
  "requested_by"
);
CREATE UNIQUE INDEX "approval_requests_request_number_unique" on "approval_requests"(
  "request_number"
);
CREATE TABLE IF NOT EXISTS "approval_actions"(
  "id" integer primary key autoincrement not null,
  "approval_request_id" integer not null,
  "approver_id" integer not null,
  "action" varchar check("action" in('approved', 'rejected')) not null,
  "comments" text,
  "actioned_at" datetime not null,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("approval_request_id") references "approval_requests"("id") on delete cascade,
  foreign key("approver_id") references "users"("id")
);
CREATE INDEX "approval_actions_approval_request_id_index" on "approval_actions"(
  "approval_request_id"
);
CREATE TABLE IF NOT EXISTS "dormitories"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "gender" varchar check("gender" in('male', 'female', 'mixed')) not null default 'mixed',
  "capacity" integer not null,
  "current_occupancy" integer not null default '0',
  "building" varchar,
  "floor" varchar,
  "matron_id" integer,
  "patron_id" integer,
  "description" text,
  "status" varchar check("status" in('active', 'inactive', 'maintenance')) not null default 'active',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("matron_id") references "users"("id") on delete set null,
  foreign key("patron_id") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "dormitories_code_unique" on "dormitories"("code");
CREATE TABLE IF NOT EXISTS "beds"(
  "id" integer primary key autoincrement not null,
  "dormitory_id" integer not null,
  "bed_number" varchar not null,
  "room_number" varchar,
  "bed_type" varchar check("bed_type" in('single', 'double', 'bunk')) not null default 'single',
  "status" varchar check("status" in('available', 'occupied', 'maintenance', 'reserved')) not null default 'available',
  "current_student_id" integer,
  "occupied_from" date,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("dormitory_id") references "dormitories"("id") on delete cascade,
  foreign key("current_student_id") references "students"("id") on delete set null
);
CREATE UNIQUE INDEX "beds_dormitory_id_bed_number_unique" on "beds"(
  "dormitory_id",
  "bed_number"
);
CREATE TABLE IF NOT EXISTS "boarding_students"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "academic_year_id" integer not null,
  "dormitory_id" integer not null,
  "bed_id" integer,
  "start_date" date not null,
  "end_date" date,
  "status" varchar check("status" in('active', 'inactive', 'transferred', 'completed')) not null default 'active',
  "meals_included" tinyint(1) not null default '1',
  "laundry_included" tinyint(1) not null default '1',
  "special_requirements" text,
  "medical_notes" text,
  "emergency_contacts" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("dormitory_id") references "dormitories"("id") on delete cascade,
  foreign key("bed_id") references "beds"("id") on delete set null
);
CREATE UNIQUE INDEX "boarding_students_student_id_academic_year_id_unique" on "boarding_students"(
  "student_id",
  "academic_year_id"
);
CREATE TABLE IF NOT EXISTS "boarding_leave_requests"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "boarding_student_id" integer not null,
  "leave_type" varchar check("leave_type" in('weekend', 'midterm', 'emergency', 'medical', 'other')) not null default 'weekend',
  "leave_start" datetime not null,
  "leave_end" datetime not null,
  "expected_return" datetime not null,
  "actual_return" datetime,
  "reason" text not null,
  "destination" varchar,
  "contact_person" varchar not null,
  "contact_phone" varchar not null,
  "status" varchar check("status" in('pending', 'approved', 'rejected', 'cancelled')) not null default 'pending',
  "requested_by" integer not null,
  "approved_by" integer,
  "approved_at" datetime,
  "approval_notes" text,
  "rejection_reason" text,
  "parent_notified" tinyint(1) not null default '0',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("boarding_student_id") references "boarding_students"("id") on delete cascade,
  foreign key("requested_by") references "users"("id"),
  foreign key("approved_by") references "users"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "boarding_health_records"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "boarding_student_id" integer not null,
  "visit_date" datetime not null,
  "visit_type" varchar check("visit_type" in('sick_bay', 'medication', 'injury', 'checkup', 'hospital_referral')) not null default 'sick_bay',
  "symptoms" text,
  "diagnosis" text,
  "treatment" text not null,
  "medication_given" text,
  "temperature" varchar,
  "blood_pressure" varchar,
  "treated_by" integer not null,
  "hospital_referral" tinyint(1) not null default '0',
  "hospital_name" varchar,
  "referral_reason" text,
  "cost" numeric not null default '0',
  "parent_notified" tinyint(1) not null default '0',
  "follow_up_date" datetime,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("boarding_student_id") references "boarding_students"("id") on delete cascade,
  foreign key("treated_by") references "users"("id")
);
CREATE TABLE IF NOT EXISTS "night_attendance"(
  "id" integer primary key autoincrement not null,
  "dormitory_id" integer not null,
  "attendance_date" date not null,
  "check_time" time not null,
  "student_id" integer not null,
  "boarding_student_id" integer not null,
  "status" varchar check("status" in('present', 'absent', 'on_leave', 'late')) not null default 'present',
  "arrival_time" time,
  "remarks" text,
  "marked_by" integer not null,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("dormitory_id") references "dormitories"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("boarding_student_id") references "boarding_students"("id") on delete cascade,
  foreign key("marked_by") references "users"("id")
);
CREATE UNIQUE INDEX "night_attendance_student_id_attendance_date_check_time_unique" on "night_attendance"(
  "student_id",
  "attendance_date",
  "check_time"
);
CREATE TABLE IF NOT EXISTS "discipline_incidents"(
  "id" integer primary key autoincrement not null,
  "incident_number" varchar not null,
  "student_id" integer not null,
  "offense_category_id" integer not null,
  "incident_date" datetime not null,
  "location" varchar,
  "description" text not null,
  "students_involved" text,
  "witnesses" text,
  "reported_by" integer not null,
  "class_id" integer,
  "status" varchar check("status" in('reported', 'investigating', 'action_taken', 'resolved', 'appealed')) not null default 'reported',
  "investigation_notes" text,
  "evidence_attachments" text,
  "parent_notified" tinyint(1) not null default '0',
  "parent_notified_at" datetime,
  "requires_followup" tinyint(1) not null default '0',
  "followup_date" date,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("offense_category_id") references "discipline_offense_categories"("id"),
  foreign key("reported_by") references "users"("id"),
  foreign key("class_id") references "classes"("id") on delete set null
);
CREATE UNIQUE INDEX "discipline_incidents_incident_number_unique" on "discipline_incidents"(
  "incident_number"
);
CREATE TABLE IF NOT EXISTS "discipline_offense_categories"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "description" text,
  "severity" varchar check("severity" in('minor', 'major', 'serious', 'critical')) not null default 'minor',
  "severity_points" integer not null default '1',
  "recommended_actions" text,
  "requires_parent_notification" tinyint(1) not null default '0',
  "requires_admin_approval" tinyint(1) not null default '0',
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE UNIQUE INDEX "discipline_offense_categories_code_unique" on "discipline_offense_categories"(
  "code"
);
CREATE TABLE IF NOT EXISTS "library_books"(
  "id" integer primary key autoincrement not null,
  "isbn" varchar,
  "accession_number" varchar not null,
  "barcode" varchar,
  "title" varchar not null,
  "author" varchar not null,
  "publisher" varchar,
  "publication_year" integer,
  "edition" varchar,
  "pages" integer,
  "language" varchar not null default 'English',
  "category" varchar check("category" in('fiction', 'non-fiction', 'reference', 'textbook', 'periodical', 'other')) not null default 'non-fiction',
  "subject" varchar,
  "genre" varchar,
  "level" varchar check("level" in('primary', 'secondary', 'general')) not null default 'general',
  "total_copies" integer not null default '1',
  "available_copies" integer not null default '1',
  "shelf_location" varchar,
  "section" varchar,
  "price" numeric,
  "date_acquired" date not null,
  "source" varchar,
  "condition" varchar check("condition" in('new', 'good', 'fair', 'poor', 'damaged')) not null default 'good',
  "status" varchar check("status" in('available', 'issued', 'reserved', 'lost', 'damaged', 'retired')) not null default 'available',
  "description" text,
  "cover_image" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE UNIQUE INDEX "library_books_accession_number_unique" on "library_books"(
  "accession_number"
);
CREATE UNIQUE INDEX "library_books_barcode_unique" on "library_books"(
  "barcode"
);
CREATE TABLE IF NOT EXISTS "library_members"(
  "id" integer primary key autoincrement not null,
  "member_number" varchar not null,
  "barcode" varchar,
  "member_type" varchar check("member_type" in('student', 'staff', 'external')) not null default 'student',
  "student_id" integer,
  "staff_id" integer,
  "external_name" varchar,
  "external_phone" varchar,
  "external_email" varchar,
  "membership_start" date not null,
  "membership_expiry" date,
  "borrowing_limit" integer not null default '3',
  "current_borrowed" integer not null default '0',
  "borrowing_days" integer not null default '14',
  "can_renew" tinyint(1) not null default '1',
  "max_renewals" integer not null default '2',
  "outstanding_fines" numeric not null default '0',
  "status" varchar check("status" in('active', 'suspended', 'blocked', 'expired')) not null default 'active',
  "suspension_reason" text,
  "suspension_end" date,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id") on delete set null,
  foreign key("staff_id") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "library_members_member_number_unique" on "library_members"(
  "member_number"
);
CREATE UNIQUE INDEX "library_members_barcode_unique" on "library_members"(
  "barcode"
);
CREATE TABLE IF NOT EXISTS "library_transactions"(
  "id" integer primary key autoincrement not null,
  "transaction_number" varchar not null,
  "member_id" integer not null,
  "book_id" integer not null,
  "transaction_type" varchar check("transaction_type" in('issue', 'return', 'renewal', 'lost', 'damaged')) not null default 'issue',
  "issue_date" date not null,
  "due_date" date not null,
  "return_date" date,
  "renewal_count" integer not null default '0',
  "days_overdue" integer not null default '0',
  "fine_amount" numeric not null default '0',
  "fine_paid" tinyint(1) not null default '0',
  "book_condition_on_issue" varchar check("book_condition_on_issue" in('new', 'good', 'fair', 'poor')) not null default 'good',
  "book_condition_on_return" varchar check("book_condition_on_return" in('new', 'good', 'fair', 'poor', 'damaged')),
  "status" varchar check("status" in('issued', 'returned', 'overdue', 'lost', 'renewed')) not null default 'issued',
  "issued_by" integer not null,
  "returned_to" integer,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("member_id") references "library_members"("id") on delete cascade,
  foreign key("book_id") references "library_books"("id") on delete cascade,
  foreign key("issued_by") references "users"("id"),
  foreign key("returned_to") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "library_transactions_transaction_number_unique" on "library_transactions"(
  "transaction_number"
);
CREATE TABLE IF NOT EXISTS "library_fines"(
  "id" integer primary key autoincrement not null,
  "member_id" integer not null,
  "transaction_id" integer,
  "fine_type" varchar check("fine_type" in('overdue', 'lost_book', 'damaged_book', 'late_return', 'other')) not null default 'overdue',
  "amount" numeric not null,
  "amount_paid" numeric not null default '0',
  "balance" numeric not null,
  "reason" text not null,
  "fine_date" date not null,
  "payment_date" date,
  "payment_method" varchar,
  "receipt_number" varchar,
  "status" varchar check("status" in('pending', 'partial', 'paid', 'waived')) not null default 'pending',
  "waived_by" integer,
  "waiver_reason" text,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("member_id") references "library_members"("id") on delete cascade,
  foreign key("transaction_id") references "library_transactions"("id") on delete set null,
  foreign key("waived_by") references "users"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "library_reservations"(
  "id" integer primary key autoincrement not null,
  "member_id" integer not null,
  "book_id" integer not null,
  "reservation_date" date not null,
  "expiry_date" date not null,
  "fulfilled_date" date,
  "status" varchar check("status" in('pending', 'fulfilled', 'cancelled', 'expired')) not null default 'pending',
  "queue_position" integer,
  "notified" tinyint(1) not null default '0',
  "notified_at" datetime,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("member_id") references "library_members"("id") on delete cascade,
  foreign key("book_id") references "library_books"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "assets"(
  "id" integer primary key autoincrement not null,
  "asset_number" varchar not null,
  "barcode" varchar,
  "name" varchar not null,
  "category_id" integer not null,
  "description" text,
  "brand" varchar,
  "model" varchar,
  "serial_number" varchar,
  "purchase_date" date not null,
  "purchase_cost" numeric not null,
  "supplier" varchar,
  "supplier_contact" varchar,
  "invoice_number" varchar,
  "warranty_period" varchar,
  "warranty_expiry" date,
  "insurance_policy" varchar,
  "insurance_expiry" date,
  "location" varchar not null,
  "department" varchar,
  "custodian_id" integer,
  "condition" varchar check("condition" in('excellent', 'good', 'fair', 'poor', 'damaged')) not null default 'good',
  "status" varchar check("status" in('active', 'in_use', 'maintenance', 'repair', 'retired', 'disposed')) not null default 'active',
  "current_value" numeric not null,
  "depreciation_amount" numeric not null default '0',
  "last_depreciation_date" date,
  "specifications" text,
  "photo" varchar,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("category_id") references "asset_categories"("id"),
  foreign key("custodian_id") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "assets_asset_number_unique" on "assets"("asset_number");
CREATE UNIQUE INDEX "assets_barcode_unique" on "assets"("barcode");
CREATE TABLE IF NOT EXISTS "asset_categories"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "description" text,
  "depreciation_rate" integer not null default '0',
  "useful_life_years" integer not null default '5',
  "requires_maintenance" tinyint(1) not null default '0',
  "maintenance_interval_months" integer,
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE UNIQUE INDEX "asset_categories_code_unique" on "asset_categories"(
  "code"
);
CREATE TABLE IF NOT EXISTS "asset_maintenance"(
  "id" integer primary key autoincrement not null,
  "asset_id" integer not null,
  "maintenance_type" varchar check("maintenance_type" in('routine', 'preventive', 'corrective', 'emergency')) not null default 'routine',
  "scheduled_date" date not null,
  "completed_date" date,
  "description" text not null,
  "work_done" text,
  "service_provider" varchar,
  "technician_name" varchar,
  "technician_contact" varchar,
  "cost" numeric not null default '0',
  "invoice_number" varchar,
  "parts_replaced" text,
  "priority" varchar check("priority" in('low', 'medium', 'high', 'urgent')) not null default 'medium',
  "status" varchar check("status" in('scheduled', 'in_progress', 'completed', 'cancelled')) not null default 'scheduled',
  "next_maintenance_date" date,
  "requested_by" integer not null,
  "completed_by" integer,
  "notes" text,
  "attachments" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("asset_id") references "assets"("id") on delete cascade,
  foreign key("requested_by") references "users"("id"),
  foreign key("completed_by") references "users"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "consumables"(
  "id" integer primary key autoincrement not null,
  "item_code" varchar not null,
  "name" varchar not null,
  "description" text,
  "category" varchar check("category" in('stationery', 'cleaning', 'medical', 'kitchen', 'maintenance', 'other')) not null default 'stationery',
  "unit_of_measure" varchar not null,
  "quantity_in_stock" integer not null default '0',
  "reorder_level" integer not null,
  "minimum_stock_level" integer not null,
  "maximum_stock_level" integer,
  "unit_cost" numeric not null,
  "total_value" numeric not null,
  "storage_location" varchar,
  "supplier" varchar,
  "supplier_contact" varchar,
  "last_purchase_date" date,
  "is_active" tinyint(1) not null default '1',
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE UNIQUE INDEX "consumables_item_code_unique" on "consumables"(
  "item_code"
);
CREATE TABLE IF NOT EXISTS "vehicles"(
  "id" integer primary key autoincrement not null,
  "vehicle_number" varchar not null,
  "registration_number" varchar not null,
  "vehicle_type" varchar check("vehicle_type" in('bus', 'van', 'minibus', 'car', 'other')) not null default 'bus',
  "make" varchar not null,
  "model" varchar not null,
  "year_of_manufacture" integer not null,
  "color" varchar,
  "seating_capacity" integer not null,
  "engine_number" varchar,
  "chassis_number" varchar,
  "purchase_date" date not null,
  "purchase_cost" numeric,
  "current_value" numeric,
  "insurance_company" varchar,
  "insurance_policy_number" varchar,
  "insurance_expiry" date,
  "road_license_number" varchar,
  "road_license_expiry" date,
  "current_mileage" integer not null default '0',
  "last_service_date" date,
  "last_service_mileage" integer,
  "next_service_date" date,
  "service_interval_km" integer not null default '5000',
  "condition" varchar check("condition" in('excellent', 'good', 'fair', 'poor')) not null default 'good',
  "status" varchar check("status" in('active', 'maintenance', 'repair', 'off_road', 'retired')) not null default 'active',
  "notes" text,
  "photo" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE UNIQUE INDEX "vehicles_vehicle_number_unique" on "vehicles"(
  "vehicle_number"
);
CREATE UNIQUE INDEX "vehicles_registration_number_unique" on "vehicles"(
  "registration_number"
);
CREATE TABLE IF NOT EXISTS "student_transport"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "route_id" integer not null,
  "academic_year_id" integer not null,
  "term_id" integer,
  "pickup_point" varchar not null,
  "dropoff_point" varchar not null,
  "pickup_time" time,
  "dropoff_time" time,
  "monthly_fee" numeric not null,
  "term_fee" numeric not null,
  "amount_paid" numeric not null default '0',
  "balance" numeric not null,
  "start_date" date not null,
  "end_date" date,
  "status" varchar check("status" in('active', 'inactive', 'suspended', 'completed')) not null default 'active',
  "morning_transport" tinyint(1) not null default '1',
  "evening_transport" tinyint(1) not null default '1',
  "emergency_contact_name" varchar,
  "emergency_contact_phone" varchar,
  "special_requirements" text,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("route_id") references "transport_routes"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete set null
);
CREATE UNIQUE INDEX "student_transport_student_id_route_id_academic_year_id_unique" on "student_transport"(
  "student_id",
  "route_id",
  "academic_year_id"
);
CREATE TABLE IF NOT EXISTS "vehicle_maintenance"(
  "id" integer primary key autoincrement not null,
  "vehicle_id" integer not null,
  "maintenance_type" varchar check("maintenance_type" in('service', 'repair', 'inspection', 'tire_change', 'oil_change', 'other')) not null default 'service',
  "scheduled_date" date not null,
  "completed_date" date,
  "mileage_at_maintenance" integer not null,
  "description" text not null,
  "work_done" text,
  "service_provider" varchar not null,
  "mechanic_name" varchar,
  "mechanic_contact" varchar,
  "parts_replaced" text,
  "fluids_changed" text,
  "parts_cost" numeric not null default '0',
  "labor_cost" numeric not null default '0',
  "total_cost" numeric not null,
  "invoice_number" varchar,
  "next_service_mileage" integer,
  "next_service_date" date,
  "priority" varchar check("priority" in('low', 'medium', 'high', 'urgent')) not null default 'medium',
  "status" varchar check("status" in('scheduled', 'in_progress', 'completed', 'cancelled')) not null default 'scheduled',
  "requested_by" integer not null,
  "approved_by" integer,
  "notes" text,
  "attachments" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("vehicle_id") references "vehicles"("id") on delete cascade,
  foreign key("requested_by") references "users"("id"),
  foreign key("approved_by") references "users"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "transport_trips"(
  "id" integer primary key autoincrement not null,
  "vehicle_id" integer not null,
  "route_id" integer not null,
  "driver_id" integer not null,
  "trip_date" date not null,
  "trip_type" varchar check("trip_type" in('morning', 'evening', 'field_trip', 'special')) not null default 'morning',
  "departure_time" time not null,
  "arrival_time" time,
  "mileage_start" integer not null,
  "mileage_end" integer,
  "distance_covered" integer,
  "fuel_consumed" numeric,
  "fuel_cost" numeric,
  "students_count" integer not null,
  "student_attendance" text,
  "status" varchar check("status" in('scheduled', 'in_progress', 'completed', 'cancelled')) not null default 'scheduled',
  "incidents" text,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("vehicle_id") references "vehicles"("id") on delete cascade,
  foreign key("route_id") references "transport_routes"("id") on delete cascade,
  foreign key("driver_id") references "users"("id")
);
CREATE TABLE IF NOT EXISTS "discipline_actions"(
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  incident_id INTEGER NOT NULL,
  action_type TEXT CHECK(action_type IN('warning', 'detention', 'suspension', 'expulsion', 'community_service', 'counseling', 'parent_meeting', 'other')) NOT NULL DEFAULT 'warning',
  action_details TEXT NOT NULL,
  action_date DATE NOT NULL,
  start_date DATE,
  end_date DATE,
  duration_days INTEGER,
  conditions TEXT,
  assigned_by INTEGER NOT NULL,
  approved_by INTEGER,
  approved_at DATETIME,
  status TEXT CHECK(status IN('pending', 'approved', 'rejected', 'completed', 'cancelled')) NOT NULL DEFAULT 'pending',
  completion_notes TEXT,
  completed_at DATETIME,
  parent_informed INTEGER NOT NULL DEFAULT 0,
  parent_response TEXT,
  appeal_allowed INTEGER NOT NULL DEFAULT 1,
  review_date DATE,
  created_at DATETIME,
  updated_at DATETIME,
  deleted_at DATETIME,
  FOREIGN KEY(incident_id) REFERENCES discipline_incidents(id) ON DELETE CASCADE,
  FOREIGN KEY(assigned_by) REFERENCES users(id),
  FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS "asset_transfers"(
  "id" integer primary key autoincrement not null,
  "asset_id" integer not null,
  "from_location" varchar,
  "to_location" varchar,
  "from_department" varchar,
  "to_department" varchar,
  "from_custodian_id" integer,
  "to_custodian_id" integer,
  "transfer_date" date not null,
  "reason" text not null,
  "notes" text,
  "transferred_by" integer not null,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("asset_id") references "assets"("id") on delete cascade,
  foreign key("from_custodian_id") references "users"("id") on delete set null,
  foreign key("to_custodian_id") references "users"("id") on delete set null,
  foreign key("transferred_by") references "users"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "asset_retirements"(
  "id" integer primary key autoincrement not null,
  "asset_id" integer not null,
  "retirement_date" date not null,
  "reason" text not null,
  "disposal_method" varchar check("disposal_method" in('sold', 'donated', 'scrapped', 'recycled')),
  "disposal_value" numeric,
  "disposal_to" varchar,
  "notes" text,
  "retired_by" integer not null,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("asset_id") references "assets"("id") on delete cascade,
  foreign key("retired_by") references "users"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "consumable_transactions"(
  "id" integer primary key autoincrement not null,
  "transaction_number" varchar not null,
  "consumable_id" integer not null,
  "transaction_type" varchar not null default('issue'),
  "quantity" integer not null,
  "unit_cost" numeric not null,
  "total_cost" numeric not null,
  "transaction_date" date not null,
  "department" varchar,
  "issued_to" varchar,
  "issued_by" integer not null,
  "reference_number" varchar,
  "supplier" varchar,
  "invoice_number" varchar,
  "purpose" text,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("issued_by") references users("id") on delete no action on update no action,
  foreign key("consumable_id") references consumables("id") on delete cascade on update no action
);
CREATE UNIQUE INDEX "consumable_transactions_transaction_number_unique" on "consumable_transactions"(
  "transaction_number"
);
CREATE TABLE IF NOT EXISTS "transport_routes"(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  route_number VARCHAR(255) NOT NULL UNIQUE,
  route_name VARCHAR(255) NOT NULL,
  description TEXT,
  pickup_points TEXT,
  dropoff_points TEXT,
  distance_km DECIMAL(8, 2),
  estimated_time_minutes INTEGER,
  morning_departure_time TIME,
  morning_arrival_time TIME,
  evening_departure_time TIME,
  evening_arrival_time TIME,
  vehicle_id INTEGER,
  driver_id INTEGER,
  capacity INTEGER NOT NULL,
  current_students INTEGER DEFAULT 0,
  monthly_fee DECIMAL(10, 2) NOT NULL,
  term_fee DECIMAL(10, 2) NOT NULL,
  status VARCHAR(255) DEFAULT "active" CHECK(status IN('active', 'inactive', 'suspended')),
  notes TEXT,
  created_at DATETIME,
  updated_at DATETIME,
  deleted_at DATETIME,
  FOREIGN KEY(vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
  FOREIGN KEY(driver_id) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS "scheme_of_works"(
  "id" integer primary key autoincrement not null,
  "subject_id" integer not null,
  "class_id" integer not null,
  "term_id" integer not null,
  "academic_year_id" integer not null,
  "week_number" integer not null,
  "topic" varchar not null,
  "subtopic" varchar,
  "objectives" text,
  "teaching_methods" text,
  "learning_activities" text,
  "resources" text,
  "assessment_methods" text,
  "remarks" text,
  "status" varchar not null default 'pending',
  "created_by" integer not null,
  "updated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("term_id") references "terms"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("created_by") references "users"("id"),
  foreign key("updated_by") references "users"("id")
);
CREATE TABLE IF NOT EXISTS "parent_teacher_meetings"(
  "id" integer primary key autoincrement not null,
  "parent_id" integer not null,
  "teacher_id" integer not null,
  "student_id" integer,
  "requested_by" integer not null,
  "title" varchar not null,
  "purpose" text,
  "scheduled_date" datetime not null,
  "duration_minutes" integer not null default '30',
  "location" varchar,
  "meeting_type" varchar not null default 'in-person',
  "virtual_link" varchar,
  "status" varchar check("status" in('pending', 'confirmed', 'completed', 'cancelled', 'rescheduled')) not null default 'pending',
  "agenda" text,
  "notes" text,
  "action_items" text,
  "confirmed_at" datetime,
  "confirmed_by" integer,
  "completed_at" datetime,
  "cancellation_reason" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("parent_id") references "parents"("id") on delete cascade,
  foreign key("teacher_id") references "users"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("requested_by") references "users"("id") on delete cascade,
  foreign key("confirmed_by") references "users"("id") on delete set null
);
CREATE INDEX "parent_teacher_meetings_parent_id_scheduled_date_index" on "parent_teacher_meetings"(
  "parent_id",
  "scheduled_date"
);
CREATE INDEX "parent_teacher_meetings_teacher_id_scheduled_date_index" on "parent_teacher_meetings"(
  "teacher_id",
  "scheduled_date"
);
CREATE INDEX "parent_teacher_meetings_status_index" on "parent_teacher_meetings"(
  "status"
);
CREATE TABLE IF NOT EXISTS "student_health_records"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "blood_type" varchar,
  "height" numeric,
  "weight" numeric,
  "last_physical_exam" date,
  "next_physical_exam_due" date,
  "chronic_conditions" text,
  "allergies" text,
  "dietary_restrictions" text,
  "current_medications" text,
  "vision_status" varchar check("vision_status" in('normal', 'corrected', 'impaired')) not null default 'normal',
  "hearing_status" varchar check("hearing_status" in('normal', 'corrected', 'impaired')) not null default 'normal',
  "physical_disabilities" text,
  "mental_health_notes" text,
  "primary_physician" varchar,
  "physician_phone" varchar,
  "preferred_hospital" varchar,
  "special_instructions" text,
  "created_by" integer,
  "updated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("created_by") references "users"("id") on delete set null,
  foreign key("updated_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "student_health_records_student_id_unique" on "student_health_records"(
  "student_id"
);
CREATE TABLE IF NOT EXISTS "vaccination_records"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "vaccine_name" varchar not null,
  "vaccine_type" varchar,
  "manufacturer" varchar,
  "batch_number" varchar,
  "date_administered" date not null,
  "next_dose_due" date,
  "dose_number" integer not null default '1',
  "total_doses" integer not null default '1',
  "administered_by" varchar,
  "administration_site" varchar,
  "certificate_number" varchar,
  "facility_name" varchar,
  "facility_location" varchar,
  "verified" tinyint(1) not null default '0',
  "verified_date" date,
  "verified_by" integer,
  "adverse_reactions" text,
  "notes" text,
  "certificate_file" varchar,
  "created_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("verified_by") references "users"("id") on delete set null,
  foreign key("created_by") references "users"("id") on delete set null
);
CREATE INDEX "vaccination_records_student_id_vaccine_name_index" on "vaccination_records"(
  "student_id",
  "vaccine_name"
);
CREATE INDEX "vaccination_records_next_dose_due_index" on "vaccination_records"(
  "next_dose_due"
);
CREATE TABLE IF NOT EXISTS "sick_bay_visits"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "visit_number" varchar not null,
  "check_in_time" datetime not null,
  "check_out_time" datetime,
  "visit_type" varchar check("visit_type" in('walk_in', 'emergency', 'scheduled', 'referred')) not null default 'walk_in',
  "presenting_symptoms" text not null,
  "temperature" numeric,
  "blood_pressure_systolic" integer,
  "blood_pressure_diastolic" integer,
  "pulse_rate" integer,
  "initial_assessment" text,
  "diagnosis" text,
  "treatment_given" text,
  "medications_administered" text,
  "first_aid_provided" text,
  "outcome" varchar check("outcome" in('returned_to_class', 'sent_home', 'referred_to_hospital', 'admitted_to_sick_bay', 'ambulance_called', 'parent_collected')),
  "follow_up_required" text,
  "follow_up_date" date,
  "parent_notified" tinyint(1) not null default '0',
  "parent_notified_at" datetime,
  "parent_notified_by" varchar,
  "parent_response" text,
  "attending_staff_id" integer,
  "attending_staff_name" varchar,
  "notes" text,
  "attachment" varchar,
  "created_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("attending_staff_id") references "users"("id") on delete set null,
  foreign key("created_by") references "users"("id") on delete set null
);
CREATE INDEX "sick_bay_visits_student_id_check_in_time_index" on "sick_bay_visits"(
  "student_id",
  "check_in_time"
);
CREATE INDEX "sick_bay_visits_visit_type_index" on "sick_bay_visits"(
  "visit_type"
);
CREATE INDEX "sick_bay_visits_outcome_index" on "sick_bay_visits"("outcome");
CREATE UNIQUE INDEX "sick_bay_visits_visit_number_unique" on "sick_bay_visits"(
  "visit_number"
);
CREATE TABLE IF NOT EXISTS "medical_insurance_records"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "provider_name" varchar not null,
  "provider_phone" varchar,
  "provider_email" varchar,
  "provider_address" text,
  "policy_number" varchar not null,
  "member_id" varchar,
  "group_number" varchar,
  "plan_type" varchar check("plan_type" in('individual', 'family', 'school', 'government', 'other')) not null default 'individual',
  "coverage_start_date" date not null,
  "coverage_end_date" date,
  "is_active" tinyint(1) not null default '1',
  "coverage_amount" numeric,
  "coverage_details" text,
  "exclusions" text,
  "deductible" numeric,
  "copay_percentage" numeric,
  "policyholder_name" varchar,
  "policyholder_relationship" varchar,
  "policyholder_id_number" varchar,
  "policyholder_phone" varchar,
  "insurance_card_front" varchar,
  "insurance_card_back" varchar,
  "policy_document" varchar,
  "verified" tinyint(1) not null default '0',
  "verified_date" date,
  "verified_by" integer,
  "notes" text,
  "created_by" integer,
  "updated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("verified_by") references "users"("id") on delete set null,
  foreign key("created_by") references "users"("id") on delete set null,
  foreign key("updated_by") references "users"("id") on delete set null
);
CREATE INDEX "medical_insurance_records_student_id_is_active_index" on "medical_insurance_records"(
  "student_id",
  "is_active"
);
CREATE INDEX "medical_insurance_records_policy_number_index" on "medical_insurance_records"(
  "policy_number"
);
CREATE INDEX "medical_insurance_records_coverage_end_date_index" on "medical_insurance_records"(
  "coverage_end_date"
);
CREATE TABLE IF NOT EXISTS "emergency_medical_procedures"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "emergency_contact_name" varchar not null,
  "emergency_contact_relationship" varchar not null,
  "emergency_contact_phone" varchar not null,
  "emergency_contact_alt_phone" varchar,
  "emergency_contact_email" varchar,
  "secondary_contact_name" varchar,
  "secondary_contact_relationship" varchar,
  "secondary_contact_phone" varchar,
  "consent_emergency_treatment" tinyint(1) not null default '1',
  "consent_hospitalization" tinyint(1) not null default '1',
  "consent_surgery" tinyint(1) not null default '0',
  "consent_blood_transfusion" tinyint(1) not null default '0',
  "consent_anesthesia" tinyint(1) not null default '0',
  "consent_limitations" text,
  "emergency_procedures" text,
  "allergic_reaction_protocol" text,
  "seizure_protocol" text,
  "asthma_protocol" text,
  "diabetic_protocol" text,
  "other_protocols" text,
  "preferred_hospital" varchar,
  "hospital_address" varchar,
  "hospital_phone" varchar,
  "family_doctor_name" varchar,
  "family_doctor_phone" varchar,
  "carries_epipen" tinyint(1) not null default '0',
  "carries_inhaler" tinyint(1) not null default '0',
  "carries_insulin" tinyint(1) not null default '0',
  "wears_medical_alert" tinyint(1) not null default '0',
  "other_medical_equipment" text,
  "religious_considerations" text,
  "cultural_considerations" text,
  "special_needs_notes" text,
  "parent_signature" varchar,
  "consent_date" date,
  "consent_expiry" date,
  "consent_form_file" varchar,
  "medical_authorization_file" varchar,
  "created_by" integer,
  "updated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("created_by") references "users"("id") on delete set null,
  foreign key("updated_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "emergency_medical_procedures_student_id_unique" on "emergency_medical_procedures"(
  "student_id"
);
CREATE TABLE IF NOT EXISTS "event_categories"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "slug" varchar not null,
  "description" text,
  "color" varchar not null default '#3B82F6',
  "icon" varchar,
  "is_active" tinyint(1) not null default '1',
  "sort_order" integer not null default '0',
  "created_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("created_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "event_categories_slug_unique" on "event_categories"(
  "slug"
);
CREATE TABLE IF NOT EXISTS "events"(
  "id" integer primary key autoincrement not null,
  "title" varchar not null,
  "slug" varchar not null,
  "category_id" integer not null,
  "academic_year_id" integer,
  "term_id" integer,
  "description" text,
  "start_date" date not null,
  "start_time" time,
  "end_date" date not null,
  "end_time" time,
  "is_all_day" tinyint(1) not null default '0',
  "venue" varchar,
  "venue_address" varchar,
  "venue_latitude" numeric,
  "venue_longitude" numeric,
  "target_audience" varchar check("target_audience" in('all', 'students', 'staff', 'parents', 'specific_classes', 'specific_grades')) not null default 'all',
  "target_classes" text,
  "target_grades" text,
  "is_mandatory" tinyint(1) not null default '0',
  "requires_registration" tinyint(1) not null default '0',
  "max_participants" integer,
  "registration_deadline" date,
  "registration_fee" numeric,
  "status" varchar check("status" in('draft', 'published', 'cancelled', 'completed')) not null default 'draft',
  "banner_image" varchar,
  "attachments" text,
  "organizer_name" varchar,
  "organizer_contact" varchar,
  "organizer_email" varchar,
  "special_instructions" text,
  "cancellation_reason" text,
  "cancelled_at" datetime,
  "published_at" datetime,
  "completed_at" datetime,
  "completion_notes" text,
  "created_by" integer,
  "updated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("category_id") references "event_categories"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("term_id") references "terms"("id") on delete set null,
  foreign key("created_by") references "users"("id") on delete set null,
  foreign key("updated_by") references "users"("id") on delete set null
);
CREATE INDEX "events_start_date_end_date_index" on "events"(
  "start_date",
  "end_date"
);
CREATE INDEX "events_status_index" on "events"("status");
CREATE INDEX "events_category_id_index" on "events"("category_id");
CREATE UNIQUE INDEX "events_slug_unique" on "events"("slug");
CREATE TABLE IF NOT EXISTS "event_participants"(
  "id" integer primary key autoincrement not null,
  "event_id" integer not null,
  "student_id" integer,
  "staff_id" integer,
  "parent_id" integer,
  "participant_type" varchar check("participant_type" in('student', 'staff', 'parent', 'external')) not null default 'student',
  "external_name" varchar,
  "external_contact" varchar,
  "external_email" varchar,
  "external_organization" varchar,
  "registration_status" varchar check("registration_status" in('registered', 'confirmed', 'attended', 'cancelled', 'no_show')) not null default 'registered',
  "registered_at" datetime,
  "confirmed_at" datetime,
  "checked_in_at" datetime,
  "checked_out_at" datetime,
  "role" varchar,
  "payment_required" tinyint(1) not null default '0',
  "payment_completed" tinyint(1) not null default '0',
  "amount_paid" numeric,
  "payment_reference" varchar,
  "special_requirements" text,
  "notes" text,
  "registered_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("event_id") references "events"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("staff_id") references "staff"("id") on delete cascade,
  foreign key("parent_id") references "parents"("id") on delete cascade,
  foreign key("registered_by") references "users"("id") on delete set null
);
CREATE INDEX "event_participants_event_id_participant_type_index" on "event_participants"(
  "event_id",
  "participant_type"
);
CREATE INDEX "event_participants_registration_status_index" on "event_participants"(
  "registration_status"
);
CREATE TABLE IF NOT EXISTS "activity_types"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "slug" varchar not null,
  "description" text,
  "category" varchar check("category" in('sports', 'arts', 'academic', 'cultural', 'service', 'leadership', 'other')) not null default 'other',
  "color" varchar not null default '#10B981',
  "icon" varchar,
  "is_active" tinyint(1) not null default '1',
  "sort_order" integer not null default '0',
  "created_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("created_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "activity_types_slug_unique" on "activity_types"("slug");
CREATE TABLE IF NOT EXISTS "activities"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "slug" varchar not null,
  "type_id" integer not null,
  "academic_year_id" integer,
  "description" text,
  "short_description" varchar,
  "meeting_frequency" varchar check("meeting_frequency" in('daily', 'weekly', 'bi-weekly', 'monthly', 'as_needed')) not null default 'weekly',
  "meeting_days" text,
  "meeting_start_time" time,
  "meeting_end_time" time,
  "meeting_venue" varchar,
  "patron_id" integer,
  "co_patron_id" integer,
  "student_leader_id" integer,
  "student_vice_leader_id" integer,
  "min_members" integer,
  "max_members" integer,
  "open_enrollment" tinyint(1) not null default '1',
  "enrollment_start" date,
  "enrollment_end" date,
  "eligibility_criteria" text,
  "membership_fee" numeric,
  "status" varchar check("status" in('active', 'inactive', 'suspended')) not null default 'active',
  "suspension_reason" text,
  "suspended_at" datetime,
  "logo" varchar,
  "banner_image" varchar,
  "social_links" text,
  "objectives" text,
  "achievements" text,
  "founded_date" date,
  "motto" varchar,
  "created_by" integer,
  "updated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("type_id") references "activity_types"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("patron_id") references "staff"("id") on delete set null,
  foreign key("co_patron_id") references "staff"("id") on delete set null,
  foreign key("student_leader_id") references "students"("id") on delete set null,
  foreign key("student_vice_leader_id") references "students"("id") on delete set null,
  foreign key("created_by") references "users"("id") on delete set null,
  foreign key("updated_by") references "users"("id") on delete set null
);
CREATE INDEX "activities_status_index" on "activities"("status");
CREATE INDEX "activities_type_id_index" on "activities"("type_id");
CREATE UNIQUE INDEX "activities_slug_unique" on "activities"("slug");
CREATE TABLE IF NOT EXISTS "activity_members"(
  "id" integer primary key autoincrement not null,
  "activity_id" integer not null,
  "student_id" integer not null,
  "academic_year_id" integer,
  "joined_date" date not null,
  "left_date" date,
  "status" varchar check("status" in('active', 'inactive', 'suspended', 'graduated')) not null default 'active',
  "role" varchar,
  "is_leader" tinyint(1) not null default '0',
  "suspension_reason" text,
  "fee_paid" tinyint(1) not null default '0',
  "fee_amount" numeric,
  "fee_paid_date" date,
  "fee_receipt_number" varchar,
  "attendance_count" integer not null default '0',
  "meeting_count" integer not null default '0',
  "attendance_percentage" numeric not null default '0',
  "achievements" text,
  "notes" text,
  "added_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("activity_id") references "activities"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("added_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "unique_activity_membership" on "activity_members"(
  "activity_id",
  "student_id",
  "academic_year_id"
);
CREATE INDEX "activity_members_status_index" on "activity_members"("status");
CREATE TABLE IF NOT EXISTS "competitions"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "slug" varchar not null,
  "academic_year_id" integer,
  "term_id" integer,
  "activity_id" integer,
  "description" text,
  "type" varchar check("type" in('internal', 'inter_school', 'regional', 'national', 'international')) not null default 'internal',
  "category" varchar check("category" in('sports', 'academics', 'arts', 'cultural', 'science', 'debate', 'music', 'drama', 'other')) not null default 'other',
  "subcategory" varchar,
  "organizer" varchar,
  "organizer_contact" varchar,
  "start_date" date not null,
  "end_date" date,
  "venue" varchar,
  "venue_address" varchar,
  "participation_type" varchar check("participation_type" in('individual', 'team', 'both')) not null default 'individual',
  "max_participants" integer,
  "team_size" integer,
  "registration_deadline" date,
  "registration_fee" numeric,
  "status" varchar check("status" in('upcoming', 'ongoing', 'completed', 'cancelled')) not null default 'upcoming',
  "cancellation_reason" text,
  "overall_results" text,
  "gold_medals" integer not null default '0',
  "silver_medals" integer not null default '0',
  "bronze_medals" integer not null default '0',
  "total_participants" integer not null default '0',
  "highlights" text,
  "banner_image" varchar,
  "photos" text,
  "documents" text,
  "coordinator_id" integer,
  "created_by" integer,
  "updated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("term_id") references "terms"("id") on delete set null,
  foreign key("activity_id") references "activities"("id") on delete set null,
  foreign key("coordinator_id") references "staff"("id") on delete set null,
  foreign key("created_by") references "users"("id") on delete set null,
  foreign key("updated_by") references "users"("id") on delete set null
);
CREATE INDEX "competitions_start_date_end_date_index" on "competitions"(
  "start_date",
  "end_date"
);
CREATE INDEX "competitions_status_index" on "competitions"("status");
CREATE INDEX "competitions_type_index" on "competitions"("type");
CREATE INDEX "competitions_category_index" on "competitions"("category");
CREATE UNIQUE INDEX "competitions_slug_unique" on "competitions"("slug");
CREATE TABLE IF NOT EXISTS "competition_results"(
  "id" integer primary key autoincrement not null,
  "competition_id" integer not null,
  "student_id" integer,
  "team_name" varchar,
  "team_members" text,
  "event_name" varchar,
  "event_category" varchar,
  "position" varchar check("position" in('1st', '2nd', '3rd', '4th', '5th', 'participant', 'finalist', 'semi_finalist', 'quarter_finalist', 'disqualified')),
  "award" varchar check("award" in('gold', 'silver', 'bronze', 'merit', 'participation', 'special', 'none')) not null default 'none',
  "award_name" varchar,
  "score" varchar,
  "unit" varchar,
  "rank" integer,
  "total_participants" integer,
  "is_record" tinyint(1) not null default '0',
  "record_details" varchar,
  "is_featured" tinyint(1) not null default '0',
  "citation" text,
  "certificate_issued" tinyint(1) not null default '0',
  "certificate_number" varchar,
  "certificate_date" date,
  "certificate_file" varchar,
  "photo" varchar,
  "media" text,
  "notes" text,
  "recorded_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("competition_id") references "competitions"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("recorded_by") references "users"("id") on delete set null
);
CREATE INDEX "competition_results_competition_id_position_index" on "competition_results"(
  "competition_id",
  "position"
);
CREATE INDEX "competition_results_award_index" on "competition_results"(
  "award"
);
CREATE TABLE IF NOT EXISTS "lesson_plan_templates"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "structure" text,
  "template_type" varchar check("template_type" in('standard', 'detailed', 'simple', 'custom')) not null default 'standard',
  "is_default" tinyint(1) not null default '0',
  "is_active" tinyint(1) not null default '1',
  "created_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("created_by") references "users"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "lesson_plans"(
  "id" integer primary key autoincrement not null,
  "template_id" integer,
  "teacher_id" integer not null,
  "subject_id" integer not null,
  "class_id" integer not null,
  "academic_year_id" integer,
  "term_id" integer,
  "title" varchar not null,
  "topic" varchar not null,
  "sub_topic" varchar,
  "lesson_date" date not null,
  "start_time" time,
  "end_time" time,
  "duration_minutes" integer,
  "week_number" integer,
  "objectives" text not null,
  "learning_outcomes" text,
  "prerequisite_knowledge" text,
  "introduction" text,
  "main_content" text not null,
  "activities" text,
  "differentiation" text,
  "assessment_strategies" text,
  "conclusion" text,
  "homework" text,
  "resources_needed" text,
  "teacher_notes" text,
  "reflection" text,
  "status" varchar check("status" in('draft', 'pending_review', 'approved', 'rejected', 'completed')) not null default 'draft',
  "reviewed_by" integer,
  "reviewed_at" datetime,
  "review_comments" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("template_id") references "lesson_plan_templates"("id") on delete set null,
  foreign key("teacher_id") references "staff"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("term_id") references "terms"("id") on delete set null,
  foreign key("reviewed_by") references "users"("id") on delete set null
);
CREATE INDEX "lesson_plans_teacher_id_lesson_date_index" on "lesson_plans"(
  "teacher_id",
  "lesson_date"
);
CREATE INDEX "lesson_plans_subject_id_class_id_index" on "lesson_plans"(
  "subject_id",
  "class_id"
);
CREATE INDEX "lesson_plans_status_index" on "lesson_plans"("status");
CREATE TABLE IF NOT EXISTS "teaching_resource_categories"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "icon" varchar,
  "color" varchar,
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "teaching_resources"(
  "id" integer primary key autoincrement not null,
  "category_id" integer,
  "uploaded_by" integer not null,
  "subject_id" integer,
  "class_id" integer,
  "title" varchar not null,
  "description" text,
  "resource_type" varchar check("resource_type" in('document', 'video', 'audio', 'image', 'presentation', 'spreadsheet', 'link', 'other')) not null default 'document',
  "file_path" varchar,
  "file_name" varchar,
  "file_type" varchar,
  "file_size" integer,
  "external_url" varchar,
  "tags" text,
  "visibility" varchar check("visibility" in('private', 'department', 'school', 'public')) not null default 'department',
  "download_count" integer not null default '0',
  "view_count" integer not null default '0',
  "is_featured" tinyint(1) not null default '0',
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("category_id") references "teaching_resource_categories"("id") on delete set null,
  foreign key("uploaded_by") references "users"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete set null,
  foreign key("class_id") references "classes"("id") on delete set null
);
CREATE INDEX "teaching_resources_subject_id_class_id_index" on "teaching_resources"(
  "subject_id",
  "class_id"
);
CREATE INDEX "teaching_resources_resource_type_index" on "teaching_resources"(
  "resource_type"
);
CREATE INDEX "teaching_resources_visibility_index" on "teaching_resources"(
  "visibility"
);
CREATE TABLE IF NOT EXISTS "lesson_plan_resources"(
  "id" integer primary key autoincrement not null,
  "lesson_plan_id" integer not null,
  "resource_id" integer not null,
  "usage_notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("lesson_plan_id") references "lesson_plans"("id") on delete cascade,
  foreign key("resource_id") references "teaching_resources"("id") on delete cascade
);
CREATE UNIQUE INDEX "lesson_plan_resources_lesson_plan_id_resource_id_unique" on "lesson_plan_resources"(
  "lesson_plan_id",
  "resource_id"
);
CREATE TABLE IF NOT EXISTS "classroom_material_categories"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "classroom_materials"(
  "id" integer primary key autoincrement not null,
  "category_id" integer,
  "name" varchar not null,
  "description" text,
  "material_code" varchar,
  "material_type" varchar check("material_type" in('equipment', 'consumable', 'technology', 'furniture', 'teaching_aid', 'other')) not null default 'teaching_aid',
  "total_quantity" integer not null default '0',
  "available_quantity" integer not null default '0',
  "minimum_quantity" integer not null default '1',
  "unit" varchar not null default 'pieces',
  "storage_location" varchar,
  "unit_cost" numeric,
  "condition" varchar check("condition" in('new', 'good', 'fair', 'poor', 'damaged')) not null default 'good',
  "purchase_date" date,
  "last_maintenance_date" date,
  "next_maintenance_date" date,
  "maintenance_notes" text,
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("category_id") references "classroom_material_categories"("id") on delete set null
);
CREATE INDEX "classroom_materials_material_type_index" on "classroom_materials"(
  "material_type"
);
CREATE INDEX "classroom_materials_condition_index" on "classroom_materials"(
  "condition"
);
CREATE UNIQUE INDEX "classroom_materials_material_code_unique" on "classroom_materials"(
  "material_code"
);
CREATE TABLE IF NOT EXISTS "classroom_material_allocations"(
  "id" integer primary key autoincrement not null,
  "material_id" integer not null,
  "allocated_to" integer not null,
  "class_id" integer,
  "allocated_by" integer not null,
  "quantity" integer not null,
  "allocation_date" date not null,
  "expected_return_date" date,
  "actual_return_date" date,
  "status" varchar check("status" in('allocated', 'in_use', 'returned', 'lost', 'damaged')) not null default 'allocated',
  "purpose" text,
  "notes" text,
  "return_condition" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("material_id") references "classroom_materials"("id") on delete cascade,
  foreign key("allocated_to") references "users"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete set null,
  foreign key("allocated_by") references "users"("id") on delete cascade
);
CREATE INDEX "classroom_material_allocations_material_id_status_index" on "classroom_material_allocations"(
  "material_id",
  "status"
);
CREATE INDEX "classroom_material_allocations_allocated_to_index" on "classroom_material_allocations"(
  "allocated_to"
);
CREATE TABLE IF NOT EXISTS "assignments"(
  "id" integer primary key autoincrement not null,
  "teacher_id" integer not null,
  "subject_id" integer not null,
  "class_id" integer not null,
  "academic_year_id" integer,
  "term_id" integer,
  "lesson_plan_id" integer,
  "title" varchar not null,
  "description" text not null,
  "instructions" text,
  "assignment_type" varchar check("assignment_type" in('homework', 'classwork', 'project', 'quiz', 'essay', 'research', 'presentation', 'practical', 'other')) not null default 'homework',
  "total_marks" numeric not null default '100',
  "passing_marks" numeric,
  "weight_percentage" numeric,
  "assigned_date" datetime not null,
  "due_date" datetime not null,
  "late_submission_deadline" datetime,
  "late_penalty_percentage" numeric not null default '0',
  "allow_late_submission" tinyint(1) not null default '1',
  "allow_resubmission" tinyint(1) not null default '0',
  "max_resubmissions" integer,
  "is_group_assignment" tinyint(1) not null default '0',
  "max_group_size" integer,
  "allowed_file_types" text,
  "max_file_size" integer,
  "max_files" integer,
  "rubric" text,
  "attachments" text,
  "visibility" varchar check("visibility" in('draft', 'scheduled', 'published', 'closed', 'archived')) not null default 'draft',
  "publish_date" datetime,
  "notify_students" tinyint(1) not null default '1',
  "notify_parents" tinyint(1) not null default '0',
  "teacher_notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("teacher_id") references "staff"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("term_id") references "terms"("id") on delete set null,
  foreign key("lesson_plan_id") references "lesson_plans"("id") on delete set null
);
CREATE INDEX "assignments_teacher_id_subject_id_index" on "assignments"(
  "teacher_id",
  "subject_id"
);
CREATE INDEX "assignments_class_id_due_date_index" on "assignments"(
  "class_id",
  "due_date"
);
CREATE INDEX "assignments_visibility_index" on "assignments"("visibility");
CREATE INDEX "assignments_assignment_type_index" on "assignments"(
  "assignment_type"
);
CREATE TABLE IF NOT EXISTS "assignment_submissions"(
  "id" integer primary key autoincrement not null,
  "assignment_id" integer not null,
  "student_id" integer not null,
  "content" text,
  "files" text,
  "external_link" varchar,
  "submitted_at" datetime not null,
  "is_late" tinyint(1) not null default '0',
  "submission_number" integer not null default '1',
  "status" varchar check("status" in('submitted', 'in_review', 'graded', 'returned', 'resubmission_requested')) not null default 'submitted',
  "marks_obtained" numeric,
  "late_penalty_applied" numeric not null default '0',
  "final_marks" numeric,
  "grade" varchar,
  "feedback" text,
  "rubric_scores" text,
  "graded_by" integer,
  "graded_at" datetime,
  "private_notes" text,
  "plagiarism_checked" tinyint(1) not null default '0',
  "plagiarism_score" numeric,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("assignment_id") references "assignments"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("graded_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "assignment_submissions_assignment_id_student_id_submission_number_unique" on "assignment_submissions"(
  "assignment_id",
  "student_id",
  "submission_number"
);
CREATE INDEX "assignment_submissions_assignment_id_status_index" on "assignment_submissions"(
  "assignment_id",
  "status"
);
CREATE INDEX "assignment_submissions_student_id_submitted_at_index" on "assignment_submissions"(
  "student_id",
  "submitted_at"
);
CREATE TABLE IF NOT EXISTS "assignment_groups"(
  "id" integer primary key autoincrement not null,
  "assignment_id" integer not null,
  "group_name" varchar not null,
  "leader_id" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("assignment_id") references "assignments"("id") on delete cascade,
  foreign key("leader_id") references "students"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "assignment_group_members"(
  "id" integer primary key autoincrement not null,
  "group_id" integer not null,
  "student_id" integer not null,
  "role" varchar check("role" in('leader', 'member')) not null default 'member',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("group_id") references "assignment_groups"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade
);
CREATE UNIQUE INDEX "assignment_group_members_group_id_student_id_unique" on "assignment_group_members"(
  "group_id",
  "student_id"
);
CREATE TABLE IF NOT EXISTS "submission_comments"(
  "id" integer primary key autoincrement not null,
  "submission_id" integer not null,
  "user_id" integer not null,
  "comment" text not null,
  "is_private" tinyint(1) not null default '0',
  "parent_id" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("submission_id") references "assignment_submissions"("id") on delete cascade,
  foreign key("user_id") references "users"("id") on delete cascade,
  foreign key("parent_id") references "submission_comments"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "alumni"(
  "id" integer primary key autoincrement not null,
  "student_id" integer,
  "first_name" varchar not null,
  "last_name" varchar not null,
  "middle_name" varchar,
  "email" varchar not null,
  "phone" varchar,
  "alternate_phone" varchar,
  "date_of_birth" date,
  "gender" varchar check("gender" in('male', 'female', 'other')),
  "current_address" text,
  "city" varchar,
  "state" varchar,
  "country" varchar,
  "postal_code" varchar,
  "admission_number" varchar,
  "graduation_year" integer not null,
  "graduation_class" varchar,
  "stream" varchar,
  "achievements" text,
  "current_employer" varchar,
  "job_title" varchar,
  "industry" varchar,
  "work_address" text,
  "work_email" varchar,
  "work_phone" varchar,
  "linkedin_url" varchar,
  "status" varchar check("status" in('active', 'inactive', 'lost_contact')) not null default 'active',
  "willing_to_mentor" tinyint(1) not null default '0',
  "willing_to_donate" tinyint(1) not null default '0',
  "interested_in_events" tinyint(1) not null default '1',
  "facebook_url" varchar,
  "twitter_url" varchar,
  "instagram_url" varchar,
  "notes" text,
  "profile_photo" varchar,
  "last_contact_date" datetime,
  "created_by" integer,
  "updated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("created_by") references "users"("id") on delete set null,
  foreign key("updated_by") references "users"("id") on delete set null
);
CREATE INDEX "alumni_graduation_year_index" on "alumni"("graduation_year");
CREATE INDEX "alumni_status_index" on "alumni"("status");
CREATE INDEX "alumni_first_name_last_name_index" on "alumni"(
  "first_name",
  "last_name"
);
CREATE UNIQUE INDEX "alumni_email_unique" on "alumni"("email");
CREATE TABLE IF NOT EXISTS "alumni_career_history"(
  "id" integer primary key autoincrement not null,
  "alumni_id" integer not null,
  "company_name" varchar not null,
  "job_title" varchar not null,
  "industry" varchar,
  "job_description" text,
  "location" varchar,
  "start_date" date not null,
  "end_date" date,
  "is_current" tinyint(1) not null default '0',
  "employment_type" varchar check("employment_type" in('full_time', 'part_time', 'contract', 'freelance', 'internship')) not null default 'full_time',
  "achievements" text,
  "skills_acquired" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("alumni_id") references "alumni"("id") on delete cascade
);
CREATE INDEX "alumni_career_history_alumni_id_index" on "alumni_career_history"(
  "alumni_id"
);
CREATE INDEX "alumni_career_history_is_current_index" on "alumni_career_history"(
  "is_current"
);
CREATE TABLE IF NOT EXISTS "alumni_education_history"(
  "id" integer primary key autoincrement not null,
  "alumni_id" integer not null,
  "institution_name" varchar not null,
  "degree_level" varchar not null,
  "field_of_study" varchar not null,
  "major" varchar,
  "minor" varchar,
  "start_date" date not null,
  "end_date" date,
  "is_current" tinyint(1) not null default '0',
  "grade_gpa" varchar,
  "achievements" text,
  "location" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("alumni_id") references "alumni"("id") on delete cascade
);
CREATE INDEX "alumni_education_history_alumni_id_index" on "alumni_education_history"(
  "alumni_id"
);
CREATE TABLE IF NOT EXISTS "alumni_contacts"(
  "id" integer primary key autoincrement not null,
  "alumni_id" integer not null,
  "contacted_by" integer not null,
  "contact_type" varchar check("contact_type" in('email', 'phone', 'meeting', 'event', 'social_media', 'letter', 'other')) not null default 'email',
  "subject" varchar,
  "notes" text not null,
  "contact_date" date not null,
  "contact_time" time,
  "outcome" varchar check("outcome" in('successful', 'no_response', 'follow_up_needed', 'information_updated')),
  "follow_up_date" date,
  "attachments" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("alumni_id") references "alumni"("id") on delete cascade,
  foreign key("contacted_by") references "users"("id") on delete cascade
);
CREATE INDEX "alumni_contacts_alumni_id_index" on "alumni_contacts"(
  "alumni_id"
);
CREATE INDEX "alumni_contacts_contact_date_index" on "alumni_contacts"(
  "contact_date"
);
CREATE INDEX "alumni_contacts_outcome_index" on "alumni_contacts"("outcome");
CREATE TABLE IF NOT EXISTS "mentorship_programs"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "start_date" date not null,
  "end_date" date,
  "status" varchar check("status" in('active', 'completed', 'cancelled', 'on_hold')) not null default 'active',
  "max_mentees_per_mentor" integer not null default '5',
  "focus_areas" text,
  "requirements" text,
  "goals" text,
  "coordinator_id" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("coordinator_id") references "users"("id") on delete set null
);
CREATE INDEX "mentorship_programs_status_index" on "mentorship_programs"(
  "status"
);
CREATE INDEX "mentorship_programs_start_date_index" on "mentorship_programs"(
  "start_date"
);
CREATE TABLE IF NOT EXISTS "mentorship_relationships"(
  "id" integer primary key autoincrement not null,
  "program_id" integer not null,
  "mentor_id" integer not null,
  "mentee_id" integer not null,
  "start_date" date not null,
  "end_date" date,
  "status" varchar check("status" in('pending', 'active', 'completed', 'cancelled')) not null default 'pending',
  "goals" text,
  "mentor_notes" text,
  "mentee_feedback" text,
  "rating" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("program_id") references "mentorship_programs"("id") on delete cascade,
  foreign key("mentor_id") references "alumni"("id") on delete cascade,
  foreign key("mentee_id") references "students"("id") on delete cascade
);
CREATE INDEX "mentorship_relationships_program_id_index" on "mentorship_relationships"(
  "program_id"
);
CREATE INDEX "mentorship_relationships_mentor_id_index" on "mentorship_relationships"(
  "mentor_id"
);
CREATE INDEX "mentorship_relationships_mentee_id_index" on "mentorship_relationships"(
  "mentee_id"
);
CREATE INDEX "mentorship_relationships_status_index" on "mentorship_relationships"(
  "status"
);
CREATE TABLE IF NOT EXISTS "mentorship_sessions"(
  "id" integer primary key autoincrement not null,
  "relationship_id" integer not null,
  "session_date" datetime not null,
  "duration_minutes" integer,
  "type" varchar check("type" in('in_person', 'video_call', 'phone_call', 'email')) not null default 'video_call',
  "location" varchar,
  "agenda" text,
  "notes" text,
  "action_items" text,
  "status" varchar check("status" in('scheduled', 'completed', 'cancelled', 'rescheduled')) not null default 'scheduled',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("relationship_id") references "mentorship_relationships"("id") on delete cascade
);
CREATE INDEX "mentorship_sessions_relationship_id_index" on "mentorship_sessions"(
  "relationship_id"
);
CREATE INDEX "mentorship_sessions_session_date_index" on "mentorship_sessions"(
  "session_date"
);
CREATE INDEX "mentorship_sessions_status_index" on "mentorship_sessions"(
  "status"
);
CREATE TABLE IF NOT EXISTS "contribution_campaigns"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "target_amount" numeric,
  "start_date" date not null,
  "end_date" date,
  "status" varchar check("status" in('active', 'completed', 'cancelled')) not null default 'active',
  "purpose" varchar,
  "managed_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("managed_by") references "users"("id") on delete set null
);
CREATE INDEX "contribution_campaigns_status_index" on "contribution_campaigns"(
  "status"
);
CREATE INDEX "contribution_campaigns_start_date_index" on "contribution_campaigns"(
  "start_date"
);
CREATE TABLE IF NOT EXISTS "alumni_contributions"(
  "id" integer primary key autoincrement not null,
  "alumni_id" integer not null,
  "campaign_id" integer,
  "contribution_type" varchar check("contribution_type" in('monetary', 'equipment', 'scholarship', 'time', 'expertise', 'other')) not null default 'monetary',
  "amount" numeric,
  "currency" varchar not null default 'USD',
  "description" text,
  "contribution_date" date not null,
  "payment_method" varchar check("payment_method" in('cash', 'bank_transfer', 'cheque', 'mobile_money', 'online', 'other')),
  "transaction_reference" varchar,
  "status" varchar check("status" in('pending', 'received', 'acknowledged', 'cancelled')) not null default 'received',
  "is_anonymous" tinyint(1) not null default '0',
  "tax_receipt_issued" tinyint(1) not null default '0',
  "tax_receipt_number" varchar,
  "acknowledgment_sent" text,
  "acknowledgment_date" date,
  "attachments" text,
  "recorded_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("alumni_id") references "alumni"("id") on delete cascade,
  foreign key("campaign_id") references "contribution_campaigns"("id") on delete set null,
  foreign key("recorded_by") references "users"("id") on delete set null
);
CREATE INDEX "alumni_contributions_alumni_id_index" on "alumni_contributions"(
  "alumni_id"
);
CREATE INDEX "alumni_contributions_campaign_id_index" on "alumni_contributions"(
  "campaign_id"
);
CREATE INDEX "alumni_contributions_contribution_type_index" on "alumni_contributions"(
  "contribution_type"
);
CREATE INDEX "alumni_contributions_contribution_date_index" on "alumni_contributions"(
  "contribution_date"
);
CREATE INDEX "alumni_contributions_status_index" on "alumni_contributions"(
  "status"
);
CREATE TABLE IF NOT EXISTS "alumni_events"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "event_type" varchar check("event_type" in('reunion', 'networking', 'fundraising', 'awards', 'workshop', 'social', 'other')) not null default 'reunion',
  "event_date" datetime not null,
  "end_date" datetime,
  "venue" varchar,
  "address" text,
  "expected_attendees" integer,
  "ticket_price" numeric,
  "status" varchar check("status" in('planned', 'confirmed', 'completed', 'cancelled')) not null default 'planned',
  "agenda" text,
  "attachments" text,
  "organized_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("organized_by") references "users"("id") on delete set null
);
CREATE INDEX "alumni_events_event_type_index" on "alumni_events"("event_type");
CREATE INDEX "alumni_events_event_date_index" on "alumni_events"("event_date");
CREATE INDEX "alumni_events_status_index" on "alumni_events"("status");
CREATE TABLE IF NOT EXISTS "alumni_event_attendees"(
  "id" integer primary key autoincrement not null,
  "event_id" integer not null,
  "alumni_id" integer not null,
  "rsvp_status" varchar check("rsvp_status" in('invited', 'confirmed', 'declined', 'attended', 'no_show')) not null default 'invited',
  "rsvp_date" date,
  "guest_count" integer not null default '0',
  "dietary_requirements" text,
  "special_needs" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("event_id") references "alumni_events"("id") on delete cascade,
  foreign key("alumni_id") references "alumni"("id") on delete cascade
);
CREATE UNIQUE INDEX "alumni_event_attendees_event_id_alumni_id_unique" on "alumni_event_attendees"(
  "event_id",
  "alumni_id"
);
CREATE INDEX "alumni_event_attendees_event_id_index" on "alumni_event_attendees"(
  "event_id"
);
CREATE INDEX "alumni_event_attendees_alumni_id_index" on "alumni_event_attendees"(
  "alumni_id"
);
CREATE INDEX "alumni_event_attendees_rsvp_status_index" on "alumni_event_attendees"(
  "rsvp_status"
);
CREATE TABLE IF NOT EXISTS "promotion_batches"(
  "id" integer primary key autoincrement not null,
  "from_academic_year_id" integer not null,
  "to_academic_year_id" integer not null,
  "batch_name" varchar not null,
  "status" varchar check("status" in('pending', 'in_progress', 'completed', 'cancelled')) not null default 'pending',
  "total_students" integer not null default '0',
  "promoted_count" integer not null default '0',
  "repeated_count" integer not null default '0',
  "graduated_count" integer not null default '0',
  "notes" text,
  "criteria" text,
  "started_at" datetime,
  "completed_at" datetime,
  "initiated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("from_academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("to_academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("initiated_by") references "users"("id") on delete set null
);
CREATE INDEX "promotion_batches_from_academic_year_id_status_index" on "promotion_batches"(
  "from_academic_year_id",
  "status"
);
CREATE INDEX "promotion_batches_to_academic_year_id_index" on "promotion_batches"(
  "to_academic_year_id"
);
CREATE TABLE IF NOT EXISTS "student_promotions"(
  "id" integer primary key autoincrement not null,
  "batch_id" integer not null,
  "student_id" integer not null,
  "from_class_id" integer not null,
  "to_class_id" integer,
  "from_academic_year_id" integer not null,
  "to_academic_year_id" integer not null,
  "promotion_type" varchar check("promotion_type" in('promoted', 'repeated', 'graduated', 'transferred', 'dropped_out')) not null default 'promoted',
  "status" varchar check("status" in('pending', 'approved', 'rejected', 'completed')) not null default 'pending',
  "average_score" numeric,
  "attendance_percentage" numeric,
  "total_subjects" integer,
  "passed_subjects" integer,
  "failed_subjects" integer,
  "meets_academic_criteria" tinyint(1) not null default '0',
  "meets_attendance_criteria" tinyint(1) not null default '0',
  "meets_discipline_criteria" tinyint(1) not null default '1',
  "is_eligible" tinyint(1) not null default '0',
  "requires_approval" tinyint(1) not null default '0',
  "override_reason" text,
  "approved_by" integer,
  "approved_at" datetime,
  "remarks" text,
  "performance_summary" text,
  "processed_by" integer,
  "processed_at" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("batch_id") references "promotion_batches"("id") on delete cascade,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("from_class_id") references "classes"("id") on delete cascade,
  foreign key("to_class_id") references "classes"("id") on delete cascade,
  foreign key("from_academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("to_academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("approved_by") references "users"("id") on delete set null,
  foreign key("processed_by") references "users"("id") on delete set null
);
CREATE INDEX "student_promotions_student_id_from_academic_year_id_index" on "student_promotions"(
  "student_id",
  "from_academic_year_id"
);
CREATE INDEX "student_promotions_batch_id_status_index" on "student_promotions"(
  "batch_id",
  "status"
);
CREATE INDEX "student_promotions_promotion_type_is_eligible_index" on "student_promotions"(
  "promotion_type",
  "is_eligible"
);
CREATE TABLE IF NOT EXISTS "promotion_criteria"(
  "id" integer primary key autoincrement not null,
  "academic_year_id" integer not null,
  "class_id" integer,
  "criteria_name" varchar not null,
  "minimum_average_score" numeric not null default '50',
  "minimum_passed_subjects" integer,
  "minimum_pass_percentage" numeric,
  "subject_requirements" text,
  "minimum_attendance_percentage" numeric not null default '75',
  "maximum_suspensions" integer not null default '2',
  "maximum_warnings" integer,
  "allow_repeat" tinyint(1) not null default '1',
  "max_repeat_times" integer not null default '1',
  "requires_manual_approval" tinyint(1) not null default '0',
  "exemptions" text,
  "is_active" tinyint(1) not null default '1',
  "description" text,
  "created_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade,
  foreign key("class_id") references "classes"("id") on delete cascade,
  foreign key("created_by") references "users"("id") on delete set null
);
CREATE INDEX "promotion_criteria_academic_year_id_is_active_index" on "promotion_criteria"(
  "academic_year_id",
  "is_active"
);
CREATE TABLE IF NOT EXISTS "promotion_history"(
  "id" integer primary key autoincrement not null,
  "student_id" integer not null,
  "promotion_id" integer not null,
  "action" varchar not null,
  "description" text,
  "old_data" text,
  "new_data" text,
  "performed_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("student_id") references "students"("id") on delete cascade,
  foreign key("promotion_id") references "student_promotions"("id") on delete cascade,
  foreign key("performed_by") references "users"("id") on delete set null
);
CREATE INDEX "promotion_history_student_id_created_at_index" on "promotion_history"(
  "student_id",
  "created_at"
);
CREATE INDEX "promotion_history_promotion_id_index" on "promotion_history"(
  "promotion_id"
);
CREATE TABLE IF NOT EXISTS "lesson_observations"(
  "id" integer primary key autoincrement not null,
  "teacher_id" integer not null,
  "observer_id" integer not null,
  "class_id" integer not null,
  "subject_id" integer not null,
  "academic_year_id" integer not null,
  "observation_title" varchar not null,
  "observation_date" date not null,
  "start_time" time not null,
  "end_time" time not null,
  "lesson_topic" varchar not null,
  "students_present" integer,
  "overall_rating" numeric,
  "rating_scale" varchar check("rating_scale" in('1-5', '1-10', 'percentage')) not null default '1-5',
  "strengths" text,
  "areas_for_improvement" text,
  "recommendations" text,
  "teacher_comments" text,
  "follow_up_actions" text,
  "status" varchar check("status" in('scheduled', 'completed', 'reviewed', 'acknowledged')) not null default 'scheduled',
  "acknowledged_at" datetime,
  "follow_up_date" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("teacher_id") references "users"("id") on delete cascade,
  foreign key("observer_id") references "users"("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("academic_year_id") references "academic_years"("id") on delete cascade
);
CREATE INDEX "lesson_observations_teacher_id_observation_date_index" on "lesson_observations"(
  "teacher_id",
  "observation_date"
);
CREATE INDEX "lesson_observations_observer_id_status_index" on "lesson_observations"(
  "observer_id",
  "status"
);
CREATE INDEX "lesson_observations_academic_year_id_status_index" on "lesson_observations"(
  "academic_year_id",
  "status"
);
CREATE TABLE IF NOT EXISTS "lesson_observation_criteria"(
  "id" integer primary key autoincrement not null,
  "category" varchar not null,
  "criterion_name" varchar not null,
  "description" text,
  "max_score" integer not null default '5',
  "weight" integer not null default '1',
  "is_active" tinyint(1) not null default '1',
  "display_order" integer not null default '0',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE INDEX "lesson_observation_criteria_category_is_active_index" on "lesson_observation_criteria"(
  "category",
  "is_active"
);
CREATE TABLE IF NOT EXISTS "lesson_observation_ratings"(
  "id" integer primary key autoincrement not null,
  "lesson_observation_id" integer not null,
  "criterion_id" integer not null,
  "score" integer not null,
  "comments" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("lesson_observation_id") references "lesson_observations"("id") on delete cascade,
  foreign key("criterion_id") references "lesson_observation_criteria"("id") on delete cascade
);
CREATE UNIQUE INDEX "lesson_observation_ratings_lesson_observation_id_criterion_id_unique" on "lesson_observation_ratings"(
  "lesson_observation_id",
  "criterion_id"
);
CREATE TABLE IF NOT EXISTS "performance_reviews"(
  "id" integer primary key autoincrement not null,
  "staff_id" integer not null,
  "reviewer_id" integer not null,
  "academic_year_id" integer not null,
  "review_period" varchar not null,
  "review_type" varchar check("review_type" in('annual', 'mid-year', 'probation', 'quarterly', 'special')) not null default 'annual',
  "review_date" date not null,
  "period_start_date" date not null,
  "period_end_date" date not null,
  "self_assessment" text,
  "self_achievements" text,
  "self_challenges" text,
  "self_development_needs" text,
  "self_assessment_submitted_at" datetime,
  "supervisor_assessment" text,
  "supervisor_achievements" text,
  "supervisor_areas_for_improvement" text,
  "supervisor_recommendations" text,
  "competency_ratings" text,
  "overall_rating" numeric,
  "performance_level" varchar check("performance_level" in('outstanding', 'exceeds', 'meets', 'needs_improvement', 'unsatisfactory')),
  "previous_goals_review" text,
  "new_goals" text,
  "training_recommendations" text,
  "training_budget" numeric,
  "status" varchar check("status" in('draft', 'self_assessment_pending', 'supervisor_review', 'completed', 'acknowledged')) not null default 'draft',
  "supervisor_signed_at" datetime,
  "staff_acknowledged_at" datetime,
  "staff_comments" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("staff_id") references "users"("id") on delete cascade,
  foreign key("reviewer_id") references "users"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete cascade
);
CREATE INDEX "performance_reviews_staff_id_review_date_index" on "performance_reviews"(
  "staff_id",
  "review_date"
);
CREATE INDEX "performance_reviews_academic_year_id_review_type_index" on "performance_reviews"(
  "academic_year_id",
  "review_type"
);
CREATE INDEX "performance_reviews_status_index" on "performance_reviews"(
  "status"
);
CREATE TABLE IF NOT EXISTS "performance_review_goals"(
  "id" integer primary key autoincrement not null,
  "performance_review_id" integer not null,
  "goal_category" varchar not null,
  "goal_description" text not null,
  "success_criteria" text,
  "target_date" date,
  "priority" varchar check("priority" in('high', 'medium', 'low')) not null default 'medium',
  "status" varchar check("status" in('not_started', 'in_progress', 'achieved', 'partially_achieved', 'not_achieved')) not null default 'not_started',
  "progress_percentage" integer not null default '0',
  "progress_notes" text,
  "achievement_evidence" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("performance_review_id") references "performance_reviews"("id") on delete cascade
);
CREATE INDEX "performance_review_goals_performance_review_id_status_index" on "performance_review_goals"(
  "performance_review_id",
  "status"
);
CREATE TABLE IF NOT EXISTS "professional_development"(
  "id" integer primary key autoincrement not null,
  "staff_id" integer not null,
  "academic_year_id" integer,
  "activity_title" varchar not null,
  "activity_type" varchar check("activity_type" in('workshop', 'seminar', 'conference', 'course', 'certification', 'webinar', 'mentoring', 'self_study', 'other')) not null default 'workshop',
  "description" text,
  "provider" varchar,
  "facilitator" varchar,
  "start_date" date not null,
  "end_date" date,
  "duration_hours" integer,
  "cpd_points" numeric,
  "format" varchar check("format" in('in-person', 'online', 'hybrid')) not null default 'in-person',
  "location" varchar,
  "certificate_url" varchar,
  "cost" numeric,
  "funding_source" varchar check("funding_source" in('school', 'self', 'sponsor', 'government', 'other')),
  "funding_approved" tinyint(1) not null default '0',
  "learning_outcomes" text,
  "skills_acquired" text,
  "impact_on_teaching" text,
  "implementation_plan" text,
  "satisfaction_rating" numeric,
  "status" varchar check("status" in('planned', 'approved', 'in_progress', 'completed', 'cancelled')) not null default 'planned',
  "approved_by" integer,
  "approved_at" datetime,
  "completed_at" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("staff_id") references "users"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("approved_by") references "users"("id") on delete set null
);
CREATE INDEX "professional_development_staff_id_start_date_index" on "professional_development"(
  "staff_id",
  "start_date"
);
CREATE INDEX "professional_development_academic_year_id_activity_type_index" on "professional_development"(
  "academic_year_id",
  "activity_type"
);
CREATE INDEX "professional_development_status_index" on "professional_development"(
  "status"
);
CREATE TABLE IF NOT EXISTS "professional_development_skills"(
  "id" integer primary key autoincrement not null,
  "professional_development_id" integer not null,
  "skill_name" varchar not null,
  "skill_category" varchar,
  "proficiency_level" varchar check("proficiency_level" in('beginner', 'intermediate', 'advanced', 'expert')) not null default 'beginner',
  "description" text,
  "certified" tinyint(1) not null default '0',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("professional_development_id") references "professional_development"("id") on delete cascade
);
CREATE INDEX "professional_development_skills_professional_development_id_index" on "professional_development_skills"(
  "professional_development_id"
);
CREATE TABLE IF NOT EXISTS "government_reports"(
  "id" integer primary key autoincrement not null,
  "report_type" varchar not null,
  "report_category" varchar not null,
  "report_code" varchar not null,
  "report_title" varchar not null,
  "report_description" text,
  "academic_year_id" integer,
  "term_id" integer,
  "reporting_period" varchar not null,
  "period_start_date" date not null,
  "period_end_date" date not null,
  "report_data" text not null,
  "report_summary" text,
  "data_sources" text,
  "total_records" integer not null default '0',
  "status" varchar check("status" in('draft', 'generated', 'validated', 'submitted', 'acknowledged', 'rejected')) not null default 'draft',
  "generated_at" datetime,
  "generated_by" integer,
  "generation_method" varchar,
  "generation_duration_seconds" integer,
  "file_format" varchar,
  "file_path" varchar,
  "file_size" varchar,
  "file_hash" varchar,
  "is_validated" tinyint(1) not null default '0',
  "validated_at" datetime,
  "validated_by" integer,
  "validation_notes" text,
  "validation_errors" text,
  "is_submitted" tinyint(1) not null default '0',
  "submitted_at" datetime,
  "submitted_by" integer,
  "submission_method" varchar,
  "submission_reference" varchar,
  "submission_status" varchar,
  "authority_name" varchar,
  "authority_code" varchar,
  "authority_contact_email" varchar,
  "authority_contact_phone" varchar,
  "is_acknowledged" tinyint(1) not null default '0',
  "acknowledged_at" datetime,
  "acknowledgement_notes" text,
  "acknowledgement_reference" varchar,
  "version" integer not null default '1',
  "previous_version_id" integer,
  "version_notes" text,
  "notes" text,
  "metadata" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("term_id") references "terms"("id") on delete set null,
  foreign key("generated_by") references "users"("id") on delete set null,
  foreign key("validated_by") references "users"("id") on delete set null,
  foreign key("submitted_by") references "users"("id") on delete set null,
  foreign key("previous_version_id") references "government_reports"("id") on delete set null
);
CREATE INDEX "government_reports_report_type_reporting_period_index" on "government_reports"(
  "report_type",
  "reporting_period"
);
CREATE INDEX "government_reports_status_created_at_index" on "government_reports"(
  "status",
  "created_at"
);
CREATE INDEX "government_reports_is_submitted_submitted_at_index" on "government_reports"(
  "is_submitted",
  "submitted_at"
);
CREATE INDEX "government_reports_period_start_date_index" on "government_reports"(
  "period_start_date"
);
CREATE UNIQUE INDEX "government_reports_report_code_unique" on "government_reports"(
  "report_code"
);
CREATE TABLE IF NOT EXISTS "emis_submissions"(
  "id" integer primary key autoincrement not null,
  "government_report_id" integer,
  "submission_code" varchar not null,
  "emis_category" varchar not null,
  "emis_form_code" varchar,
  "academic_year_id" integer,
  "reporting_period" varchar not null,
  "period_start_date" date not null,
  "period_end_date" date not null,
  "student_enrollment_data" text,
  "staff_data" text,
  "infrastructure_data" text,
  "financial_data" text,
  "examination_data" text,
  "welfare_data" text,
  "total_students" integer not null default '0',
  "total_male_students" integer not null default '0',
  "total_female_students" integer not null default '0',
  "total_teachers" integer not null default '0',
  "total_classrooms" integer not null default '0',
  "student_teacher_ratio" numeric,
  "status" varchar check("status" in('draft', 'ready', 'submitted', 'received', 'processing', 'approved', 'rejected')) not null default 'draft',
  "submitted_at" datetime,
  "submitted_by" integer,
  "submission_channel" varchar,
  "emis_portal_reference" varchar,
  "emis_receipt_number" varchar,
  "emis_response" text,
  "emis_feedback" text,
  "emis_validation_errors" text,
  "emis_response_date" datetime,
  "xml_file_path" varchar,
  "pdf_file_path" varchar,
  "excel_file_path" varchar,
  "meets_emis_standards" tinyint(1) not null default '0',
  "compliance_checklist" text,
  "data_completeness_percentage" numeric,
  "submission_deadline" date,
  "submitted_on_time" tinyint(1),
  "days_before_deadline" integer,
  "contact_person_name" varchar,
  "contact_person_email" varchar,
  "contact_person_phone" varchar,
  "notes" text,
  "metadata" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("government_report_id") references "government_reports"("id") on delete cascade,
  foreign key("academic_year_id") references "academic_years"("id") on delete set null,
  foreign key("submitted_by") references "users"("id") on delete set null
);
CREATE INDEX "emis_submissions_emis_category_reporting_period_index" on "emis_submissions"(
  "emis_category",
  "reporting_period"
);
CREATE INDEX "emis_submissions_status_submission_deadline_index" on "emis_submissions"(
  "status",
  "submission_deadline"
);
CREATE INDEX "emis_submissions_submitted_at_index" on "emis_submissions"(
  "submitted_at"
);
CREATE UNIQUE INDEX "emis_submissions_submission_code_unique" on "emis_submissions"(
  "submission_code"
);
CREATE TABLE IF NOT EXISTS "infrastructure_reports"(
  "id" integer primary key autoincrement not null,
  "government_report_id" integer,
  "report_code" varchar not null,
  "reporting_period" varchar not null,
  "report_date" date not null,
  "classrooms" text,
  "total_classrooms" integer not null default '0',
  "permanent_classrooms" integer not null default '0',
  "temporary_classrooms" integer not null default '0',
  "classrooms_good_condition" integer not null default '0',
  "classrooms_needing_repair" integer not null default '0',
  "laboratories" text,
  "total_laboratories" integer not null default '0',
  "libraries" text,
  "has_library" tinyint(1) not null default '0',
  "library_books_count" integer,
  "staff_rooms" text,
  "administration_offices" text,
  "toilets_boys" integer not null default '0',
  "toilets_girls" integer not null default '0',
  "toilets_staff" integer not null default '0',
  "toilets_accessible" integer not null default '0',
  "toilet_condition" varchar,
  "has_handwashing_facilities" tinyint(1) not null default '0',
  "water_sources" text,
  "has_reliable_water" tinyint(1) not null default '0',
  "has_electricity" tinyint(1) not null default '0',
  "electricity_source" varchar,
  "has_internet" tinyint(1) not null default '0',
  "internet_type" varchar,
  "land_size_acres" numeric,
  "has_playing_field" tinyint(1) not null default '0',
  "sports_facilities" text,
  "has_fence" tinyint(1) not null default '0',
  "fence_condition" varchar,
  "has_gate" tinyint(1) not null default '0',
  "has_ramps" tinyint(1) not null default '0',
  "has_accessible_toilets" tinyint(1) not null default '0',
  "has_special_needs_facilities" tinyint(1) not null default '0',
  "accessibility_features" text,
  "has_fire_extinguishers" tinyint(1) not null default '0',
  "has_first_aid_kit" tinyint(1) not null default '0',
  "has_security_personnel" tinyint(1) not null default '0',
  "has_perimeter_wall" tinyint(1) not null default '0',
  "safety_measures" text,
  "desks_count" integer,
  "chairs_count" integer,
  "tables_count" integer,
  "computers_count" integer,
  "printers_count" integer,
  "teaching_materials" text,
  "has_dining_hall" tinyint(1) not null default '0',
  "dining_hall_capacity" integer,
  "has_kitchen" tinyint(1) not null default '0',
  "has_dormitories" tinyint(1) not null default '0',
  "boarding_capacity" integer,
  "overall_infrastructure_rating" varchar,
  "priority_needs" text,
  "estimated_improvement_cost" numeric,
  "meets_minimum_standards" tinyint(1) not null default '0',
  "compliance_gaps" text,
  "inspector_notes" text,
  "last_inspection_date" date,
  "is_submitted" tinyint(1) not null default '0',
  "submitted_at" datetime,
  "submitted_by" integer,
  "notes" text,
  "metadata" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("government_report_id") references "government_reports"("id") on delete cascade,
  foreign key("submitted_by") references "users"("id") on delete set null
);
CREATE INDEX "infrastructure_reports_report_date_index" on "infrastructure_reports"(
  "report_date"
);
CREATE INDEX "infrastructure_reports_is_submitted_index" on "infrastructure_reports"(
  "is_submitted"
);
CREATE UNIQUE INDEX "infrastructure_reports_report_code_unique" on "infrastructure_reports"(
  "report_code"
);
CREATE TABLE IF NOT EXISTS "education_authority_submissions"(
  "id" integer primary key autoincrement not null,
  "government_report_id" integer,
  "submission_code" varchar not null,
  "authority_type" varchar not null,
  "authority_name" varchar not null,
  "authority_code" varchar,
  "submission_type" varchar not null,
  "submission_category" varchar not null,
  "document_type" varchar not null,
  "document_reference" varchar,
  "reporting_period" varchar not null,
  "period_start_date" date not null,
  "period_end_date" date not null,
  "submission_content" text,
  "submission_data" text,
  "attachments" text,
  "status" varchar check("status" in('draft', 'ready', 'submitted', 'received', 'under_review', 'approved', 'rejected', 'returned')) not null default 'draft',
  "submitted_at" datetime,
  "submitted_by" integer,
  "submission_method" varchar,
  "authority_response" text,
  "authority_feedback" text,
  "response_received_at" datetime,
  "response_reference" varchar,
  "requires_follow_up" tinyint(1) not null default '0',
  "follow_up_date" date,
  "follow_up_notes" text,
  "follow_up_completed" tinyint(1) not null default '0',
  "submission_deadline" date,
  "submitted_on_time" tinyint(1),
  "days_to_deadline" integer,
  "authority_contact_person" varchar,
  "authority_contact_email" varchar,
  "authority_contact_phone" varchar,
  "notes" text,
  "metadata" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("government_report_id") references "government_reports"("id") on delete cascade,
  foreign key("submitted_by") references "users"("id") on delete set null
);
CREATE INDEX "education_authority_submissions_authority_type_submission_category_index" on "education_authority_submissions"(
  "authority_type",
  "submission_category"
);
CREATE INDEX "education_authority_submissions_status_submission_deadline_index" on "education_authority_submissions"(
  "status",
  "submission_deadline"
);
CREATE INDEX "education_authority_submissions_submitted_at_index" on "education_authority_submissions"(
  "submitted_at"
);
CREATE UNIQUE INDEX "education_authority_submissions_submission_code_unique" on "education_authority_submissions"(
  "submission_code"
);
CREATE TABLE IF NOT EXISTS "sms_credits"(
  "id" integer primary key autoincrement not null,
  "balance" numeric not null default '0',
  "reserved_balance" numeric not null default '0',
  "total_purchased" numeric not null default '0',
  "total_used" numeric not null default '0',
  "total_messages_sent" integer not null default '0',
  "average_cost_per_message" numeric not null default '0',
  "last_purchase_date" date,
  "last_usage_date" date,
  "low_balance_threshold" numeric not null default '100',
  "low_balance_alert_enabled" tinyint(1) not null default '1',
  "last_low_balance_alert_sent" datetime,
  "auto_topup_enabled" tinyint(1) not null default '0',
  "auto_topup_threshold" numeric,
  "auto_topup_amount" numeric,
  "sms_provider" varchar not null default 'bulky',
  "provider_account_id" varchar,
  "provider_settings" text,
  "metadata" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE INDEX "sms_credits_balance_index" on "sms_credits"("balance");
CREATE INDEX "sms_credits_last_purchase_date_index" on "sms_credits"(
  "last_purchase_date"
);
CREATE TABLE IF NOT EXISTS "sms_credit_transactions"(
  "id" integer primary key autoincrement not null,
  "transaction_code" varchar not null,
  "transaction_type" varchar check("transaction_type" in('purchase', 'deduction', 'refund', 'adjustment', 'bonus')) not null,
  "amount" numeric not null,
  "balance_before" numeric not null,
  "balance_after" numeric not null,
  "payment_method" varchar,
  "payment_reference" varchar,
  "receipt_number" varchar,
  "amount_paid" numeric,
  "currency" varchar not null default 'KES',
  "package_name" varchar,
  "sms_quantity" integer,
  "unit_cost" numeric,
  "discount_percentage" numeric not null default '0',
  "message_id" integer,
  "message_count" integer,
  "recipient_phone" varchar,
  "message_preview" text,
  "status" varchar check("status" in('pending', 'processing', 'completed', 'failed', 'reversed')) not null default 'pending',
  "status_notes" text,
  "processed_by" integer,
  "approved_by" integer,
  "approved_at" datetime,
  "transaction_date" datetime not null default CURRENT_TIMESTAMP,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("processed_by") references "users"("id") on delete set null,
  foreign key("approved_by") references "users"("id") on delete set null
);
CREATE INDEX "sms_credit_transactions_transaction_code_index" on "sms_credit_transactions"(
  "transaction_code"
);
CREATE INDEX "sms_credit_transactions_transaction_type_status_index" on "sms_credit_transactions"(
  "transaction_type",
  "status"
);
CREATE INDEX "sms_credit_transactions_transaction_date_index" on "sms_credit_transactions"(
  "transaction_date"
);
CREATE INDEX "sms_credit_transactions_payment_reference_index" on "sms_credit_transactions"(
  "payment_reference"
);
CREATE UNIQUE INDEX "sms_credit_transactions_transaction_code_unique" on "sms_credit_transactions"(
  "transaction_code"
);
CREATE TABLE IF NOT EXISTS "sms_credit_packages"(
  "id" integer primary key autoincrement not null,
  "package_code" varchar not null,
  "package_name" varchar not null,
  "description" text,
  "sms_quantity" integer not null,
  "price" numeric not null,
  "unit_cost" numeric not null,
  "discount_percentage" numeric not null default '0',
  "original_price" numeric,
  "validity_days" integer,
  "never_expires" tinyint(1) not null default '1',
  "supports_international" tinyint(1) not null default '0',
  "includes_api_access" tinyint(1) not null default '1',
  "includes_delivery_reports" tinyint(1) not null default '1',
  "features" text,
  "is_active" tinyint(1) not null default '1',
  "is_featured" tinyint(1) not null default '0',
  "display_order" integer not null default '0',
  "minimum_quantity" integer not null default '1',
  "maximum_quantity" integer,
  "is_promotional" tinyint(1) not null default '0',
  "promotion_start_date" datetime,
  "promotion_end_date" datetime,
  "total_purchases" integer not null default '0',
  "total_revenue" numeric not null default '0',
  "metadata" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE INDEX "sms_credit_packages_package_code_index" on "sms_credit_packages"(
  "package_code"
);
CREATE INDEX "sms_credit_packages_is_active_display_order_index" on "sms_credit_packages"(
  "is_active",
  "display_order"
);
CREATE UNIQUE INDEX "sms_credit_packages_package_code_unique" on "sms_credit_packages"(
  "package_code"
);
CREATE TABLE IF NOT EXISTS "sms_usage_logs"(
  "id" integer primary key autoincrement not null,
  "message_id" varchar not null,
  "recipient_phone" varchar not null,
  "recipient_name" varchar,
  "message_content" text not null,
  "message_segments" integer not null default '1',
  "credits_used" numeric not null,
  "unit_cost" numeric not null,
  "total_cost" numeric not null,
  "status" varchar check("status" in('pending', 'sent', 'delivered', 'failed', 'expired')) not null default 'pending',
  "provider_message_id" varchar,
  "delivery_report" text,
  "sent_at" datetime,
  "delivered_at" datetime,
  "message_type" varchar,
  "message_category" varchar,
  "related_entity_id" integer,
  "related_entity_type" varchar,
  "sent_by" integer,
  "transaction_id" integer,
  "provider_response" text,
  "retry_count" integer not null default '0',
  "metadata" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("sent_by") references "users"("id") on delete set null,
  foreign key("transaction_id") references "sms_credit_transactions"("id") on delete set null
);
CREATE INDEX "sms_usage_logs_message_id_index" on "sms_usage_logs"(
  "message_id"
);
CREATE INDEX "sms_usage_logs_recipient_phone_index" on "sms_usage_logs"(
  "recipient_phone"
);
CREATE INDEX "sms_usage_logs_status_sent_at_index" on "sms_usage_logs"(
  "status",
  "sent_at"
);
CREATE INDEX "sms_usage_logs_message_type_index" on "sms_usage_logs"(
  "message_type"
);
CREATE UNIQUE INDEX "sms_usage_logs_message_id_unique" on "sms_usage_logs"(
  "message_id"
);
CREATE TABLE IF NOT EXISTS "sms_pricing_rules"(
  "id" integer primary key autoincrement not null,
  "rule_name" varchar not null,
  "description" text,
  "destination_type" varchar check("destination_type" in('local', 'international', 'premium')) not null,
  "country_code" varchar,
  "network_prefixes" text,
  "network_name" varchar,
  "cost_per_sms" numeric not null,
  "cost_per_segment" numeric not null,
  "currency" varchar not null default 'KES',
  "minimum_quantity" integer not null default '1',
  "maximum_quantity" integer,
  "discount_percentage" numeric not null default '0',
  "has_peak_pricing" tinyint(1) not null default '0',
  "peak_start_time" time,
  "peak_end_time" time,
  "peak_multiplier" numeric not null default '1',
  "is_active" tinyint(1) not null default '1',
  "priority" integer not null default '0',
  "effective_from" datetime,
  "effective_until" datetime,
  "metadata" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime
);
CREATE INDEX "sms_pricing_rules_is_active_priority_index" on "sms_pricing_rules"(
  "is_active",
  "priority"
);
CREATE INDEX "sms_pricing_rules_destination_type_index" on "sms_pricing_rules"(
  "destination_type"
);
CREATE TABLE IF NOT EXISTS "biometric_devices"(
  "id" integer primary key autoincrement not null,
  "device_code" varchar not null,
  "device_name" varchar not null,
  "device_type" varchar check("device_type" in('fingerprint', 'facial_recognition', 'rfid', 'qr_scanner', 'manual')) not null,
  "manufacturer" varchar,
  "model" varchar,
  "serial_number" varchar,
  "firmware_version" varchar,
  "ip_address" varchar,
  "port" integer,
  "mac_address" varchar,
  "connection_settings" text,
  "location" varchar not null,
  "location_type" varchar check("location_type" in('entrance', 'exit', 'classroom', 'library', 'lab', 'office', 'other')) not null default 'entrance',
  "building_id" integer,
  "status" varchar check("status" in('online', 'offline', 'maintenance', 'error')) not null default 'offline',
  "last_heartbeat" datetime,
  "last_sync" datetime,
  "sync_interval" integer not null default '300',
  "supports_entry" tinyint(1) not null default '1',
  "supports_exit" tinyint(1) not null default '1',
  "supports_enrollment" tinyint(1) not null default '1',
  "max_enrollments" integer,
  "current_enrollments" integer not null default '0',
  "settings" text,
  "verification_timeout" integer not null default '10',
  "identification_threshold" integer not null default '70',
  "require_pin" tinyint(1) not null default '0',
  "auto_mark_attendance" tinyint(1) not null default '1',
  "allowed_user_types" text,
  "active_start_time" time,
  "active_end_time" time,
  "active_days" text,
  "total_scans" integer not null default '0',
  "successful_scans" integer not null default '0',
  "failed_scans" integer not null default '0',
  "success_rate" numeric not null default '0',
  "last_successful_scan" datetime,
  "installation_date" date,
  "last_maintenance_date" date,
  "next_maintenance_date" date,
  "maintenance_notes" text,
  "is_active" tinyint(1) not null default '1',
  "notes" text,
  "managed_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("managed_by") references "users"("id")
);
CREATE INDEX "biometric_devices_device_type_status_is_active_index" on "biometric_devices"(
  "device_type",
  "status",
  "is_active"
);
CREATE INDEX "biometric_devices_location_type_is_active_index" on "biometric_devices"(
  "location_type",
  "is_active"
);
CREATE INDEX "biometric_devices_last_heartbeat_index" on "biometric_devices"(
  "last_heartbeat"
);
CREATE UNIQUE INDEX "biometric_devices_device_code_unique" on "biometric_devices"(
  "device_code"
);
CREATE UNIQUE INDEX "biometric_devices_serial_number_unique" on "biometric_devices"(
  "serial_number"
);
CREATE TABLE IF NOT EXISTS "biometric_enrollments"(
  "id" integer primary key autoincrement not null,
  "enrollable_id" integer not null,
  "enrollable_type" varchar not null,
  "enrollment_code" varchar not null,
  "biometric_type" varchar check("biometric_type" in('fingerprint', 'facial_recognition', 'iris', 'palm_vein')) not null,
  "template_id" varchar not null,
  "template_index" integer,
  "template_references" text,
  "template_quality" integer,
  "finger_position" varchar check("finger_position" in('left_thumb', 'left_index', 'left_middle', 'left_ring', 'left_pinky', 'right_thumb', 'right_index', 'right_middle', 'right_ring', 'right_pinky')),
  "is_primary_finger" tinyint(1) not null default '0',
  "facial_features" text,
  "photo_reference" varchar,
  "facial_quality_score" integer,
  "enrolled_on_device_id" integer,
  "synced_device_ids" text,
  "enrolled_at" datetime not null,
  "enrolled_by" integer not null,
  "enrollment_attempts" integer not null default '1',
  "enrollment_notes" text,
  "status" varchar check("status" in('active', 'inactive', 'expired', 'pending_verification')) not null default 'active',
  "expiry_date" date,
  "last_used" datetime,
  "usage_count" integer not null default '0',
  "successful_verifications" integer not null default '0',
  "failed_verifications" integer not null default '0',
  "verification_accuracy" numeric not null default '0',
  "requires_pin" tinyint(1) not null default '0',
  "backup_pin_hash" varchar,
  "is_locked" tinyint(1) not null default '0',
  "locked_at" datetime,
  "lock_reason" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("enrolled_on_device_id") references "biometric_devices"("id"),
  foreign key("enrolled_by") references "users"("id")
);
CREATE INDEX "biometric_enrollments_enrollable_type_enrollable_id_index" on "biometric_enrollments"(
  "enrollable_type",
  "enrollable_id"
);
CREATE INDEX "biometric_enrollments_biometric_type_status_index" on "biometric_enrollments"(
  "biometric_type",
  "status"
);
CREATE INDEX "biometric_enrollments_template_id_index" on "biometric_enrollments"(
  "template_id"
);
CREATE UNIQUE INDEX "biometric_enrollments_enrollable_type_enrollable_id_biometric_type_finger_position_unique" on "biometric_enrollments"(
  "enrollable_type",
  "enrollable_id",
  "biometric_type",
  "finger_position"
);
CREATE UNIQUE INDEX "biometric_enrollments_enrollment_code_unique" on "biometric_enrollments"(
  "enrollment_code"
);
CREATE UNIQUE INDEX "biometric_enrollments_template_id_unique" on "biometric_enrollments"(
  "template_id"
);
CREATE TABLE IF NOT EXISTS "rfid_cards"(
  "id" integer primary key autoincrement not null,
  "card_number" varchar not null,
  "card_uid" varchar not null,
  "card_type" varchar not null default 'mifare',
  "card_format" varchar not null default 'decimal',
  "holder_id" integer,
  "holder_type" varchar,
  "holder_name" varchar,
  "status" varchar check("status" in('active', 'inactive', 'lost', 'stolen', 'damaged', 'expired')) not null default 'active',
  "issue_date" date not null,
  "expiry_date" date,
  "never_expires" tinyint(1) not null default '0',
  "issued_by" integer not null,
  "issued_at" datetime not null,
  "issue_location" varchar,
  "issue_fee" numeric not null default '0',
  "fee_paid" tinyint(1) not null default '0',
  "has_photo" tinyint(1) not null default '0',
  "photo_path" varchar,
  "encoded_data" text,
  "storage_capacity" integer,
  "access_levels" text,
  "allowed_locations" text,
  "access_start_time" time,
  "access_end_time" time,
  "access_days" text,
  "activated_at" datetime,
  "deactivated_at" datetime,
  "reported_lost_at" datetime,
  "lost_notes" text,
  "last_used" datetime,
  "total_scans" integer not null default '0',
  "successful_scans" integer not null default '0',
  "failed_scans" integer not null default '0',
  "access_denied_count" integer not null default '0',
  "replaces_card_id" integer,
  "replacement_reason" text,
  "is_replacement" tinyint(1) not null default '0',
  "replacement_count" integer not null default '0',
  "card_color" varchar,
  "card_design" varchar,
  "physical_description" text,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("issued_by") references "users"("id"),
  foreign key("replaces_card_id") references "rfid_cards"("id")
);
CREATE INDEX "rfid_cards_holder_type_holder_id_index" on "rfid_cards"(
  "holder_type",
  "holder_id"
);
CREATE INDEX "rfid_cards_status_expiry_date_index" on "rfid_cards"(
  "status",
  "expiry_date"
);
CREATE INDEX "rfid_cards_card_number_index" on "rfid_cards"("card_number");
CREATE UNIQUE INDEX "rfid_cards_card_number_unique" on "rfid_cards"(
  "card_number"
);
CREATE UNIQUE INDEX "rfid_cards_card_uid_unique" on "rfid_cards"("card_uid");
CREATE TABLE IF NOT EXISTS "attendance_qr_codes"(
  "id" integer primary key autoincrement not null,
  "qr_code" varchar not null,
  "qr_type" varchar not null default 'session',
  "qr_data" text,
  "attendance_session_id" integer,
  "related_entity_id" integer,
  "related_entity_type" varchar,
  "assigned_to_id" integer,
  "assigned_to_type" varchar,
  "valid_from" datetime not null,
  "valid_until" datetime not null,
  "is_single_use" tinyint(1) not null default '0',
  "is_used" tinyint(1) not null default '0',
  "used_at" datetime,
  "allowed_device_ids" text,
  "allowed_ip_addresses" text,
  "geo_latitude" numeric,
  "geo_longitude" numeric,
  "geo_radius_meters" integer,
  "security_token" varchar not null,
  "max_scans" integer not null default '1',
  "scan_count" integer not null default '0',
  "rotation_interval" integer,
  "last_rotated" datetime,
  "size_pixels" integer not null default '300',
  "error_correction" varchar not null default 'M',
  "foreground_color" varchar not null default '#000000',
  "background_color" varchar not null default '#ffffff',
  "logo_path" varchar,
  "status" varchar check("status" in('active', 'expired', 'revoked', 'used')) not null default 'active',
  "revocation_reason" text,
  "revoked_at" datetime,
  "generated_by" integer not null,
  "generated_at" datetime not null,
  "generation_purpose" varchar,
  "successful_scans" integer not null default '0',
  "failed_scans" integer not null default '0',
  "first_scanned_at" datetime,
  "last_scanned_at" datetime,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("attendance_session_id") references "attendance_sessions"("id"),
  foreign key("generated_by") references "users"("id")
);
CREATE INDEX "attendance_qr_codes_qr_type_status_index" on "attendance_qr_codes"(
  "qr_type",
  "status"
);
CREATE INDEX "attendance_qr_codes_assigned_to_type_assigned_to_id_index" on "attendance_qr_codes"(
  "assigned_to_type",
  "assigned_to_id"
);
CREATE INDEX "attendance_qr_codes_valid_from_valid_until_index" on "attendance_qr_codes"(
  "valid_from",
  "valid_until"
);
CREATE UNIQUE INDEX "attendance_qr_codes_qr_code_unique" on "attendance_qr_codes"(
  "qr_code"
);
CREATE UNIQUE INDEX "attendance_qr_codes_security_token_unique" on "attendance_qr_codes"(
  "security_token"
);
CREATE TABLE IF NOT EXISTS "attendance_sessions"(
  "id" integer primary key autoincrement not null,
  "session_code" varchar not null,
  "session_name" varchar not null,
  "session_type" varchar check("session_type" in('class', 'exam', 'event', 'assembly', 'training', 'meeting', 'other')) not null,
  "class_id" integer,
  "subject_id" integer,
  "teacher_id" integer,
  "event_id" integer,
  "session_date" date not null,
  "start_time" time not null,
  "end_time" time not null,
  "duration_minutes" integer,
  "venue" varchar,
  "attendance_opens_at" datetime,
  "attendance_closes_at" datetime,
  "late_threshold_minutes" integer not null default '15',
  "allow_early_checkin" tinyint(1) not null default '0',
  "early_checkin_minutes" integer not null default '30',
  "allow_manual" tinyint(1) not null default '1',
  "allow_biometric" tinyint(1) not null default '1',
  "allow_rfid" tinyint(1) not null default '1',
  "allow_qr_code" tinyint(1) not null default '1',
  "allow_mobile_app" tinyint(1) not null default '1',
  "enforce_geofencing" tinyint(1) not null default '0',
  "geo_latitude" numeric,
  "geo_longitude" numeric,
  "geo_radius_meters" integer not null default '50',
  "expected_participants" integer not null default '0',
  "present_count" integer not null default '0',
  "absent_count" integer not null default '0',
  "late_count" integer not null default '0',
  "excused_count" integer not null default '0',
  "attendance_percentage" numeric not null default '0',
  "status" varchar check("status" in('scheduled', 'open', 'in_progress', 'closed', 'cancelled')) not null default 'scheduled',
  "opened_at" datetime,
  "closed_at" datetime,
  "closed_by" integer,
  "is_cancelled" tinyint(1) not null default '0',
  "cancelled_at" datetime,
  "cancelled_by" integer,
  "cancellation_reason" text,
  "qr_code_id" integer,
  "send_absence_notifications" tinyint(1) not null default '1',
  "absence_notifications_sent_at" datetime,
  "description" text,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("class_id") references "classes"("id"),
  foreign key("subject_id") references "subjects"("id"),
  foreign key("teacher_id") references "users"("id"),
  foreign key("event_id") references "events"("id"),
  foreign key("closed_by") references "users"("id"),
  foreign key("cancelled_by") references "users"("id"),
  foreign key("qr_code_id") references "attendance_qr_codes"("id")
);
CREATE INDEX "attendance_sessions_session_type_session_date_status_index" on "attendance_sessions"(
  "session_type",
  "session_date",
  "status"
);
CREATE INDEX "attendance_sessions_class_id_session_date_index" on "attendance_sessions"(
  "class_id",
  "session_date"
);
CREATE INDEX "attendance_sessions_status_session_date_index" on "attendance_sessions"(
  "status",
  "session_date"
);
CREATE UNIQUE INDEX "attendance_sessions_session_code_unique" on "attendance_sessions"(
  "session_code"
);
CREATE TABLE IF NOT EXISTS "attendance_logs"(
  "id" integer primary key autoincrement not null,
  "log_code" varchar not null,
  "attendee_id" integer not null,
  "attendee_type" varchar not null,
  "attendee_name" varchar,
  "attendance_session_id" integer,
  "check_type" varchar check("check_type" in('check_in', 'check_out')) not null,
  "attendance_method" varchar check("attendance_method" in('manual', 'fingerprint', 'facial_recognition', 'rfid', 'qr_code', 'mobile_app', 'web_portal')) not null,
  "device_id" integer,
  "rfid_card_id" integer,
  "qr_code_id" integer,
  "biometric_enrollment_id" integer,
  "check_time" datetime not null,
  "attendance_date" date,
  "attendance_time" time,
  "attendance_status" varchar check("attendance_status" in('present', 'late', 'absent', 'excused', 'half_day')) not null default 'present',
  "is_late" tinyint(1) not null default '0',
  "late_minutes" integer,
  "location" varchar,
  "latitude" numeric,
  "longitude" numeric,
  "within_geofence" tinyint(1),
  "verification_status" varchar check("verification_status" in('verified', 'unverified', 'suspicious', 'rejected')) not null default 'verified',
  "confidence_score" integer,
  "verification_notes" text,
  "device_info" varchar,
  "ip_address" varchar,
  "user_agent" varchar,
  "marked_by" integer,
  "marked_at" datetime,
  "manual_notes" text,
  "captured_photo" varchar,
  "photo_matches" tinyint(1),
  "photo_match_confidence" numeric,
  "is_valid" tinyint(1) not null default '1',
  "validation_errors" text,
  "validated_at" datetime,
  "validated_by" integer,
  "is_corrected" tinyint(1) not null default '0',
  "corrected_at" datetime,
  "corrected_by" integer,
  "correction_reason" text,
  "original_values" text,
  "is_synced" tinyint(1) not null default '1',
  "synced_at" datetime,
  "sync_source" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  "deleted_at" datetime,
  foreign key("attendance_session_id") references "attendance_sessions"("id"),
  foreign key("device_id") references "biometric_devices"("id"),
  foreign key("rfid_card_id") references "rfid_cards"("id"),
  foreign key("qr_code_id") references "attendance_qr_codes"("id"),
  foreign key("biometric_enrollment_id") references "biometric_enrollments"("id"),
  foreign key("marked_by") references "users"("id"),
  foreign key("validated_by") references "users"("id"),
  foreign key("corrected_by") references "users"("id")
);
CREATE INDEX "attendance_logs_attendee_type_attendee_id_attendance_date_index" on "attendance_logs"(
  "attendee_type",
  "attendee_id",
  "attendance_date"
);
CREATE INDEX "attendance_logs_attendance_session_id_check_type_index" on "attendance_logs"(
  "attendance_session_id",
  "check_type"
);
CREATE INDEX "attendance_logs_attendance_method_check_time_index" on "attendance_logs"(
  "attendance_method",
  "check_time"
);
CREATE INDEX "attendance_logs_attendance_date_attendance_status_index" on "attendance_logs"(
  "attendance_date",
  "attendance_status"
);
CREATE INDEX "attendance_logs_device_id_check_time_index" on "attendance_logs"(
  "device_id",
  "check_time"
);
CREATE UNIQUE INDEX "attendance_logs_log_code_unique" on "attendance_logs"(
  "log_code"
);
CREATE TABLE IF NOT EXISTS "device_sync_logs"(
  "id" integer primary key autoincrement not null,
  "sync_code" varchar not null,
  "device_id" integer not null,
  "sync_type" varchar check("sync_type" in('auto', 'manual', 'scheduled')) not null,
  "sync_direction" varchar check("sync_direction" in('upload', 'download', 'bidirectional')) not null,
  "sync_started_at" datetime not null,
  "sync_completed_at" datetime,
  "duration_seconds" integer,
  "records_sent" integer not null default '0',
  "records_received" integer not null default '0',
  "enrollments_synced" integer not null default '0',
  "attendance_logs_synced" integer not null default '0',
  "settings_synced" integer not null default '0',
  "status" varchar check("status" in('in_progress', 'completed', 'partial', 'failed')) not null default 'in_progress',
  "error_message" text,
  "error_details" text,
  "device_ip" varchar,
  "connection_quality" integer,
  "data_size_kb" integer,
  "initiated_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("device_id") references "biometric_devices"("id"),
  foreign key("initiated_by") references "users"("id")
);
CREATE INDEX "device_sync_logs_device_id_sync_started_at_index" on "device_sync_logs"(
  "device_id",
  "sync_started_at"
);
CREATE INDEX "device_sync_logs_status_sync_completed_at_index" on "device_sync_logs"(
  "status",
  "sync_completed_at"
);
CREATE UNIQUE INDEX "device_sync_logs_sync_code_unique" on "device_sync_logs"(
  "sync_code"
);

INSERT INTO migrations VALUES(1,'0001_01_01_000000_create_users_table',1);
INSERT INTO migrations VALUES(2,'0001_01_01_000001_create_cache_table',1);
INSERT INTO migrations VALUES(3,'0001_01_01_000002_create_jobs_table',1);
INSERT INTO migrations VALUES(4,'2024_12_21_001_create_rooms_table',1);
INSERT INTO migrations VALUES(5,'2024_12_21_002_create_timetable_periods_table',1);
INSERT INTO migrations VALUES(6,'2024_12_21_003_create_timetables_table',1);
INSERT INTO migrations VALUES(7,'2024_12_21_004_create_timetable_sessions_table',1);
INSERT INTO migrations VALUES(8,'2024_12_26_create_site_settings_table',1);
INSERT INTO migrations VALUES(9,'2025_12_10_151347_create_personal_access_tokens_table',1);
INSERT INTO migrations VALUES(10,'2025_12_10_151447_create_permission_tables',1);
INSERT INTO migrations VALUES(11,'2025_12_14_000001_add_fields_to_users_table',1);
INSERT INTO migrations VALUES(12,'2025_12_14_000002_create_audit_logs_table',1);
INSERT INTO migrations VALUES(13,'2025_12_14_000003_create_academic_years_table',1);
INSERT INTO migrations VALUES(14,'2025_12_14_000004_create_terms_table',1);
INSERT INTO migrations VALUES(15,'2025_12_14_000005_create_classes_table',1);
INSERT INTO migrations VALUES(16,'2025_12_14_000006_create_streams_table',1);
INSERT INTO migrations VALUES(17,'2025_12_14_000007_create_subjects_table',1);
INSERT INTO migrations VALUES(18,'2025_12_14_000008_create_students_table',1);
INSERT INTO migrations VALUES(19,'2025_12_14_000009_create_parents_table',1);
INSERT INTO migrations VALUES(20,'2025_12_14_000010_create_student_parent_table',1);
INSERT INTO migrations VALUES(21,'2025_12_14_000011_create_staff_table',1);
INSERT INTO migrations VALUES(22,'2025_12_14_000012_create_class_subject_table',1);
INSERT INTO migrations VALUES(23,'2025_12_14_000013_create_teacher_subject_assignments_table',1);
INSERT INTO migrations VALUES(24,'2025_12_14_000014_create_attendances_table',1);
INSERT INTO migrations VALUES(25,'2025_12_14_000015_alter_classes_level_to_string',1);
INSERT INTO migrations VALUES(26,'2025_12_14_000015_create_fee_structures_table',1);
INSERT INTO migrations VALUES(27,'2025_12_14_000016_create_fee_invoices_table',1);
INSERT INTO migrations VALUES(28,'2025_12_14_000017_create_fee_invoice_items_table',1);
INSERT INTO migrations VALUES(29,'2025_12_14_000018_create_fee_payments_table',1);
INSERT INTO migrations VALUES(30,'2025_12_17_000001_add_pass_mark_to_subjects_table',1);
INSERT INTO migrations VALUES(31,'2025_12_17_113713_create_student_documents_table',1);
INSERT INTO migrations VALUES(32,'2025_12_21_000001_create_assessment_types_table',1);
INSERT INTO migrations VALUES(33,'2025_12_21_000002_create_examinations_table',1);
INSERT INTO migrations VALUES(34,'2025_12_21_000003_create_marks_table',1);
INSERT INTO migrations VALUES(35,'2025_12_21_000004_create_grading_scales_table',1);
INSERT INTO migrations VALUES(36,'2025_12_21_000005_create_student_results_table',1);
INSERT INTO migrations VALUES(37,'2025_12_21_000006_create_report_cards_table',1);
INSERT INTO migrations VALUES(38,'2025_12_21_000007_create_analytics_tables',1);
INSERT INTO migrations VALUES(39,'2025_12_21_113558_update_grading_scales_add_scale_type_and_settings',1);
INSERT INTO migrations VALUES(40,'2025_12_22_000001_create_exam_seating_arrangements_table',1);
INSERT INTO migrations VALUES(41,'2025_12_29_000001_create_payment_gateways_table',1);
INSERT INTO migrations VALUES(42,'2025_12_29_000002_create_payment_transactions_table',1);
INSERT INTO migrations VALUES(43,'2025_12_29_000003_create_fee_arrears_table',1);
INSERT INTO migrations VALUES(44,'2025_12_29_000004_add_optional_fees_to_fee_structures',1);
INSERT INTO migrations VALUES(45,'2025_12_29_000005_create_announcements_table',1);
INSERT INTO migrations VALUES(46,'2025_12_29_000006_create_messages_table',1);
INSERT INTO migrations VALUES(47,'2025_12_29_000007_create_sms_logs_table',1);
INSERT INTO migrations VALUES(48,'2025_12_29_000008_create_email_logs_table',1);
INSERT INTO migrations VALUES(49,'2025_12_29_000009_create_push_subscriptions_table',1);
INSERT INTO migrations VALUES(50,'2025_12_29_000010_create_notifications_table',1);
INSERT INTO migrations VALUES(52,'2025_12_29_000011_create_budgets_table',2);
INSERT INTO migrations VALUES(53,'2026_01_02_000001_create_expenditures_table',3);
INSERT INTO migrations VALUES(54,'2026_01_02_000002_create_bank_accounts_table',3);
INSERT INTO migrations VALUES(55,'2026_01_02_000003_create_bank_transactions_table',3);
INSERT INTO migrations VALUES(56,'2026_01_02_000004_create_fee_refunds_table',3);
INSERT INTO migrations VALUES(57,'2026_01_02_000005_create_fee_waivers_table',3);
INSERT INTO migrations VALUES(58,'2026_01_02_000006_create_payment_plans_table',3);
INSERT INTO migrations VALUES(59,'2026_01_02_000007_create_approval_workflows_table',3);
INSERT INTO migrations VALUES(60,'2026_01_02_143044_add_currency_settings_to_site_settings',4);
INSERT INTO migrations VALUES(61,'2026_01_05_145852_create_dormitories_table',5);
INSERT INTO migrations VALUES(62,'2026_01_05_145858_create_beds_table',5);
INSERT INTO migrations VALUES(63,'2026_01_05_145858_create_boarding_students_table',5);
INSERT INTO migrations VALUES(64,'2026_01_05_145859_create_boarding_leave_requests_table',5);
INSERT INTO migrations VALUES(65,'2026_01_05_145900_create_boarding_health_records_table',5);
INSERT INTO migrations VALUES(66,'2026_01_05_145900_create_night_attendance_table',5);
INSERT INTO migrations VALUES(67,'2026_01_05_145906_create_discipline_incidents_table',5);
INSERT INTO migrations VALUES(68,'2026_01_05_145906_create_discipline_offense_categories_table',5);
INSERT INTO migrations VALUES(69,'2026_01_05_145907_create_discipline_actions_table',5);
INSERT INTO migrations VALUES(70,'2026_01_05_145912_create_library_books_table',5);
INSERT INTO migrations VALUES(71,'2026_01_05_145912_create_library_members_table',5);
INSERT INTO migrations VALUES(72,'2026_01_05_145913_create_library_transactions_table',5);
INSERT INTO migrations VALUES(73,'2026_01_05_145914_create_library_fines_table',5);
INSERT INTO migrations VALUES(74,'2026_01_05_145914_create_library_reservations_table',5);
INSERT INTO migrations VALUES(75,'2026_01_05_145919_create_assets_table',5);
INSERT INTO migrations VALUES(76,'2026_01_05_145920_create_asset_categories_table',5);
INSERT INTO migrations VALUES(77,'2026_01_05_145921_create_asset_maintenance_table',5);
INSERT INTO migrations VALUES(78,'2026_01_05_145922_create_consumable_transactions_table',5);
INSERT INTO migrations VALUES(79,'2026_01_05_145922_create_consumables_table',5);
INSERT INTO migrations VALUES(80,'2026_01_05_145927_create_vehicles_table',5);
INSERT INTO migrations VALUES(81,'2026_01_05_145928_create_transport_routes_table',5);
INSERT INTO migrations VALUES(82,'2026_01_05_145929_create_student_transport_table',5);
INSERT INTO migrations VALUES(83,'2026_01_05_145929_create_vehicle_maintenance_table',5);
INSERT INTO migrations VALUES(84,'2026_01_05_145930_create_transport_trips_table',5);
INSERT INTO migrations VALUES(85,'2026_01_06_000001_update_discipline_actions_action_type_enum',6);
INSERT INTO migrations VALUES(86,'2026_01_06_140611_create_asset_transfers_table',7);
INSERT INTO migrations VALUES(87,'2026_01_06_141239_create_asset_retirements_table',8);
INSERT INTO migrations VALUES(88,'2026_01_06_153545_modify_issued_to_column_in_consumable_transactions_table',9);
INSERT INTO migrations VALUES(89,'2026_01_07_000001_make_transport_routes_columns_nullable',10);
INSERT INTO migrations VALUES(90,'2026_01_07_152430_add_transport_permissions',11);
INSERT INTO migrations VALUES(91,'2026_01_09_000000_create_scheme_of_works_table',12);
INSERT INTO migrations VALUES(93,'2026_01_09_000001_add_room_number_to_classes_table',13);
INSERT INTO migrations VALUES(94,'2026_01_09_144753_create_parent_teacher_meetings_table',14);
INSERT INTO migrations VALUES(95,'2026_01_09_150001_create_student_health_records_table',15);
INSERT INTO migrations VALUES(96,'2026_01_09_150002_create_vaccination_records_table',15);
INSERT INTO migrations VALUES(97,'2026_01_09_150003_create_sick_bay_visits_table',15);
INSERT INTO migrations VALUES(98,'2026_01_09_150004_create_medical_insurance_records_table',15);
INSERT INTO migrations VALUES(99,'2026_01_09_150005_create_emergency_medical_procedures_table',15);
INSERT INTO migrations VALUES(100,'2026_01_09_160001_create_event_categories_table',15);
INSERT INTO migrations VALUES(101,'2026_01_09_160002_create_events_table',15);
INSERT INTO migrations VALUES(102,'2026_01_09_160003_create_event_participants_table',15);
INSERT INTO migrations VALUES(103,'2026_01_09_160004_create_activity_types_table',15);
INSERT INTO migrations VALUES(104,'2026_01_09_160005_create_activities_table',15);
INSERT INTO migrations VALUES(105,'2026_01_09_160006_create_activity_members_table',15);
INSERT INTO migrations VALUES(106,'2026_01_09_160007_create_competitions_table',15);
INSERT INTO migrations VALUES(107,'2026_01_09_160008_create_competition_results_table',15);
INSERT INTO migrations VALUES(108,'2026_01_09_000001_create_lesson_plan_templates_table',16);
INSERT INTO migrations VALUES(109,'2026_01_09_000002_create_lesson_plans_table',16);
INSERT INTO migrations VALUES(110,'2026_01_09_000003_create_teaching_resources_table',16);
INSERT INTO migrations VALUES(111,'2026_01_09_000004_create_classroom_materials_table',16);
INSERT INTO migrations VALUES(112,'2026_01_09_000005_create_assignments_table',16);
INSERT INTO migrations VALUES(113,'2026_01_09_000006_create_assignment_submissions_table',16);
INSERT INTO migrations VALUES(114,'2026_01_10_000001_create_alumni_table',17);
INSERT INTO migrations VALUES(115,'2026_01_10_000002_create_alumni_career_history_table',17);
INSERT INTO migrations VALUES(116,'2026_01_10_000003_create_alumni_education_history_table',17);
INSERT INTO migrations VALUES(117,'2026_01_10_000004_create_alumni_contacts_table',17);
INSERT INTO migrations VALUES(118,'2026_01_10_000005_create_mentorship_programs_table',17);
INSERT INTO migrations VALUES(119,'2026_01_10_000006_create_alumni_contributions_table',17);
INSERT INTO migrations VALUES(120,'2026_01_10_000010_create_student_promotions_table',17);
INSERT INTO migrations VALUES(121,'2026_01_10_000011_create_performance_appraisals_table',18);
INSERT INTO migrations VALUES(122,'2026_01_10_000013_create_government_reports_table',19);
INSERT INTO migrations VALUES(123,'2026_01_10_000014_create_sms_credits_table',20);
INSERT INTO migrations VALUES(125,'2026_01_10_000015_create_biometric_attendance_tables',21);
