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 "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 INDEX "cache_expiration_index" on "cache"("expiration");
CREATE TABLE IF NOT EXISTS "cache_locks"(
  "key" varchar not null,
  "owner" varchar not null,
  "expiration" integer not null,
  primary key("key")
);
CREATE INDEX "cache_locks_expiration_index" on "cache_locks"("expiration");
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" varchar not null,
  "queue" varchar not null,
  "payload" text not null,
  "exception" text not null,
  "failed_at" datetime not null default CURRENT_TIMESTAMP
);
CREATE INDEX "failed_jobs_connection_queue_failed_at_index" on "failed_jobs"(
  "connection",
  "queue",
  "failed_at"
);
CREATE UNIQUE INDEX "failed_jobs_uuid_unique" on "failed_jobs"("uuid");
CREATE TABLE IF NOT EXISTS "boards"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "boards_name_unique" on "boards"("name");
CREATE TABLE IF NOT EXISTS "classes"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "level" integer,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "classes_name_unique" on "classes"("name");
CREATE TABLE IF NOT EXISTS "attendance"(
  "id" integer primary key autoincrement not null,
  "person_type" varchar not null,
  "person_id" integer not null,
  "date" date not null,
  "status" varchar not null,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "attendance_person_type_person_id_date_unique" on "attendance"(
  "person_type",
  "person_id",
  "date"
);
CREATE INDEX "attendance_date_index" on "attendance"("date");
CREATE INDEX "attendance_person_type_person_id_index" on "attendance"(
  "person_type",
  "person_id"
);
CREATE TABLE IF NOT EXISTS "batches"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "description" text,
  "start_date" date,
  "end_date" date,
  "board_id" integer,
  "class_id" integer,
  "stream_id" integer,
  "subject_id" integer,
  "teacher_id" integer,
  "schedule_time" varchar,
  "academic_year" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("board_id") references "boards"("id") on delete set null,
  foreign key("class_id") references "classes"("id") on delete set null,
  foreign key("stream_id") references "streams"("id") on delete set null,
  foreign key("subject_id") references "subjects"("id") on delete set null,
  foreign key("teacher_id") references "staff"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "staff"(
  "id" integer primary key autoincrement not null,
  "full_name" varchar not null,
  "email" varchar,
  "phone" varchar,
  "role" varchar,
  "subjects" text,
  "address" text,
  "status" varchar not null default 'active',
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "streams"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "streams_name_unique" on "streams"("name");
CREATE TABLE IF NOT EXISTS "students"(
  "id" integer primary key autoincrement not null,
  "full_name" varchar not null,
  "email" varchar,
  "phone" varchar,
  "parent_name" varchar,
  "parent_phone" varchar,
  "address" text,
  "board_id" integer,
  "class_id" integer,
  "stream_id" integer,
  "batch_id" integer,
  "admission_date" date,
  "fee_total" numeric not null default '0',
  "fee_paid" numeric not null default '0',
  "status" varchar not null default 'active',
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("board_id") references "boards"("id") on delete set null,
  foreign key("class_id") references "classes"("id") on delete set null,
  foreign key("stream_id") references "streams"("id") on delete set null,
  foreign key("batch_id") references "batches"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "subjects"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "board_id" integer,
  "class_id" integer,
  "stream_id" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("board_id") references "boards"("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
);
CREATE UNIQUE INDEX "subjects_name_board_id_class_id_stream_id_unique" on "subjects"(
  "name",
  "board_id",
  "class_id",
  "stream_id"
);
CREATE TABLE IF NOT EXISTS "timetable"(
  "id" integer primary key autoincrement not null,
  "batch_id" integer not null,
  "staff_id" integer,
  "subject" varchar not null,
  "day_of_week" integer not null,
  "start_time" time not null,
  "end_time" time not null,
  "room" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("batch_id") references "batches"("id") on delete cascade,
  foreign key("staff_id") references "staff"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "inquiries"(
  "id" integer primary key autoincrement not null,
  "full_name" varchar not null,
  "email" varchar,
  "phone" varchar not null,
  "course_interest" varchar,
  "source" varchar,
  "status" varchar not null default 'new',
  "follow_up_notes" text,
  "next_follow_up" date,
  "created_at" datetime,
  "updated_at" datetime,
  "preferred_time" varchar,
  "message" text
);
CREATE TABLE IF NOT EXISTS "materials"(
  "id" integer primary key autoincrement not null,
  "title" varchar not null,
  "description" text,
  "category" varchar not null default 'note',
  "subject" varchar,
  "batch_id" integer,
  "file_path" varchar,
  "file_name" varchar,
  "created_at" datetime,
  foreign key("batch_id") references "batches"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "questions"(
  "id" integer primary key autoincrement not null,
  "subject" varchar not null,
  "topic" varchar,
  "difficulty" varchar not null default 'medium',
  "question_type" varchar not null default 'mcq',
  "question_text" text not null,
  "options" text,
  "correct_answer" text,
  "marks" integer not null default '1',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "tests"(
  "id" integer primary key autoincrement not null,
  "title" varchar not null,
  "subject" varchar,
  "instructions" text,
  "duration_minutes" integer,
  "total_marks" integer,
  "question_ids" text,
  "created_at" datetime
);
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,
  "role_id" integer,
  foreign key("role_id") references "roles"("id") on delete set null
);
CREATE UNIQUE INDEX "users_email_unique" on "users"("email");
CREATE TABLE IF NOT EXISTS "roles"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "guard_name" varchar not null default 'web',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "roles_name_unique" on "roles"("name");
CREATE TABLE IF NOT EXISTS "settings"(
  "id" integer primary key autoincrement not null,
  "key" varchar not null,
  "value" text,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "settings_key_unique" on "settings"("key");
CREATE TABLE IF NOT EXISTS "syllabus_topics"(
  "id" integer primary key autoincrement not null,
  "batch_id" integer not null,
  "subject_id" integer,
  "title" varchar not null,
  "description" text,
  "planned_date" date,
  "completed_date" date,
  "status" varchar check("status" in('pending', 'completed', 'cancelled')) not null default 'pending',
  "hours_spent" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("batch_id") references "batches"("id") on delete cascade,
  foreign key("subject_id") references "subjects"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "homework"(
  "id" integer primary key autoincrement not null,
  "batch_id" integer not null,
  "subject" varchar not null,
  "description" text not null,
  "assigned_date" date,
  "due_date" date,
  "status" varchar not null default 'pending',
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("batch_id") references "batches"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "communication_logs"(
  "id" integer primary key autoincrement not null,
  "person_type" varchar not null,
  "person_id" integer not null,
  "type" varchar not null default 'phone',
  "notes" text not null,
  "direction" varchar not null default 'outgoing',
  "contacted_at" datetime not null default CURRENT_TIMESTAMP,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "holidays"(
  "id" integer primary key autoincrement not null,
  "title" varchar not null,
  "date" date not null,
  "description" text,
  "repeat_yearly" tinyint(1) not null default '0',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE TABLE IF NOT EXISTS "notes"(
  "id" integer primary key autoincrement not null,
  "content" text not null,
  "color" varchar not null default '#FEF3C7',
  "pinned" tinyint(1) not null default '0',
  "created_at" datetime,
  "updated_at" datetime
);

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,'2026_06_21_091934_create_boards_table',1);
INSERT INTO migrations VALUES(5,'2026_06_21_091934_create_classes_table',1);
INSERT INTO migrations VALUES(6,'2026_06_21_091935_create_attendance_table',1);
INSERT INTO migrations VALUES(7,'2026_06_21_091935_create_batches_table',1);
INSERT INTO migrations VALUES(8,'2026_06_21_091935_create_staff_table',1);
INSERT INTO migrations VALUES(9,'2026_06_21_091935_create_streams_table',1);
INSERT INTO migrations VALUES(10,'2026_06_21_091935_create_students_table',1);
INSERT INTO migrations VALUES(11,'2026_06_21_091935_create_subjects_table',1);
INSERT INTO migrations VALUES(12,'2026_06_21_091935_create_timetable_table',1);
INSERT INTO migrations VALUES(13,'2026_06_21_091936_create_inquiries_table',1);
INSERT INTO migrations VALUES(14,'2026_06_21_091936_create_materials_table',1);
INSERT INTO migrations VALUES(15,'2026_06_21_091936_create_questions_table',1);
INSERT INTO migrations VALUES(16,'2026_06_21_091936_create_tests_table',1);
INSERT INTO migrations VALUES(17,'2026_06_21_091937_add_role_to_users_table',2);
INSERT INTO migrations VALUES(18,'2026_06_21_091937_create_roles_table',2);
INSERT INTO migrations VALUES(19,'2026_06_21_091938_add_source_to_inquiries',3);
INSERT INTO migrations VALUES(20,'2026_06_21_091939_create_settings_table',3);
INSERT INTO migrations VALUES(21,'2026_06_22_091940_create_syllabus_topics_table',4);
INSERT INTO migrations VALUES(22,'2026_06_22_091941_create_homework_table',4);
INSERT INTO migrations VALUES(23,'2026_06_22_091942_create_communication_logs_table',4);
INSERT INTO migrations VALUES(24,'2026_06_22_091943_create_holidays_table',4);
INSERT INTO migrations VALUES(25,'2026_06_22_091944_create_notes_table',4);
INSERT INTO migrations VALUES(26,'2026_06_22_091945_add_batch_id_to_students',5);