# Database Schema - Task Breakdown

## Pre-requisites

- [ ] Access to `userfrosting/migrations/` directory
- [ ] Understanding of JSON migration format
- [ ] Access to test store database

## Tasks

### Task 1: Create Tasks Table Alter Migration

**Estimated Complexity**: Low
**Files**: `userfrosting/migrations/input/20250301_001_daybook_tasks_table_alter.json`

**Acceptance Criteria**:
- [ ] JSON file created with correct format
- [ ] `priority` column addition defined with check_query
- [ ] `sortOrder` column addition defined with check_query
- [ ] Database set to `{{store}}`
- [ ] Migration runs without error on test database
- [ ] Re-running migration skips already applied changes

---

### Task 2: Create Task System Tables Migration

**Estimated Complexity**: Medium
**Files**: `userfrosting/migrations/input/20250301_002_daybook_task_system.json`

**Acceptance Criteria**:
- [ ] `daybook_task_lists` table defined
- [ ] `daybook_task_assignments` table defined
- [ ] `daybook_task_completions` table defined with unique constraint
- [ ] `daybook_task_comments` table defined
- [ ] All tables have proper indexes
- [ ] All tables use utf8mb4 charset
- [ ] check_query for each table uses `SHOW TABLES LIKE`
- [ ] Migration runs without error on test database

---

### Task 3: Create Notes System Tables Migration

**Estimated Complexity**: Medium
**Files**: `userfrosting/migrations/input/20250301_003_daybook_notes_system.json`

**Acceptance Criteria**:
- [ ] `daybook_notes` table defined with visibility indexes
- [ ] `daybook_note_reactions` table defined with unique constraint
- [ ] `daybook_note_comments` table defined
- [ ] All tables have proper indexes
- [ ] check_query for each table
- [ ] Migration runs without error on test database

---

### Task 4: Create Whiteboard System Tables Migration

**Estimated Complexity**: Medium
**Files**: `userfrosting/migrations/input/20250301_004_daybook_whiteboard_system.json`

**Acceptance Criteria**:
- [ ] `daybook_whiteboard_canvas` table defined with unique date constraint
- [ ] `daybook_whiteboard_items` table defined with enum type
- [ ] `daybook_whiteboard_history` table defined with enum action
- [ ] `canvasData` uses longtext for large canvas data
- [ ] All tables have proper indexes
- [ ] Migration runs without error on test database

---

### Task 5: Create KPI Config Table Migration

**Estimated Complexity**: Low
**Files**: `userfrosting/migrations/input/20250301_005_daybook_kpi_config.json`

**Acceptance Criteria**:
- [ ] `daybook_kpi_config` table defined
- [ ] Unique constraint on `kpiKey`
- [ ] check_query using `SHOW TABLES LIKE`
- [ ] Migration runs without error on test database

---

### Task 6: Create KPI Defaults Insert Migration

**Estimated Complexity**: Low
**Files**: `userfrosting/migrations/input/20250301_006_daybook_kpi_defaults.json`

**Acceptance Criteria**:
- [ ] Insert statement with all 13 default KPIs
- [ ] check_query verifies table is empty before insert
- [ ] `expected_count: 0` set for idempotency
- [ ] All KPI keys match specification
- [ ] Migration runs without error on test database
- [ ] Re-running migration skips insert if data exists

---

### Task 7: Create Schedule Cache Table Migration

**Estimated Complexity**: Low
**Files**: `userfrosting/migrations/input/20250301_007_daybook_schedule_cache.json`

**Acceptance Criteria**:
- [ ] `daybook_schedule_cache` table defined
- [ ] `provider` enum includes wheniwork, homebase, manual
- [ ] Proper indexes on date and employee
- [ ] check_query using `SHOW TABLES LIKE`
- [ ] Migration runs without error on test database

---

### Task 8: Test Migration on Single Store

**Estimated Complexity**: Low
**Description**: Run migrations on a single test store database

**Acceptance Criteria**:
- [ ] All 7 migration files pass syntax validation
- [ ] Run `php migrate.php` from migrations directory
- [ ] All migrations complete without error
- [ ] `migration_log` table shows all migrations recorded
- [ ] Verify tables exist: `SHOW TABLES LIKE 'daybook_%'`
- [ ] Verify `tasks` table has new columns
- [ ] Verify `daybook_kpi_config` has 13 rows

---

### Task 9: Test Migration Idempotency

**Estimated Complexity**: Low
**Description**: Verify migrations can be re-run safely

**Acceptance Criteria**:
- [ ] Run `php migrate.php` a second time
- [ ] No errors occur
- [ ] Migrations show as "skipped" (already applied)
- [ ] No duplicate data in `daybook_kpi_config`
- [ ] `migration_log` doesn't have duplicate entries

---

### Task 10: Run Migrations on All Stores

**Estimated Complexity**: Medium
**Description**: Apply migrations to all production store databases

**Acceptance Criteria**:
- [ ] Backup all store databases before migration
- [ ] Run `php migrate.php`
- [ ] All stores show successful migration
- [ ] Spot-check 3 different stores for table existence
- [ ] Verify no data loss in existing `tasks` table
- [ ] Document any stores that failed and resolve

---

### Task 11: Update BLANK_STRUCTURE.sql

**Estimated Complexity**: Low
**Files**: `sql/BLANK_STRUCTURE.sql`

**Acceptance Criteria**:
- [ ] All `daybook_*` table definitions added
- [ ] `tasks` table includes `priority` and `sortOrder` columns
- [ ] Default KPI insert statements included
- [ ] New store provisioning creates all Daybook tables

---

### Task 12: Create Rollback Migration (Keep Disabled)

**Estimated Complexity**: Low
**Files**: `userfrosting/migrations/input/20250301_999_daybook_rollback.json.disabled`

**Acceptance Criteria**:
- [ ] File created with `.disabled` extension (won't auto-run)
- [ ] DROP TABLE statements for all daybook_* tables
- [ ] ALTER TABLE statements to remove tasks columns
- [ ] Instructions in file header for manual rollback

---

## Testing Tasks

### Test 1: Verify Table Structure

**Description**: Verify all tables match specification

**Test Cases**:
- [ ] `daybook_task_lists` has all 7 columns
- [ ] `daybook_task_completions` has unique constraint on (taskId, date)
- [ ] `daybook_notes` has visibility index
- [ ] `daybook_whiteboard_items` enum has 3 values
- [ ] `daybook_kpi_config` unique constraint on kpiKey
- [ ] All tables have AUTO_INCREMENT primary keys

---

### Test 2: Verify Foreign Key Compatibility

**Description**: Test that foreign key columns match parent tables

**Test Cases**:
- [ ] `daybook_task_lists.groupId` can reference `taskGroups.id`
- [ ] `daybook_*` employeeId columns can reference `employees.id`
- [ ] `daybook_task_*.taskId` columns can reference `tasks.id`
- [ ] Note: Foreign keys not enforced at DB level, but types must match

---

### Test 3: Verify Data Types

**Description**: Ensure all data types are correct

**Test Cases**:
- [ ] All IDs are `int(10) unsigned`
- [ ] All timestamps are `timestamp` type
- [ ] All text content is `text` type
- [ ] `canvasData` is `longtext` (for large data)
- [ ] All varchar fields have appropriate lengths

---

## Completion Checklist

- [ ] All 7 migration JSON files created
- [ ] All migrations tested on single store
- [ ] Migrations tested for idempotency
- [ ] All stores migrated successfully
- [ ] BLANK_STRUCTURE.sql updated
- [ ] Rollback migration prepared (disabled)
- [ ] All test cases passed