-- Migration: Create declarative quotation form
-- Date: 2025-11-27
-- Purpose: Create declarative FormRenderer-based quotation form with VAT calculation

-- Insert quotation form definition
INSERT INTO forms (
  id,
  name,
  description,
  mapped_table,
  columns,
  layout_type,
  elements,
  isdeleted
) VALUES (
  9002,
  'Quotation Form',
  'Create and edit quotations with line items, formulas, and VAT calculation',
  'quotations',
  1,
  'grid',
  jsonb_build_object(
    'sections', jsonb_build_array(
    -- Section 1: Basic Quotation Information
    jsonb_build_object(
      'step', 1,
      'order', 1,
      'layout', jsonb_build_object(
        'type', 'grid',
        'columns', 'repeat(2, 1fr)',
        'classes', array['form-grid'],
        'gap', '1rem'
      ),
      'elements', jsonb_build_array(
        jsonb_build_object(
          'name', 'customer_id',
          'field_name', 'customer_id',
          'label', 'Customer',
          'type', 'select-search',
          'type_id', 6,
          'options_table', 'customers',
          'options_label_column', 'name',
          'options_value_column', 'customer_id',
          'required', true,
          'validation', jsonb_build_object('required', true)
        ),
        jsonb_build_object(
          'name', 'date',
          'field_name', 'date',
          'label', 'Quotation Date',
          'type', 'date',
          'type_id', 8,
          'default_value', 'today'
        ),
        jsonb_build_object(
          'name', 'due_date',
          'field_name', 'due_date',
          'label', 'Due Date',
          'type', 'date',
          'type_id', 8
        ),
        jsonb_build_object(
          'name', 'status',
          'field_name', 'status',
          'label', 'Status',
          'type', 'select',
          'type_id', 3,
          'options', jsonb_build_array(
            jsonb_build_object('value', 'draft', 'display_text', 'Draft'),
            jsonb_build_object('value', 'sent', 'display_text', 'Sent'),
            jsonb_build_object('value', 'accepted', 'display_text', 'Accepted'),
            jsonb_build_object('value', 'rejected', 'display_text', 'Rejected'),
            jsonb_build_object('value', 'expired', 'display_text', 'Expired'),
            jsonb_build_object('value', 'converted', 'display_text', 'Converted')
          ),
          'default_value', 'draft'
        )
      )
    ),
    
    -- Section 2: Line Items with Dynamic Table
    jsonb_build_object(
      'step', 2,
      'order', 2,
      'layout', jsonb_build_object(
        'type', 'grid',
        'columns', '1fr',
        'classes', array['form-grid']
      ),
      'elements', jsonb_build_array(
        jsonb_build_object(
          'name', 'line_items',
          'field_name', 'line_items',
          'label', 'Quotation Items',
          'type', 'dynamic-table',
          'type_id', 15,
          'columns', jsonb_build_array(
            jsonb_build_object(
              'name', 'name',
              'label', 'Name',
              'type', 'text'
            ),
            jsonb_build_object(
              'name', 'description',
              'label', 'Description',
              'type', 'text'
            ),
            jsonb_build_object(
              'name', 'unit_price',
              'label', 'Unit Price',
              'type', 'number'
            ),
            jsonb_build_object(
              'name', 'quantity',
              'label', 'Quantity',
              'type', 'number'
            ),
            jsonb_build_object(
              'name', 'total',
              'label', 'Total',
              'type', 'number',
              'formula', 'unit_price * quantity',
              'readonly', true
            )
          ),
          'rows', jsonb_build_array(),
          'aggregates', jsonb_build_array(
            jsonb_build_object(
              'field', 'total',
              'operation', 'sum',
              'label', 'Subtotal',
              'display', 'Subtotal: ${{value}}'
            )
          )
        )
      )
    ),
    
    -- Section 3: Action Buttons for Sections
    jsonb_build_object(
      'step', 3,
      'order', 3,
      'layout', jsonb_build_object(
        'type', 'flex',
        'classes', array['flex', 'gap-2', 'mt-4']
      ),
      'elements', jsonb_build_array(
        jsonb_build_object(
          'name', 'toggle_notes_btn',
          'label', 'Note',
          'type', 'button',
          'action', 'toggle-section',
          'target', 'noteSection',
          'class', 'btn btn-outline btn-info'
        ),
        jsonb_build_object(
          'name', 'toggle_payment_btn',
          'label', 'Payment Terms',
          'type', 'button',
          'action', 'toggle-section',
          'target', 'paymentSection',
          'class', 'btn btn-outline btn-success'
        )
      )
    ),
    
    -- Section 4: Collapsible Note Section
    jsonb_build_object(
      'step', 4,
      'order', 4,
      'layout', jsonb_build_object(
        'type', 'grid',
        'columns', '1fr'
      ),
      'elements', jsonb_build_array(
        jsonb_build_object(
          'type', 'section',
          'id', 'noteSection',
          'label', 'Quotation Notes',
          'collapsed', true,
          'fields', jsonb_build_array(
            jsonb_build_object(
              'name', 'notes',
              'field_name', 'notes',
              'label', 'Additional Notes',
              'type', 'textarea',
              'type_id', 10,
              'help_text', 'Enter any additional notes for this quotation'
            )
          )
        )
      )
    ),
    
    -- Section 5: Collapsible Payment Terms Section
    jsonb_build_object(
      'step', 5,
      'order', 5,
      'layout', jsonb_build_object(
        'type', 'grid',
        'columns', '1fr'
      ),
      'elements', jsonb_build_array(
        jsonb_build_object(
          'type', 'section',
          'id', 'paymentSection',
          'label', 'Payment Terms',
          'collapsed', true,
          'fields', jsonb_build_array(
            jsonb_build_object(
              'name', 'payment_terms',
              'field_name', 'payment_terms',
              'label', 'Payment Terms',
              'type', 'select',
              'type_id', 3,
              'options', jsonb_build_array(
                jsonb_build_object('value', 'net30', 'display_text', 'Net 30'),
                jsonb_build_object('value', 'net15', 'display_text', 'Net 15'),
                jsonb_build_object('value', 'net7', 'display_text', 'Net 7'),
                jsonb_build_object('value', 'due_on_receipt', 'display_text', 'Due on Receipt'),
                jsonb_build_object('value', 'custom', 'display_text', 'Custom')
              ),
              'default_value', 'net30'
            )
          )
        )
      )
    )
  ),
    'buttons', jsonb_build_array(
      jsonb_build_object(
        'label', 'Save Quotation',
        'action_type', 'submit',
        'order_index', 1
      ),
      jsonb_build_object(
        'label', 'Cancel',
        'action_type', 'close',
        'order_index', 2
      )
    ),
    'config', jsonb_build_object(
      'version', '1.0',
      'declarative', true,
      'features', array['formulas', 'aggregates', 'toggle-sections', 'vat-calculation']
    )
  ),
  false
) ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  description = EXCLUDED.description,
  elements = EXCLUDED.elements,
  updatedat = now();

-- Add comment
COMMENT ON TABLE forms IS 'Forms table now includes declarative quotation form (ID 9002) with formulas, aggregates, and VAT calculation';

-- Verify insertion
DO $$
DECLARE
  form_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO form_count FROM forms WHERE id = 9002;
  IF form_count = 0 THEN
    RAISE EXCEPTION 'Failed to insert quotation form';
  ELSE
    RAISE NOTICE 'Quotation form successfully created with ID 9002';
  END IF;
END $$;
