CREATE TABLE "addresses" ("id" integer primary key autoincrement not null, "user_id" integer not null, "label" varchar not null default 'Home', "recipient_name" varchar not null, "phone" varchar not null, "line1" varchar not null, "line2" varchar, "landmark" varchar, "city" varchar not null, "state" varchar not null, "postal_code" varchar not null, "country" varchar not null default 'India', "is_default" tinyint(1) not null default '0', "is_verified" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade)
;

CREATE TABLE "agents" ("id" integer primary key autoincrement not null, "user_id" integer, "code" varchar not null, "business_name" varchar not null, "contact_name" varchar not null, "phone" varchar not null, "email" varchar, "commission_rate" numeric not null default '0', "status" varchar not null default 'pending', "city" varchar, "state" varchar, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete set null)
;

CREATE TABLE "cache" ("key" varchar not null, "value" text not null, "expiration" integer not null, primary key ("key"))
;

CREATE TABLE "cache_locks" ("key" varchar not null, "owner" varchar not null, "expiration" integer not null, primary key ("key"))
;

CREATE TABLE "commissions" ("id" integer primary key autoincrement not null, "agent_id" integer not null, "order_id" integer not null, "amount" numeric not null default '0', "status" varchar not null default 'pending', "settled_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("agent_id") references "agents"("id") on delete cascade, foreign key("order_id") references "orders"("id") on delete cascade)
;

CREATE TABLE "coupons" ("id" integer primary key autoincrement not null, "code" varchar not null, "description" text, "type" varchar not null default 'percent', "value" numeric not null default '0', "min_order_amount" numeric, "usage_limit" integer, "used_count" integer not null default '0', "starts_at" datetime, "expires_at" datetime, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime)
;

CREATE TABLE "documents" ("id" integer primary key autoincrement not null, "user_id" integer not null, "title" varchar not null, "document_type" varchar not null, "original_name" varchar not null, "storage_path" varchar not null, "mime_type" varchar not null, "file_size" integer not null default ('0'), "page_count" integer not null default ('1'), "is_deleted" tinyint(1) not null default ('0'), "delete_after_at" datetime, "metadata" text, "created_at" datetime, "updated_at" datetime, "document_number" varchar, "approval_status" varchar not null default 'pending', "approved_at" datetime, "approved_by" integer, "rejection_reason" text, foreign key("user_id") references users("id") on delete cascade on update no action, foreign key("approved_by") references "users"("id") on delete set null)
;

CREATE TABLE "failed_jobs" ("id" integer primary key autoincrement not null, "uuid" varchar not null, "connection" text not null, "queue" text not null, "payload" text not null, "exception" text not null, "failed_at" datetime not null default CURRENT_TIMESTAMP)
;

CREATE TABLE "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 "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 "migrations" ("id" integer primary key autoincrement not null, "migration" varchar not null, "batch" integer not null)
;

CREATE TABLE "notification_logs" ("id" integer primary key autoincrement not null, "user_id" integer, "order_id" integer, "channel" varchar not null, "recipient" varchar, "subject" varchar, "message" text, "status" varchar not null default 'queued', "sent_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete set null, foreign key("order_id") references "orders"("id") on delete set null)
;

CREATE TABLE "order_items" ("id" integer primary key autoincrement not null, "order_id" integer not null, "document_id" integer, "item_name" varchar not null, "page_size" varchar not null, "print_mode" varchar not null default 'bw', "sides" varchar not null default 'single', "copies" integer not null default '1', "binding_type" varchar not null default 'none', "lamination_type" varchar not null default 'none', "pvc_printing" tinyint(1) not null default '0', "page_count" integer not null default '1', "unit_price" numeric not null default '0', "total_price" numeric not null default '0', "options_json" text, "created_at" datetime, "updated_at" datetime, foreign key("order_id") references "orders"("id") on delete cascade, foreign key("document_id") references "documents"("id") on delete set null)
;

CREATE TABLE "orders" ("id" integer primary key autoincrement not null, "user_id" integer not null, "address_id" integer, "agent_id" integer, "coupon_id" integer, "order_number" varchar not null, "status" varchar not null default 'pending', "currency" varchar not null default 'INR', "subtotal" numeric not null default '0', "discount_amount" numeric not null default '0', "delivery_amount" numeric not null default '0', "total_amount" numeric not null default '0', "payment_status" varchar not null default 'pending', "delivery_partner" varchar, "tracking_number" varchar, "tracking_url" varchar, "notes" text, "printed_at" datetime, "shipped_at" datetime, "delivered_at" datetime, "created_at" datetime, "updated_at" datetime, "payment_provider" varchar not null default 'razorpay', "payment_method" varchar, "invoice_number" varchar, "gst_amount" numeric not null default '0', "approved_at" datetime, "dispatched_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("address_id") references "addresses"("id") on delete set null)
;

CREATE TABLE "otp_codes" ("id" integer primary key autoincrement not null, "user_id" integer not null, "channel" varchar not null, "recipient" varchar not null, "code" varchar not null, "purpose" varchar not null default 'register', "expires_at" datetime, "verified_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade)
;

CREATE TABLE "password_reset_tokens" ("email" varchar not null, "token" varchar not null, "created_at" datetime, primary key ("email"))
;

CREATE TABLE "payments" ("id" integer primary key autoincrement not null, "order_id" integer not null, "user_id" integer not null, "amount" numeric not null, "currency" varchar not null default 'INR', "provider" varchar not null default 'razorpay', "provider_order_id" varchar, "provider_payment_id" varchar, "provider_signature" varchar, "status" varchar not null default 'pending', "paid_at" datetime, "payload" text, "created_at" datetime, "updated_at" datetime, foreign key("order_id") references "orders"("id") on delete cascade, foreign key("user_id") references "users"("id") on delete cascade)
;

CREATE TABLE "roles" ("id" integer primary key autoincrement not null, "name" varchar not null, "slug" varchar not null, "description" text, "created_at" datetime, "updated_at" datetime)
;

CREATE TABLE "sessions" ("id" varchar not null, "user_id" integer, "ip_address" varchar, "user_agent" text, "payload" text not null, "last_activity" integer not null, primary key ("id"))
;

CREATE TABLE "settings" ("id" integer primary key autoincrement not null, "group_key" varchar not null default 'general', "key" varchar not null, "value" text, "type" varchar not null default 'string', "label" varchar, "is_public" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime)
;

CREATE TABLE "tracking_updates" ("id" integer primary key autoincrement not null, "order_id" integer not null, "status" varchar not null, "title" varchar not null, "description" text, "location" varchar, "created_by" integer, "created_at" datetime, "updated_at" datetime, foreign key("order_id") references "orders"("id") on delete cascade, foreign key("created_by") references "users"("id") on delete set null)
;

CREATE TABLE "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, "mobile" varchar, "status" varchar not null default 'active', "wallet_balance" numeric not null default '0', "referral_code" varchar, "referred_by" integer, foreign key("role_id") references "roles"("id") on delete set null, foreign key("referred_by") references "users"("id") on delete set null)
;