CREATE TABLE "poa" (
	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "poa_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
	"contract_no" varchar(100) NOT NULL,
	"original_contract_no" varchar(100) NOT NULL,
	"project_id" integer NOT NULL,
	"vendor_id" integer NOT NULL,
	"original_contract_name" varchar(255) NOT NULL,
	"original_status" varchar(50) NOT NULL,
	"delivery_terms" text,
	"delivery_date" date,
	"delivery_location" varchar(255),
	"currency" varchar(10),
	"total_amount" numeric(12, 2),
	"discount" numeric(12, 2),
	"tax" numeric(12, 2),
	"shipping_fee" numeric(12, 2),
	"net_total" numeric(12, 2),
	"change_reason" text,
	"approval_status" varchar(50) DEFAULT 'PENDING',
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "vendor_investigation_attachments" (
	"id" serial PRIMARY KEY NOT NULL,
	"investigation_id" integer NOT NULL,
	"file_name" varchar(255) NOT NULL,
	"file_path" varchar(1024) NOT NULL,
	"attachment_type" varchar(50) DEFAULT 'REPORT',
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "vendor_investigations" (
	"id" serial PRIMARY KEY NOT NULL,
	"vendor_id" integer NOT NULL,
	"status" varchar(50) DEFAULT 'PLANNED' NOT NULL,
	"scheduled_start_at" timestamp,
	"scheduled_end_at" timestamp,
	"completed_at" timestamp,
	"investigation_notes" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "poa" ADD CONSTRAINT "poa_original_contract_no_contracts_contract_no_fk" FOREIGN KEY ("original_contract_no") REFERENCES "public"."contracts"("contract_no") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "poa" ADD CONSTRAINT "poa_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "poa" ADD CONSTRAINT "poa_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_investigation_attachments" ADD CONSTRAINT "vendor_investigation_attachments_investigation_id_vendor_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."vendor_investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_investigations" ADD CONSTRAINT "vendor_investigations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."poa_detail_view" AS (select "poa"."id", "poa"."contract_no", "contracts"."project_id", "contracts"."vendor_id", "poa"."change_reason", "poa"."approval_status", "contracts"."contract_name" as "original_contract_name", "contracts"."status" as "original_status", "contracts"."start_date" as "original_start_date", "contracts"."end_date" as "original_end_date", "poa"."delivery_terms", "poa"."delivery_date", "poa"."delivery_location", "poa"."currency", "poa"."total_amount", "poa"."discount", "poa"."tax", "poa"."shipping_fee", "poa"."net_total", "poa"."created_at", "poa"."updated_at", EXISTS (
              SELECT 1 
              FROM "contract_envelopes" 
              WHERE "contract_envelopes"."contract_id" = "poa"."id"
          ) as "has_signature" from "poa" left join "contracts" on "poa"."contract_no" = "contracts"."contract_no");--> statement-breakpoint
CREATE VIEW "public"."vendor_investigations_view" AS (select "vendor_investigations"."id", "vendor_investigations"."status", "vendor_investigations"."scheduled_start_at", "vendor_investigations"."scheduled_end_at", "vendor_investigations"."completed_at", "vendor_investigations"."investigation_notes", "vendor_investigations"."created_at", "vendor_investigations"."updated_at", "vendors"."id", "vendors"."vendor_name", "vendors"."vendor_code", "vendors"."tax_id", "vendors"."status", "vendors"."country", "vendors"."email", "vendors"."phone", "vendors"."website", "vendors"."created_at", "vendors"."updated_at", (
        SELECT COALESCE(
          json_agg(json_build_object(
            'id', c.id,
            'contactName', c.contact_name,
            'contactEmail', c.contact_email,
            'contactPhone', c.contact_phone,
            'contactPosition', c.contact_position,
            'isPrimary', c.is_primary,
            'createdAt', c.created_at,
            'updatedAt', c.updated_at
          )),
          '[]'::json
        )
        FROM vendor_contacts c
        WHERE c.vendor_id = "vendors"."id"
      ) as "contacts", (
        SELECT COALESCE(
          json_agg(json_build_object(
            'id', pi.id,
            'vendorId', pi.vendor_id,
            'itemCode', pi.item_code,
            'createdAt', pi.created_at,
            'updatedAt', pi.updated_at
          )),
          '[]'::json
        )
        FROM vendor_possible_items pi
        WHERE pi.vendor_id = "vendors"."id"
      ) as "possibleItems" from "vendor_investigations" left join "vendors" on "vendor_investigations"."vendor_id" = "vendors"."id");