-- Add new optional columns to orders table
ALTER TABLE "orders" ADD COLUMN IF NOT EXISTS "orderDate" TIMESTAMP(3);
ALTER TABLE "orders" ADD COLUMN IF NOT EXISTS "startTime" TEXT;
ALTER TABLE "orders" ADD COLUMN IF NOT EXISTS "endTime" TEXT;

-- Update existing orders with default values based on existing docDt
UPDATE "orders" SET 
  "orderDate" = COALESCE("docDt", CURRENT_TIMESTAMP),
  "startTime" = '09:00',
  "endTime" = '17:00'
WHERE "orderDate" IS NULL OR "startTime" IS NULL OR "endTime" IS NULL;

-- Make docNo optional by dropping NOT NULL constraint if it exists
DO $$ 
BEGIN
    BEGIN
        ALTER TABLE "orders" ALTER COLUMN "docNo" DROP NOT NULL;
    EXCEPTION
        WHEN others THEN NULL;
    END;
END $$;