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,
  "role" varchar not null default 'operator',
  "phone" varchar,
  "is_active" tinyint(1) not null default '1',
  "two_factor_secret" text,
  "two_factor_recovery_codes" text
);
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 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 "ticket_categories"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "price" numeric not null default '0',
  "discount_percent" integer not null default '0',
  "is_free" tinyint(1) not null default '0',
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "ticket_categories_code_unique" on "ticket_categories"(
  "code"
);
CREATE TABLE IF NOT EXISTS "membership_categories"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "code" varchar not null,
  "monthly_fee" numeric not null,
  "duration_months" integer not null default '1',
  "is_active" tinyint(1) not null default '1',
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "membership_categories_code_unique" on "membership_categories"(
  "code"
);
CREATE TABLE IF NOT EXISTS "tickets"(
  "id" integer primary key autoincrement not null,
  "ticket_number" varchar not null,
  "ticket_category_id" integer not null,
  "amount" numeric not null,
  "secure_payload" text not null,
  "barcode_value" varchar not null,
  "status" varchar not null default 'unused',
  "issued_at" datetime not null,
  "used_at" datetime,
  "cancelled_at" datetime,
  "operator_id" integer,
  "cashier_id" integer,
  "gate_name" varchar,
  "notes" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("ticket_category_id") references "ticket_categories"("id") on delete restrict,
  foreign key("operator_id") references "users"("id") on delete set null,
  foreign key("cashier_id") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "tickets_ticket_number_unique" on "tickets"(
  "ticket_number"
);
CREATE UNIQUE INDEX "tickets_barcode_value_unique" on "tickets"(
  "barcode_value"
);
CREATE TABLE IF NOT EXISTS "memberships"(
  "id" integer primary key autoincrement not null,
  "membership_number" varchar not null,
  "membership_category_id" integer not null,
  "photo_path" varchar,
  "name" varchar not null,
  "father_name" varchar not null,
  "cnic" varchar not null,
  "phone" varchar not null,
  "address" text not null,
  "email" varchar,
  "emergency_contact" varchar not null,
  "issue_date" date not null,
  "expiry_date" date not null,
  "status" varchar not null default 'active',
  "secure_payload" text not null,
  "barcode_value" varchar not null,
  "created_by" integer,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("membership_category_id") references "membership_categories"("id") on delete restrict,
  foreign key("created_by") references "users"("id") on delete set null
);
CREATE UNIQUE INDEX "memberships_membership_number_unique" on "memberships"(
  "membership_number"
);
CREATE UNIQUE INDEX "memberships_cnic_unique" on "memberships"("cnic");
CREATE UNIQUE INDEX "memberships_barcode_value_unique" on "memberships"(
  "barcode_value"
);
CREATE TABLE IF NOT EXISTS "entry_logs"(
  "id" integer primary key autoincrement not null,
  "ticket_id" integer,
  "membership_id" integer,
  "gate_name" varchar not null,
  "operator_id" integer,
  "status" varchar not null,
  "message" varchar not null,
  "entry_time" datetime not null,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("ticket_id") references "tickets"("id") on delete set null,
  foreign key("membership_id") references "memberships"("id") on delete set null,
  foreign key("operator_id") references "users"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "transactions"(
  "id" integer primary key autoincrement not null,
  "type" varchar not null,
  "reference_type" varchar not null,
  "reference_id" integer not null,
  "amount" numeric not null,
  "operator_id" integer,
  "description" varchar not null,
  "paid_at" datetime not null,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("operator_id") references "users"("id") on delete set null
);
CREATE TABLE IF NOT EXISTS "system_settings"(
  "id" integer primary key autoincrement not null,
  "key" varchar not null,
  "value" text
);
CREATE UNIQUE INDEX "system_settings_key_unique" on "system_settings"("key");
CREATE TABLE IF NOT EXISTS "audit_logs"(
  "id" integer primary key autoincrement not null,
  "user_id" integer,
  "action" varchar not null,
  "auditable_type" varchar,
  "auditable_id" integer,
  "description" text,
  "ip_address" varchar,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("user_id") references "users"("id") on delete set 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_07_03_000001_add_roles_to_users_table',1);
INSERT INTO migrations VALUES(5,'2026_07_03_000002_create_ticketing_tables',1);