-- Enums
CREATE TYPE user_role AS ENUM ('super_admin', 'co_admin', 'branch_admin', 'staff');
CREATE TYPE order_status AS ENUM ('pending', 'completed', 'cancel', 'hold', 'moktob_amal');
CREATE TYPE payment_method AS ENUM ('cash', 'bank');
CREATE TYPE transaction_type AS ENUM ('agent_commission', 'customer_payment', 'due_collection', 'refund', 'service_charge_deduction');
CREATE TYPE invoice_log_action AS ENUM ('created', 'updated', 'deleted');
CREATE TYPE voucher_type AS ENUM ('debit', 'credit');

-- Tables
CREATE TABLE "public"."customers" (
  "id" SERIAL,
  "name" TEXT NOT NULL,
  "mobile_no" TEXT NOT NULL,
  "identity_no" TEXT NOT NULL,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  CONSTRAINT "customers_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "customers_mobile_no_unique" UNIQUE ("mobile_no")
);

CREATE TABLE "public"."branches" (
  "id" SERIAL,
  "name" TEXT NOT NULL,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  CONSTRAINT "branches_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "public"."users" (
  "id" SERIAL,
  "name" TEXT NOT NULL,
  "email" TEXT NOT NULL,
  "password" TEXT NOT NULL,
  "role" user_role NOT NULL,
  "branch_id" BIGINT,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  "email_verified_at" TIMESTAMP,
  "remember_token" VARCHAR(100),
  CONSTRAINT "users_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "users_email_key" UNIQUE ("email"),
  CONSTRAINT "users_branch_id_fkey" FOREIGN KEY ("branch_id") REFERENCES "public"."branches" ("id")
);

CREATE TABLE "public"."invoices" (
  "invoice_id" TEXT NOT NULL,
  "customer_id" BIGINT NOT NULL,
  "branch_id" BIGINT NOT NULL,
  "user_id" BIGINT NOT NULL,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  CONSTRAINT "invoices_pkey" PRIMARY KEY ("invoice_id"),
  CONSTRAINT "invoices_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "public"."customers" ("id"),
  CONSTRAINT "invoices_branch_id_fkey" FOREIGN KEY ("branch_id") REFERENCES "public"."branches" ("id"),
  CONSTRAINT "invoices_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id")
);

CREATE TABLE "public"."services" (
  "id" SERIAL,
  "name" TEXT NOT NULL,
  "description" TEXT,
  "cost" NUMERIC NOT NULL,
  "need_days" INTEGER NOT NULL,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  CONSTRAINT "services_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "public"."agents" (
  "id" SERIAL,
  "name" TEXT NOT NULL,
  "mobile_no" TEXT NOT NULL,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  CONSTRAINT "agents_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "public"."service_orders" (
  "id" SERIAL,
  "service_id" BIGINT NOT NULL,
  "customer_id" BIGINT NOT NULL,
  "agent_id" BIGINT,
  "branch_id" BIGINT NOT NULL,
  "invoice_id" TEXT NOT NULL,
  "deadline" TIMESTAMP WITH TIME ZONE,
  "total_cost" NUMERIC NOT NULL,
  "agent_commission" NUMERIC NOT NULL DEFAULT '0'::numeric,
  "agent_commission_paid" NUMERIC DEFAULT 0,
  "status" order_status DEFAULT 'pending',
  "user_id" BIGINT NOT NULL,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  CONSTRAINT "service_orders_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "service_orders_invoice_id_key" UNIQUE ("invoice_id"),
  CONSTRAINT "service_orders_service_id_fkey" FOREIGN KEY ("service_id") REFERENCES "public"."services" ("id"),
  CONSTRAINT "service_orders_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "public"."customers" ("id"),
  CONSTRAINT "service_orders_agent_id_fkey" FOREIGN KEY ("agent_id") REFERENCES "public"."agents" ("id"),
  CONSTRAINT "service_orders_branch_id_fkey" FOREIGN KEY ("branch_id") REFERENCES "public"."branches" ("id"),
  CONSTRAINT "service_orders_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id"),
  CONSTRAINT "service_orders_invoice_id_fkey" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices" ("invoice_id")
);

