DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'asca_contribution_status_enum') THEN
        CREATE TYPE "public"."asca_contribution_status_enum" AS ENUM('PENDING', 'PAID', 'LATE', 'REFUNDED');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'asca_member_status_enum') THEN
        CREATE TYPE "public"."asca_member_status_enum" AS ENUM('ACTIVE', 'DEFAULTED', 'WITHDRAWN');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'asca_status_enum') THEN
        CREATE TYPE "public"."asca_status_enum" AS ENUM('DRAFT', 'FUNDING', 'READY', 'PENDING_INVESTMENT', 'INVESTED', 'MATURED', 'CLOSED', 'CANCELLED');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'asca_type_enum') THEN
        CREATE TYPE "public"."asca_type_enum" AS ENUM('GENERAL', 'INVESTMENT');
    END IF;
END$$;

DO $$
BEGIN
    ALTER TYPE "public"."wallet_owner_type_enum" ADD VALUE 'ASCA';
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
DO $$
BEGIN
    ALTER TYPE "public"."wallet_transaction_type_enum" ADD VALUE 'ASCA_CONTRIBUTION';
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
DO $$
BEGIN
    ALTER TYPE "public"."wallet_transaction_type_enum" ADD VALUE 'ASCA_REFUND';
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
DO $$
BEGIN
    ALTER TYPE "public"."wallet_transaction_type_enum" ADD VALUE 'ASCA_INVESTMENT';
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "ascas" (
  "id" serial PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "description" text,
  "type" "asca_type_enum" DEFAULT 'GENERAL' NOT NULL,
  "status" "asca_status_enum" DEFAULT 'DRAFT' NOT NULL,
  "investment_id" integer,
  "escrow_wallet_id" integer,
  "target_amount" numeric(12, 2),
  "current_amount" numeric(12, 2) DEFAULT '0' NOT NULL,
  "constitution" jsonb DEFAULT '{}'::jsonb NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "asca_members" (
  "id" serial PRIMARY KEY NOT NULL,
  "asca_id" integer NOT NULL,
  "user_id" integer NOT NULL,
  "target_contribution" numeric(12, 2) NOT NULL,
  "contributed_amount" numeric(12, 2) DEFAULT '0' NOT NULL,
  "status" "asca_member_status_enum" DEFAULT 'ACTIVE' NOT NULL,
  "joined_at" timestamp DEFAULT now() NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "asca_contributions" (
  "id" serial PRIMARY KEY NOT NULL,
  "asca_id" integer NOT NULL,
  "member_id" integer NOT NULL,
  "amount" numeric(12, 2) NOT NULL,
  "status" "asca_contribution_status_enum" DEFAULT 'PENDING' NOT NULL,
  "paid_at" timestamp,
  "transaction_id" integer,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "asca_contributions" ADD CONSTRAINT "asca_contributions_asca_id_ascas_id_fk" FOREIGN KEY ("asca_id") REFERENCES "public"."ascas"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "asca_contributions" ADD CONSTRAINT "asca_contributions_member_id_asca_members_id_fk" FOREIGN KEY ("member_id") REFERENCES "public"."asca_members"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "asca_contributions" ADD CONSTRAINT "asca_contributions_transaction_id_wallet_transactions_id_fk" FOREIGN KEY ("transaction_id") REFERENCES "public"."wallet_transactions"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "asca_members" ADD CONSTRAINT "asca_members_asca_id_ascas_id_fk" FOREIGN KEY ("asca_id") REFERENCES "public"."ascas"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "asca_members" ADD CONSTRAINT "asca_members_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "ascas" ADD CONSTRAINT "ascas_investment_id_investment_opportunities_id_fk" FOREIGN KEY ("investment_id") REFERENCES "public"."investment_opportunities"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "ascas" ADD CONSTRAINT "ascas_escrow_wallet_id_wallets_id_fk" FOREIGN KEY ("escrow_wallet_id") REFERENCES "public"."wallets"("id") ON DELETE no action ON UPDATE no action;

CREATE INDEX "asca_contributions_member_idx" ON "asca_contributions" USING btree ("member_id");
CREATE INDEX "asca_contributions_asca_idx" ON "asca_contributions" USING btree ("asca_id");
CREATE INDEX "asca_contributions_status_idx" ON "asca_contributions" USING btree ("status");
CREATE UNIQUE INDEX "asca_members_asca_user_idx" ON "asca_members" USING btree ("asca_id","user_id");
CREATE INDEX "asca_members_user_idx" ON "asca_members" USING btree ("user_id");
CREATE INDEX "ascas_status_idx" ON "ascas" USING btree ("status");
CREATE INDEX "ascas_type_idx" ON "ascas" USING btree ("type");
CREATE INDEX "ascas_investment_idx" ON "ascas" USING btree ("investment_id");