PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "migrations" ("id" integer primary key autoincrement not null, "migration" varchar not null, "batch" integer not null);
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_03_21_142937_create_cbt_classes_table',1);
INSERT INTO migrations VALUES(5,'2026_03_21_142939_create_question_sets_table',1);
INSERT INTO migrations VALUES(6,'2026_03_21_142940_create_questions_table',1);
INSERT INTO migrations VALUES(7,'2026_03_21_142942_create_results_table',1);
INSERT INTO migrations VALUES(8,'2026_03_21_144817_create_test_sessions_table',1);
INSERT INTO migrations VALUES(9,'2026_03_21_144819_create_student_answers_table',1);
INSERT INTO migrations VALUES(10,'2026_03_21_145304_add_grading_to_results_table',1);
INSERT INTO migrations VALUES(11,'2026_03_21_145424_create_settings_table',1);
INSERT INTO migrations VALUES(12,'2026_03_21_150142_create_plans_table',1);
INSERT INTO migrations VALUES(13,'2026_03_21_150144_create_subscriptions_table',1);
INSERT INTO migrations VALUES(14,'2026_03_21_150146_create_payments_table',1);
INSERT INTO migrations VALUES(15,'2026_03_21_150147_add_credits_to_users_table',1);
INSERT INTO migrations VALUES(16,'2026_03_21_160000_create_file_buckets_table',2);
INSERT INTO migrations VALUES(17,'2026_03_21_160001_create_files_table',2);
INSERT INTO migrations VALUES(18,'2026_03_21_160002_create_upload_sessions_table',2);
INSERT INTO migrations VALUES(19,'2026_03_21_160003_create_file_chunks_table',2);
INSERT INTO migrations VALUES(20,'2026_03_21_160004_create_file_permissions_table',2);
INSERT INTO migrations VALUES(21,'2026_03_21_160005_create_storage_usage_stats_table',2);
INSERT INTO migrations VALUES(22,'2026_03_21_160006_create_signed_urls_table',2);
INSERT INTO migrations VALUES(23,'2026_03_21_171358_create_personal_access_tokens_table',2);
INSERT INTO migrations VALUES(24,'2026_03_21_170000_add_payment_fields_to_payments_table',3);
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, "role" varchar not null default 'user', "parent_name" varchar, "parent_email" varchar, "remember_token" varchar, "created_at" datetime, "updated_at" datetime, "credits" integer not null default '0');
INSERT INTO users VALUES(1,'Super Admin','admin@school.com',NULL,'$2y$12$o6WhDy/IoZcmm37xbjuuoeoHI/GlRTy966C.UNPkppjgS3kMmZzeC','super_admin','System','system@school.com',NULL,'2026-03-21 15:03:30','2026-03-21 15:03:30',0);
INSERT INTO users VALUES(2,'Student One','student@example.com',NULL,'$2y$12$U9luzBvrEKUH/ekxTUg9UumUYYTKxu9DwB5oPfQ0aNDZ15J8OzL.C','user','Parent One','parent@example.com',NULL,'2026-03-21 15:03:32','2026-03-21 15:03:32',0);
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 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 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 TABLE IF NOT EXISTS "cbt_classes" ("id" integer primary key autoincrement not null, "name" varchar not null, "description" text, "created_at" datetime, "updated_at" datetime);
INSERT INTO cbt_classes VALUES(1,'Mathematics 101','Introductory mathematics course covering basic algebra and geometry.','2026-03-21 15:03:32','2026-03-21 15:03:32');
CREATE TABLE IF NOT EXISTS "question_sets" ("id" integer primary key autoincrement not null, "cbt_class_id" integer not null, "title" varchar not null, "description" text, "created_at" datetime, "updated_at" datetime, foreign key("cbt_class_id") references "cbt_classes"("id") on delete cascade);
INSERT INTO question_sets VALUES(1,1,'Mid-term Quiz','A quiz covering the first 5 chapters of the course.','2026-03-21 15:03:32','2026-03-21 15:03:32');
CREATE TABLE IF NOT EXISTS "questions" ("id" integer primary key autoincrement not null, "question_set_id" integer not null, "question_text" text not null, "option_a" varchar not null, "option_b" varchar not null, "option_c" varchar not null, "option_d" varchar not null, "correct_answer" varchar not null, "created_at" datetime, "updated_at" datetime, foreign key("question_set_id") references "question_sets"("id") on delete cascade);
INSERT INTO questions VALUES(1,1,'What is 2 + 2?','3','4','5','6','B','2026-03-21 15:03:32','2026-03-21 15:03:32');
INSERT INTO questions VALUES(2,1,'Solve for x: 2x = 10','2','5','10','20','B','2026-03-21 15:03:32','2026-03-21 15:03:32');
INSERT INTO questions VALUES(3,1,'Which shape has 3 sides?','Square','Circle','Triangle','Rectangle','C','2026-03-21 15:03:32','2026-03-21 15:03:32');
INSERT INTO questions VALUES(4,1,'What is the square root of 81?','7','8','9','10','C','2026-03-21 15:03:32','2026-03-21 15:03:32');
INSERT INTO questions VALUES(5,1,'What is 15 x 3?','30','40','45','50','C','2026-03-21 15:03:32','2026-03-21 15:03:32');
CREATE TABLE IF NOT EXISTS "results" ("id" integer primary key autoincrement not null, "user_id" integer not null, "score" integer not null, "total_questions" integer not null, "created_at" datetime, "updated_at" datetime, "correct_answers" integer not null default '0', "wrong_answers" integer not null default '0', "percentage" numeric not null default '0', "grade" varchar, foreign key("user_id") references "users"("id") on delete cascade);
CREATE TABLE IF NOT EXISTS "test_sessions" ("id" integer primary key autoincrement not null, "user_id" integer not null, "question_set_id" integer not null, "current_question_index" integer not null default '0', "score" integer not null default '0', "completed" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("question_set_id") references "question_sets"("id") on delete cascade);
CREATE TABLE IF NOT EXISTS "student_answers" ("id" integer primary key autoincrement not null, "test_session_id" integer not null, "question_id" integer not null, "selected_answer" varchar not null, "is_correct" tinyint(1) not null, "created_at" datetime, "updated_at" datetime, foreign key("test_session_id") references "test_sessions"("id") on delete cascade, foreign key("question_id") references "questions"("id") on delete cascade);
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);
INSERT INTO settings VALUES(1,'stripe_enabled','1','2026-03-21 17:54:54','2026-03-21 17:54:54');
INSERT INTO settings VALUES(2,'paypal_enabled','1','2026-03-21 17:54:54','2026-03-21 17:54:54');
INSERT INTO settings VALUES(3,'stripe_webhook_secret','','2026-03-21 17:54:54','2026-03-21 17:54:54');
INSERT INTO settings VALUES(4,'paypal_client_id','','2026-03-21 17:54:54','2026-03-21 17:54:54');
INSERT INTO settings VALUES(5,'paypal_client_secret','','2026-03-21 17:54:54','2026-03-21 17:54:54');
INSERT INTO settings VALUES(6,'paypal_sandbox_mode','1','2026-03-21 17:54:54','2026-03-21 17:54:54');
INSERT INTO settings VALUES(7,'payment_confirmation_email','1','2026-03-21 17:54:54','2026-03-21 17:54:54');
CREATE TABLE IF NOT EXISTS "plans" ("id" integer primary key autoincrement not null, "name" varchar not null, "display_name" varchar not null, "price" numeric not null, "duration_months" integer not null, "credits" integer not null, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime);
INSERT INTO plans VALUES(1,'monthly','Monthly Plan',3,1,30,1,'2026-03-21 15:03:32','2026-03-21 17:54:51');
INSERT INTO plans VALUES(2,'6_months','6 Months Plan',10,6,200,1,'2026-03-21 15:03:32','2026-03-21 17:54:51');
INSERT INTO plans VALUES(3,'yearly','Yearly Plan',20,12,500,1,'2026-03-21 15:03:32','2026-03-21 17:54:51');
CREATE TABLE IF NOT EXISTS "subscriptions" ("id" integer primary key autoincrement not null, "user_id" integer not null, "plan_id" integer not null, "plan_name" varchar not null, "price" numeric not null, "start_date" datetime not null, "end_date" datetime not null, "status" varchar not null default 'active', "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("plan_id") references "plans"("id") on delete cascade);
CREATE TABLE IF NOT EXISTS "file_buckets" ("id" integer primary key autoincrement not null, "name" varchar not null, "display_name" varchar not null, "description" text, "visibility" varchar check ("visibility" in ('public', 'private', 'mixed')) not null default 'private', "max_file_size" integer not null default '104857600', "allowed_mime_types" text, "is_active" tinyint(1) not null default '1', "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("created_by") references "users"("id") on delete set null);
INSERT INTO file_buckets VALUES(1,'question-media','Question Media','Images and media for exam questions','private',104857600,NULL,1,NULL,'2026-03-21 17:33:45','2026-03-21 17:33:45');
INSERT INTO file_buckets VALUES(2,'user-avatars','User Avatars','User profile pictures','public',104857600,NULL,1,NULL,'2026-03-21 17:33:45','2026-03-21 17:33:45');
INSERT INTO file_buckets VALUES(3,'course-materials','Course Materials','PDFs, documents, and learning resources','private',104857600,NULL,1,NULL,'2026-03-21 17:33:45','2026-03-21 17:33:45');
INSERT INTO file_buckets VALUES(4,'submissions','Student Submissions','Student uploaded assignments and files','private',104857600,NULL,1,NULL,'2026-03-21 17:33:45','2026-03-21 17:33:45');
CREATE TABLE IF NOT EXISTS "files" ("id" integer primary key autoincrement not null, "uuid" varchar not null, "bucket_id" integer not null, "uploaded_by" integer not null, "original_name" varchar not null, "stored_name" varchar not null, "path" varchar not null, "disk" varchar not null default 's3', "mime_type" varchar not null, "extension" varchar not null, "size" integer not null, "checksum" varchar not null, "visibility" varchar check ("visibility" in ('public', 'private', 'signed')) not null default 'private', "metadata" text, "thumbnail_path" varchar, "virus_scan_status" varchar check ("virus_scan_status" in ('pending', 'clean', 'infected', 'skipped')) not null default 'pending', "virus_scanned_at" datetime, "expires_at" datetime, "created_at" datetime, "updated_at" datetime, "deleted_at" datetime, foreign key("bucket_id") references "file_buckets"("id") on delete cascade, foreign key("uploaded_by") references "users"("id") on delete cascade);
CREATE TABLE IF NOT EXISTS "upload_sessions" ("id" integer primary key autoincrement not null, "uuid" varchar not null, "user_id" integer not null, "bucket_id" integer not null, "original_name" varchar not null, "total_size" integer not null, "chunk_size" integer not null, "total_chunks" integer not null, "uploaded_chunks" text, "checksum" varchar, "status" varchar check ("status" in ('pending', 'uploading', 'assembling', 'completed', 'failed', 'expired')) not null default 'pending', "mime_type" varchar, "metadata" text, "expires_at" datetime not null, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("bucket_id") references "file_buckets"("id") on delete cascade);
CREATE TABLE IF NOT EXISTS "file_chunks" ("id" integer primary key autoincrement not null, "upload_session_id" integer not null, "chunk_index" integer not null, "chunk_path" varchar not null, "chunk_size" integer not null, "checksum" varchar not null, "uploaded_at" datetime, foreign key("upload_session_id") references "upload_sessions"("id") on delete cascade);
CREATE TABLE IF NOT EXISTS "file_permissions" ("id" integer primary key autoincrement not null, "file_id" integer not null, "user_id" integer, "role" varchar, "permission" varchar check ("permission" in ('view', 'download', 'manage')) not null default 'view', "expires_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("file_id") references "files"("id") on delete cascade, foreign key("user_id") references "users"("id") on delete cascade);
CREATE TABLE IF NOT EXISTS "storage_usage_stats" ("id" integer primary key autoincrement not null, "user_id" integer, "bucket_id" integer, "date" date not null, "storage_bytes" integer not null default '0', "upload_count" integer not null default '0', "upload_bytes" integer not null default '0', "download_count" integer not null default '0', "download_bytes" integer not null default '0', "bandwidth_bytes" integer not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("bucket_id") references "file_buckets"("id") on delete cascade);
CREATE TABLE IF NOT EXISTS "signed_urls" ("id" integer primary key autoincrement not null, "file_id" integer not null, "token" varchar not null, "created_by" integer not null, "ip_address" varchar, "expires_at" datetime not null, "access_count" integer not null default '0', "max_access_count" integer, "created_at" datetime not null default CURRENT_TIMESTAMP, foreign key("file_id") references "files"("id") on delete cascade, foreign key("created_by") references "users"("id") on delete cascade);
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 TABLE IF NOT EXISTS "payments" ("id" integer primary key autoincrement not null, "user_id" integer not null, "amount" numeric not null, "payment_method" varchar not null, "transaction_id" varchar not null, "status" varchar not null, "created_at" datetime, "updated_at" datetime, "subscription_id" integer, "plan_id" integer, "currency" varchar not null default 'USD', "gateway_payment_id" varchar, "gateway_customer_id" varchar, "metadata" text, "paid_at" datetime, foreign key("user_id") references users("id") on delete cascade on update no action, foreign key("subscription_id") references "subscriptions"("id") on delete set null, foreign key("plan_id") references "plans"("id") on delete set null);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('migrations',24);
INSERT INTO sqlite_sequence VALUES('users',2);
INSERT INTO sqlite_sequence VALUES('cbt_classes',1);
INSERT INTO sqlite_sequence VALUES('question_sets',1);
INSERT INTO sqlite_sequence VALUES('questions',5);
INSERT INTO sqlite_sequence VALUES('plans',3);
INSERT INTO sqlite_sequence VALUES('file_buckets',4);
INSERT INTO sqlite_sequence VALUES('payments',0);
INSERT INTO sqlite_sequence VALUES('settings',7);
CREATE UNIQUE INDEX "users_email_unique" on "users" ("email");
CREATE INDEX "sessions_user_id_index" on "sessions" ("user_id");
CREATE INDEX "sessions_last_activity_index" on "sessions" ("last_activity");
CREATE INDEX "cache_expiration_index" on "cache" ("expiration");
CREATE INDEX "cache_locks_expiration_index" on "cache_locks" ("expiration");
CREATE INDEX "jobs_queue_index" on "jobs" ("queue");
CREATE UNIQUE INDEX "failed_jobs_uuid_unique" on "failed_jobs" ("uuid");
CREATE UNIQUE INDEX "settings_key_unique" on "settings" ("key");
CREATE INDEX "file_buckets_name_index" on "file_buckets" ("name");
CREATE INDEX "file_buckets_is_active_index" on "file_buckets" ("is_active");
CREATE UNIQUE INDEX "file_buckets_name_unique" on "file_buckets" ("name");
CREATE INDEX "files_uuid_index" on "files" ("uuid");
CREATE INDEX "files_bucket_id_index" on "files" ("bucket_id");
CREATE INDEX "files_uploaded_by_index" on "files" ("uploaded_by");
CREATE INDEX "files_mime_type_index" on "files" ("mime_type");
CREATE INDEX "files_virus_scan_status_index" on "files" ("virus_scan_status");
CREATE INDEX "files_expires_at_index" on "files" ("expires_at");
CREATE UNIQUE INDEX "files_uuid_unique" on "files" ("uuid");
CREATE INDEX "upload_sessions_uuid_index" on "upload_sessions" ("uuid");
CREATE INDEX "upload_sessions_user_id_index" on "upload_sessions" ("user_id");
CREATE INDEX "upload_sessions_bucket_id_index" on "upload_sessions" ("bucket_id");
CREATE INDEX "upload_sessions_status_index" on "upload_sessions" ("status");
CREATE INDEX "upload_sessions_expires_at_index" on "upload_sessions" ("expires_at");
CREATE UNIQUE INDEX "upload_sessions_uuid_unique" on "upload_sessions" ("uuid");
CREATE UNIQUE INDEX "file_chunks_upload_session_id_chunk_index_unique" on "file_chunks" ("upload_session_id", "chunk_index");
CREATE INDEX "file_chunks_upload_session_id_index" on "file_chunks" ("upload_session_id");
CREATE INDEX "file_permissions_file_id_index" on "file_permissions" ("file_id");
CREATE INDEX "file_permissions_user_id_index" on "file_permissions" ("user_id");
CREATE INDEX "file_permissions_role_index" on "file_permissions" ("role");
CREATE UNIQUE INDEX "storage_usage_stats_user_id_bucket_id_date_unique" on "storage_usage_stats" ("user_id", "bucket_id", "date");
CREATE INDEX "storage_usage_stats_user_id_index" on "storage_usage_stats" ("user_id");
CREATE INDEX "storage_usage_stats_bucket_id_index" on "storage_usage_stats" ("bucket_id");
CREATE INDEX "storage_usage_stats_date_index" on "storage_usage_stats" ("date");
CREATE INDEX "signed_urls_token_index" on "signed_urls" ("token");
CREATE INDEX "signed_urls_file_id_index" on "signed_urls" ("file_id");
CREATE INDEX "signed_urls_expires_at_index" on "signed_urls" ("expires_at");
CREATE UNIQUE INDEX "signed_urls_token_unique" on "signed_urls" ("token");
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 UNIQUE INDEX "payments_transaction_id_unique" on "payments" ("transaction_id");
CREATE INDEX "payments_gateway_payment_id_index" on "payments" ("gateway_payment_id");
CREATE INDEX "payments_status_index" on "payments" ("status");
COMMIT;
