-- ============================================================================
-- Migration: Update line_items table to use org_id (UUID) and add product_id reference
-- Date: 2025-10-18
-- Purpose: Align line_items table with modern TAF schema (org_id UUID pattern)
--          and add proper product reference for revenue tracking
-- ============================================================================

BEGIN;

-- Step 1: Add new org_id column (nullable initially)
ALTER TABLE line_items 
ADD COLUMN IF NOT EXISTS org_id UUID;

-- Step 2: Add product_id column (nullable - for service-based line items)
ALTER TABLE line_items
ADD COLUMN IF NOT EXISTS product_id UUID;

-- Step 3: Migrate data from tenant_id to org_id
-- This assumes there's a mapping or that tenant_id can be used to look up org_id
-- If there's a direct tenant -> org mapping table, use it. Otherwise, this will set NULL.
-- UPDATE line_items li
-- SET org_id = (SELECT org_id FROM orgs WHERE orgs.legacy_tenant_id = li.tenant_id LIMIT 1);

-- For now, we'll just set it to NULL and let the application handle it
-- Or if there's only one org, you could set it to that org's ID:
-- UPDATE line_items SET org_id = (SELECT org_id FROM orgs LIMIT 1);

-- Step 4: Try to match product_id by name (if products exist)
-- This is optional and depends on your data structure
-- UPDATE line_items li
-- SET product_id = (
--     SELECT p.product_id 
--     FROM products p 
--     WHERE p.name = li.name 
--     AND p.org_id = li.org_id 
--     LIMIT 1
-- )
-- WHERE li.name IS NOT NULL;

-- Step 5: Make org_id NOT NULL (only after data migration is complete)
-- ALTER TABLE line_items 
-- ALTER COLUMN org_id SET NOT NULL;

-- Step 6: Add foreign key constraint to orgs table
-- ALTER TABLE line_items
-- ADD CONSTRAINT line_items_org_id_fkey 
-- FOREIGN KEY (org_id) REFERENCES orgs(org_id) ON DELETE CASCADE;

-- Step 7: Add foreign key constraint to products table (nullable - for service-based items)
-- ALTER TABLE line_items
-- ADD CONSTRAINT line_items_product_id_fkey 
-- FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE SET NULL;

-- Step 8: Add index on product_id for better query performance
-- CREATE INDEX IF NOT EXISTS idx_line_items_product_id ON line_items(product_id) WHERE product_id IS NOT NULL;

-- Step 9: Add index on org_id for better query performance
-- CREATE INDEX IF NOT EXISTS idx_line_items_org_id ON line_items(org_id);

-- Step 10: Drop old tenant_id column (only after migration is verified)
-- ALTER TABLE line_items 
-- DROP COLUMN tenant_id;

-- Step 11: Drop old branch_id column if it exists (legacy)
-- ALTER TABLE line_items 
-- DROP COLUMN IF EXISTS branch_id;

COMMIT;

-- ============================================================================
-- ROLLBACK SCRIPT (if needed)
-- ============================================================================
/*
BEGIN;
ALTER TABLE line_items DROP CONSTRAINT IF EXISTS line_items_org_id_fkey;
ALTER TABLE line_items DROP COLUMN IF EXISTS org_id;
COMMIT;
*/

-- ============================================================================
-- NOTES:
-- ============================================================================
-- 1. This migration is COMMENTED OUT in critical steps to prevent data loss
-- 2. Before running this in production:
--    a) Backup the database
--    b) Verify the tenant_id -> org_id mapping logic
--    c) Update the migration script with correct mapping
--    d) Test on a copy of production data
--    e) Uncomment and run each step carefully
-- 3. The application code must be updated to use org_id before dropping tenant_id
-- 4. Consider keeping tenant_id temporarily for backward compatibility
-- 5. product_id is NULLABLE by design:
--    - Products: line items with product_id reference actual products
--    - Services: line items without product_id are service-based
--    - This allows revenue_by_product view to work while supporting both cases
-- 6. Going forward, the application should:
--    - Set product_id when selling a product from inventory
--    - Leave product_id NULL when selling a custom service
--    - Use the line_items.name field for display in both cases
-- ============================================================================
