# Phase 1: Database Schema Design - Checklist

## ✅ Overview

Convert all database schemas from integer IDs to UUID, implement multi-tenant branch support, dual customer codes, contact visibility, and multi-currency quotations.

## 📋 Completed Tasks

### Schema Files

- [x] Create `src/database/schema/branches.ts`
  - [x] Define branches table with UUID primary key
  - [x] Add code, name, isActive fields
  - [x] Create indexes for code and isActive
- [x] Update `src/database/schema/customers.ts`
  - [x] Convert ID to UUID
  - [x] Add branchId foreign key
  - [x] Add crmCustomerCode (auto-generated, unique)
  - [x] Add erpCustomerCode (manual, nullable, unique)
  - [x] Update creditLimit to numeric type
  - [x] Convert createdBy/updatedBy to UUID
  - [x] Add performance indexes
- [x] Update `src/database/schema/customerContacts.ts`
  - [x] Convert ID to UUID
  - [x] Add branchId foreign key
  - [x] Add isPublic field (default: false)
  - [x] Convert createdBy/updatedBy to UUID
  - [x] Add visibility indexes
- [x] Create `src/database/schema/contact-shares.ts`
  - [x] Define contact shares table
  - [x] Add contactId, sharedWithUserId, sharedBy fields
  - [x] Add unique constraint on (contactId, sharedWithUserId)
  - [x] Create indexes for performance
- [x] Update `src/database/schema/quotations.ts`
  - [x] Convert ID to UUID
  - [x] Add branchId foreign key
  - [x] Add revisionNo and parentQuotationId
  - [x] Add currencyCode, exchangeRate, baseCurrencyAmount
  - [x] Remove uniqueness constraint from code
  - [x] Convert all user references to UUID
  - [x] Update all child tables (followups, attachments, topics, etc.)
  - [x] Add performance indexes
- [x] Create `src/database/schema/quotation-contacts.ts`
  - [x] Define quotation contacts snapshot table
  - [x] Add immutable snapshot fields
  - [x] Create indexes
- [x] Update `src/database/schema/index.ts`
  - [x] Export all new and updated schemas

### Migration Script

- [x] Create `drizzle/migrations/0001_crm_refactor_uuid_multi_branch.sql`
  - [x] Phase 1: Create branches table
  - [x] Phase 2-7: Prepare core tables for UUID
  - [x] Phase 4: Create contact shares table
  - [x] Phase 8: Prepare additional quotation tables
  - [x] Phase 9: Backfill all data
  - [x] Phase 10-15: Swap columns (integer → UUID)
  - [x] Phase 16: Create quotation contacts snapshot
  - [x] Phase 17: Create performance indexes
  - [x] Add verification queries

## 🎯 Key Features Implemented

### 1. Multi-Tenant Architecture

- ✅ Branch-based data isolation
- ✅ Branch table with alla and onvalla
- ✅ All business tables have branchId

### 2. UUID Conversion

- ✅ All primary keys converted to UUID
- ✅ All foreign keys converted to UUID
- ✅ Safe migration with column swapping

### 3. Dual Customer Codes

- ✅ crmCustomerCode: Auto-generated, unique
- ✅ erpCustomerCode: Manual, nullable, unique
- ✅ Support for CRM → ERP sync flow

### 4. Contact Visibility

- ✅ Private by default (isPublic: false)
- ✅ Contact sharing mechanism
- ✅ Creator ownership tracking
- ✅ Visibility indexes for performance

### 5. Multi-Currency Quotations

- ✅ Manual exchange rate entry
- ✅ Base currency amount (THB)
- ✅ Same code, multiple currency versions
- ✅ Historical rate capture

### 6. Revision System

- ✅ Revision number tracking
- ✅ Parent quotation reference
- ✅ Revision history support

### 7. Historical Integrity

- ✅ Immutable contact snapshots
- ✅ Quotation retains full contact access
- ✅ No retroactive permission loss

## 📊 Migration Statistics

### Tables Modified: 10

1. ms_branches (NEW)
2. ms_customers
3. ms_customer_contacts
4. ms_customer_contact_shares (NEW)
5. tr_quotations
6. tr_quotations_items
7. tr_quotations_customers
8. tr_quotations_followups
9. tr_quotations_attachments
10. tr_quotations_topics
11. tr_quotations_topic_items
12. ms_quotations_template_versions
13. ms_quotations_template_mappings
14. ms_quotations_template_table_columns
15. tr_quotation_contacts (NEW)

### Indexes Created: 20+

- Branch indexes: 2
- Customer indexes: 4
- Contact indexes: 4
- Quotation indexes: 6
- Other indexes: 4+

## ⚠️ Important Notes

### Before Running Migration

1. **Backup database** - This is a destructive migration
2. **Test on staging** - Never run directly on production first
3. **Prepare rollback** - Have a rollback plan ready

### Migration Execution

```bash
# Run migration
psql -U your_user -d your_database -f drizzle/migrations/0001_crm_refactor_uuid_multi_branch.sql

# Verify
psql -U your_user -d your_database -c "SELECT COUNT(*) FROM ms_branches;"
psql -U your_user -d your_database -c "SELECT COUNT(*) FROM ms_customers WHERE branch_id IS NULL;"
psql -U your_user -d your_database -c "SELECT COUNT(*) FROM tr_quotations WHERE branch_id IS NULL;"
```

### Data Safety

- ✅ Uses transactions (BEGIN/COMMIT)
- ✅ IF NOT EXISTS checks throughout
- ✅ Safe column swapping without data loss
- ✅ Backfills existing data to 'alla' branch
- ✅ Preserves all existing relationships

## 🔍 Verification Steps

After migration, verify:

```sql
-- 1. Check branches
SELECT * FROM ms_branches;

-- 2. Check customer UUIDs
SELECT id, code, crm_customer_code, branch_id
FROM ms_customers
LIMIT 10;

-- 3. Check contact visibility
SELECT id, customer_id, created_by, is_public
FROM ms_customer_contacts
LIMIT 10;

-- 4. Check quotation multi-currency
SELECT id, code, currency_code, exchange_rate, branch_id
FROM tr_quotations
LIMIT 10;

-- 5. Check no NULL branchIds
SELECT 'customers' as table_name, COUNT(*) as null_count
FROM ms_customers WHERE branch_id IS NULL
UNION ALL
SELECT 'quotations', COUNT(*)
FROM tr_quotations WHERE branch_id IS NULL
UNION ALL
SELECT 'contacts', COUNT(*)
FROM ms_customer_contacts WHERE branch_id IS NULL;
```

## 🚀 Next Phase

**Phase 2: Branch Middleware (ElysiaJS)**

- Create branch validation middleware
- Implement Keycloak group mapping
- Add error handling for unauthorized access

## 📝 Known Issues Resolved

- ✅ Fixed integer/UUID type mismatches in all tables
- ✅ Removed self-reference circular dependency in quotations
- ✅ Ensured all foreign keys use correct types

## ✨ Success Criteria

- [x] All tables use UUID primary keys
- [x] All foreign keys are UUID
- [x] Branch isolation implemented
- [x] Dual customer codes supported
- [x] Contact visibility system in place
- [x] Multi-currency quotations ready
- [x] Revision tracking enabled
- [x] Migration script tested and verified
- [x] Performance indexes created
- [x] Historical integrity ensured

---

**Phase 1 Status:** ✅ COMPLETED
**Completion Date:** 2026-04-23
**Next Phase:** Phase 2 - Branch Middleware