CREATE TABLE "public"."payments" (
  "id" SERIAL,
  "invoice_id" TEXT,
  "branch_id" BIGINT NOT NULL,
  "amount" NUMERIC NOT NULL,
  "payment_method" payment_method NOT NULL,
  "trx_id" TEXT,
  "payment_date" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "user_id" BIGINT NOT NULL,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  "agent_id" BIGINT,
  CONSTRAINT "payments_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "payments_branch_id_fkey" FOREIGN KEY ("branch_id") REFERENCES "public"."branches" ("id"),
  CONSTRAINT "payments_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id"),
  CONSTRAINT "payments_agent_id_fkey" FOREIGN KEY ("agent_id") REFERENCES "public"."agents" ("id"),
  CONSTRAINT "payments_invoice_id_fkey" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices" ("invoice_id")
);

CREATE TABLE "public"."invoice_logs" (
  "id" SERIAL,
  "invoice_id" TEXT NOT NULL,
  "user_id" BIGINT NOT NULL,
  "branch_id" BIGINT NOT NULL,
  "action" invoice_log_action NOT NULL,
  "details" TEXT,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  CONSTRAINT "invoice_logs_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "invoice_logs_invoice_id_fkey" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices" ("invoice_id"),
  CONSTRAINT "invoice_logs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id"),
  CONSTRAINT "invoice_logs_branch_id_fkey" FOREIGN KEY ("branch_id") REFERENCES "public"."branches" ("id")
);

CREATE TABLE "public"."vouchers" (
  "id" SERIAL,
  "voucher_id" TEXT NOT NULL,
  "transaction_type" transaction_type NOT NULL,
  "type" voucher_type NOT NULL,
  "invoice_id" TEXT,
  "payment_id" BIGINT,
  "amount" NUMERIC NOT NULL,
  "branch_id" BIGINT NOT NULL,
  "payment_method" payment_method,
  "trx_id" TEXT,
  "user_id" BIGINT NOT NULL,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE,
  CONSTRAINT "vouchers_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "vouchers_voucher_id_key" UNIQUE ("voucher_id"),
  CONSTRAINT "vouchers_invoice_id_fkey" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices" ("invoice_id"),
  CONSTRAINT "vouchers_payment_id_fkey" FOREIGN KEY ("payment_id") REFERENCES "public"."payments" ("id"),
  CONSTRAINT "vouchers_branch_id_fkey" FOREIGN KEY ("branch_id") REFERENCES "public"."branches" ("id"),
  CONSTRAINT "vouchers_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id")
);

CREATE TABLE "public"."failed_jobs" (
  "id" SERIAL,
  "uuid" VARCHAR(255) NOT NULL,
  "connection" TEXT NOT NULL,
  "queue" TEXT NOT NULL,
  "payload" TEXT NOT NULL,
  "exception" TEXT NOT NULL,
  "failed_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "failed_jobs_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "failed_jobs_uuid_unique" UNIQUE ("uuid")
);

CREATE TABLE "public"."sessions" (
  "id" VARCHAR(255) NOT NULL,
  "user_id" BIGINT,
  "ip_address" VARCHAR(45),
  "user_agent" TEXT,
  "payload" TEXT NOT NULL,
  "last_activity" INTEGER NOT NULL,
  CONSTRAINT "sessions_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "public"."migrations" (
  "id" SERIAL,
  "migration" VARCHAR(255) NOT NULL,
  "batch" INTEGER NOT NULL,
  CONSTRAINT "migrations_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "public"."cache" (
  "key" VARCHAR(255) NOT NULL,
  "value" TEXT NOT NULL,
  "expiration" INTEGER NOT NULL,
  CONSTRAINT "cache_pkey" PRIMARY KEY ("key")
);

CREATE TABLE "public"."cache_locks" (
  "key" VARCHAR(255) NOT NULL,
  "owner" VARCHAR(255) NOT NULL,
  "expiration" INTEGER NOT NULL,
  CONSTRAINT "cache_locks_pkey" PRIMARY KEY ("key")
);

CREATE TABLE "public"."jobs" (
  "id" SERIAL,
  "queue" VARCHAR(255) NOT NULL,
  "payload" TEXT NOT NULL,
  "attempts" SMALLINT NOT NULL,
  "reserved_at" INTEGER,
  "available_at" INTEGER NOT NULL,
  "created_at" INTEGER NOT NULL,
  CONSTRAINT "jobs_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "public"."job_batches" (
  "id" VARCHAR(255) NOT NULL,
  "name" VARCHAR(255) 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,
  CONSTRAINT "job_batches_pkey" PRIMARY KEY ("id")
);