-- Combined Migration: All SQL changes from last 2 days
-- Date: 2025-11-14
-- This file combines all SQL changes made to the repository in the last 2 days
-- Files included:
-- - migrations/20251113_add_transaction_counters_and_numbers.sql (transaction_number columns only)
-- - migrations/20251113_add_pos_id_to_transaction_tables.sql
-- - migrations/fix_products_module_search.sql
-- - sql/create_sync_hooks_tables.sql
-- - sql/migrations/20251113_make_branch_id_nullable_in_product_quantity.sql
-- Note: sql/modules.sql is excluded as it's the full table dump; changes to modules are in fix_products_module_search.sql
-- Note: transaction_counters table creation removed as it's no longer used

-- ========================================
-- From: migrations/20251113_add_transaction_counters_and_numbers.sql (transaction_number columns first)
-- ========================================

-- Add transaction_number field to sales_receipts table
ALTER TABLE sales_receipts
ADD COLUMN IF NOT EXISTS transaction_number VARCHAR(20);

-- Create index for transaction_number lookups in sales_receipts
CREATE INDEX IF NOT EXISTS idx_sales_receipts_transaction_number
    ON sales_receipts(transaction_number);

-- Add transaction_number field to lineitems_payments table
ALTER TABLE lineitems_payments
ADD COLUMN IF NOT EXISTS transaction_number VARCHAR(20);

-- Create index for transaction_number lookups in lineitems_payments
CREATE INDEX IF NOT EXISTS idx_lineitems_payments_transaction_number
    ON lineitems_payments(transaction_number);

-- ========================================
-- From: migrations/20251113_add_pos_id_to_transaction_tables.sql
-- ========================================

-- Migration: Add pos_id column to transaction tables
-- Date: 2025-11-13
-- Purpose: Add pos_id column to sales_receipts and lineitems_payments tables
--          to enable simple POS-based transaction queries and reporting

-- Add pos_id column to sales_receipts table
ALTER TABLE sales_receipts
ADD COLUMN IF NOT EXISTS pos_id VARCHAR(20);

-- Add pos_id column to lineitems_payments table
ALTER TABLE lineitems_payments
ADD COLUMN IF NOT EXISTS pos_id VARCHAR(20);

-- Create indexes for pos_id for efficient querying
CREATE INDEX IF NOT EXISTS idx_sales_receipts_pos_id ON sales_receipts(pos_id);
CREATE INDEX IF NOT EXISTS idx_lineitems_payments_pos_id ON lineitems_payments(pos_id);

-- Create composite index for pos_id + transaction_number for efficient counter queries
CREATE INDEX IF NOT EXISTS idx_sales_receipts_pos_transaction ON sales_receipts(pos_id, transaction_number);

-- Update existing records to set pos_id based on transaction_number format
-- Extract POS-01 from transaction numbers like "POS-01-20251113-0004"
UPDATE sales_receipts
SET pos_id = CASE
  WHEN transaction_number ~ '^POS-\d+-\d{8}-\d{4}$' THEN
    split_part(transaction_number, '-', 1) || '-' || split_part(transaction_number, '-', 2)
  ELSE 'POS-01'  -- Default fallback
END
WHERE transaction_number IS NOT NULL AND pos_id IS NULL;

UPDATE lineitems_payments
SET pos_id = CASE
  WHEN transaction_number ~ '^POS-\d+-\d{8}-\d{4}$' THEN
    split_part(transaction_number, '-', 1) || '-' || split_part(transaction_number, '-', 2)
  ELSE 'POS-01'  -- Default fallback
END
WHERE transaction_number IS NOT NULL AND pos_id IS NULL;

-- Add comments for documentation
COMMENT ON COLUMN sales_receipts.pos_id IS 'POS terminal identifier (e.g., POS-01, POS-02) for transaction grouping and reporting';
COMMENT ON COLUMN lineitems_payments.pos_id IS 'POS terminal identifier (e.g., POS-01, POS-02) for transaction grouping and reporting';

COMMENT ON COLUMN sales_receipts.transaction_number IS 'Sequential transaction number per terminal per day';
COMMENT ON COLUMN lineitems_payments.transaction_number IS 'Sequential transaction number per terminal per day';

-- ========================================
-- From: migrations/fix_products_module_search.sql
-- ========================================

-- Migration: Fix Products Module Search Configuration
-- Date: 2025-11-13
-- Description: Updates the products module configuration to fix search functionality
-- This migration updates the filter-select configuration and enables proper partial search

-- Update the products module configuration
UPDATE modules
SET config = '{
  "title": "Products",
  "header": [
    {
      "type": "filter-select",
      "options": {
        "source_table": "products",
        "source_column": "name",
        "display_column": "name",
        "data_column": "name",
        "data_column_id": "selected_product_id",
        "placeholder": "Search products by name...",
        "search_fields": ["name", "brand", "sku", "product_number"],
        "max_results": 25,
        "debounce_ms": 250,
        "populateMode": "immediate"
      }
    },
    {
      "type": "button",
      "label": "Add Product",
      "formName": "Product"
    },
    {
      "type": "button",
      "label": "Auto Orders",
      "formName": "Auto Order"
    },
    {
      "type": "button",
      "label": "Generate Barcode",
      "action": "products:generate-barcode"
    },
    {
      "type": "button",
      "label": "Generate QR Code",
      "action": "products:generate-qrcode"
    },
    {
      "type": "button",
      "label": "Import Products",
      "onClick": "importProducts",
      "class": "btn-outline btn-info"
    },
    {
      "type": "button",
      "label": "Export Products",
      "onClick": "exportProducts",
      "class": "btn-outline btn-success"
    }
  ],
  "body": [
    {
      "type": "tabs",
      "tabs": [
        {
          "label": "Products",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "products",
                "showGlobalSearch": true,
                "addButton": false,
                "editFormId": 111,
                "filterField": "name",
                "listenColumn": "name",
                "limit": 1000,
                "columns": [
                  {
                    "field": "name",
                    "label": "Name"
                  },
                  {
                    "field": "price",
                    "label": "Price"
                  },
                  {
                    "field": "brand",
                    "label": "Brand"
                  },
                  {
                    "field": "tax_rates",
                    "label": "Tax Rates",
                    "renderer": "json",
                    "rendererOptions": {
                      "maxInlineItems": 3
                    }
                  }
                ],
                "rowActions": [
                  {
                    "label": "Discount",
                    "formId": 150,
                    "icon": "fa-tags",
                    "class": "btn-sm btn-info"
                  }
                ]
              }
            }
          ]
        },
        {
          "label": "Auto Orders",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "auto_orders",
                "showGlobalSearch": false,
                "addButton": false,
                "editFormId": 131,
                "limit": 1000,
                "columnsToShow": [
                  "product_name",
                  "minimum_stock_qty",
                  "order_qty"
                ]
              }
            }
          ]
        }
      ]
    }
  ],
  "footer": [
    {
      "type": "button",
      "label": "Close",
      "onClick": "closeModal"
    }
  ]
}'::json
WHERE name = 'Products';

-- ========================================
-- From: sql/create_sync_hooks_tables.sql
-- ========================================

-- Migration: Create sync_hooks table
-- Date: 2025-11-11
-- Description: Creates sync_hooks table for declarative sync operations

-- Table: sync_hooks
-- Declarative hooks for sync operations (auto-aggregation, cascades, validations)
CREATE TABLE IF NOT EXISTS sync_hooks (
  hook_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name varchar(100) NOT NULL UNIQUE,
  description text,
  trigger_table varchar(100) NOT NULL,
  trigger_operations text[] NOT NULL,
  hook_type varchar(50) NOT NULL,
  priority INTEGER NOT NULL DEFAULT 100,
  config jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sync_hooks_trigger ON sync_hooks(trigger_table, is_active);
CREATE INDEX IF NOT EXISTS idx_sync_hooks_active ON sync_hooks(is_active, priority);

-- Table: sync_hook_logs
-- Audit log for sync hook executions
DROP INDEX IF EXISTS idx_sync_hook_logs_hook;
DROP INDEX IF EXISTS idx_sync_hook_logs_status;

CREATE TABLE sync_hook_logs (
  log_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  hook_id uuid NOT NULL REFERENCES sync_hooks(hook_id) ON DELETE CASCADE,
  trigger_table varchar(100) NOT NULL,
  trigger_operation varchar(50) NOT NULL,
  execution_status varchar(50) NOT NULL,
  execution_result jsonb,
  execution_time_ms numeric(10,2),
  executed_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_sync_hook_logs_hook ON sync_hook_logs(hook_id, executed_at);
CREATE INDEX idx_sync_hook_logs_status ON sync_hook_logs(execution_status, executed_at);

-- Add update trigger for sync_hooks
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_hooks_updated_at ON sync_hooks;
CREATE TRIGGER trg_sync_hooks_updated_at
    BEFORE UPDATE ON sync_hooks
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

-- ========================================
-- From: sql/migrations/20251113_make_branch_id_nullable_in_product_quantity.sql
-- ========================================

-- Migration: Make branch_id nullable in product_quantity table
-- Date: 2025-11-13
-- Purpose: Allow inventory tracking at org level without requiring branch specification
-- This fixes the issue where POS sales fail due to null branch_id constraint violation

-- Make branch_id column nullable in product_quantity table
ALTER TABLE product_quantity ALTER COLUMN branch_id DROP NOT NULL;

-- Add a comment to document the change
COMMENT ON COLUMN product_quantity.branch_id IS 'Branch ID - nullable to allow org-level inventory tracking';

-- The unique constraint uq_product_quantity_product_branch_org already handles NULL values correctly
-- as PostgreSQL treats NULL values as distinct in unique constraints

-- Add an index for queries that filter by non-null branch_id
-- (the existing idx_product_quantity_branch already handles this with a WHERE isdeleted = false clause)

-- Verify the change
DO $$
BEGIN
    -- Check if branch_id is now nullable
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = 'product_quantity'
        AND column_name = 'branch_id'
        AND is_nullable = 'YES'
    ) THEN
        RAISE NOTICE 'SUCCESS: branch_id column is now nullable in product_quantity table';
    ELSE
        RAISE EXCEPTION 'FAILED: branch_id column is still NOT NULL in product_quantity table';
    END IF;
END $$;

-- Note: This change allows the inventory trigger to work at the org level
-- when branch_id is NULL, which is needed for POS sales that don't specify a branch