┌─────────────────────────────────────────────────────────────────────────────┐
│                        REPORTING LAYERS                                      │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   OPERATIONAL ────► TACTICAL ────► STRATEGIC                                 │
│                                                                              │
│   Daily status      Weekly summary    Monthly review                         │
│   Deliverable       Project health    ROI analysis                          │
│   updates           Milestone track   Recommendations                        │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

## Weekly Status Report

**Project:** [Project Name]
**Period:** [Date Range]
**Overall Status:** 🟢 On Track / 🟡 At Risk / 🔴 Blocked

### Progress Summary

| Milestone | Status | Completion |
|-----------|--------|------------|
| Setup | ✅ Complete | 100% |
| Build Phase 1 | 🔄 In Progress | 65% |
| Testing | ⏳ Upcoming | 0% |

### This Week
- ✅ Completed API integration
- ✅ Built first automation workflow
- 🔄 Testing in progress

### Next Week
- Complete workflow testing
- Client review session
- Documentation draft

### Blockers
- None currently

### Hours Logged
- This week: 12 hours
- Total: 28 / 40 estimated

### Deliverables Status
| Deliverable | Due | Status |
|-------------|-----|--------|
| Workflow v1 | Dec 20 | In Review |
| Documentation | Dec 22 | In Progress |

## Deliverable Submission

**Deliverable:** [Title]
**Project:** [Project Name]
**Version:** 2.0
**Submitted:** [Date]

### Description
[What this deliverable does]

### Files Included
- workflow.json (n8n export)
- documentation.pdf
- training-video.mp4

### Testing Summary
| Test Case | Result |
|-----------|--------|
| Happy path | ✅ Pass |
| Error handling | ✅ Pass |
| Edge case: empty input | ✅ Pass |

### Changes from v1
- Added retry logic
- Improved error messages
- Performance optimization

### Action Required
Please review and approve or provide feedback by [Date].

[Approve Button] [Request Changes Button]

## Monthly Executive Summary

**Client:** [Company Name]
**Period:** [Month Year]
**Account Lead:** [Name]

---

### Investment Summary

| Category | This Month | YTD |
|----------|------------|-----|
| Retainer | $5,000 | $30,000 |
| Projects | $2,500 | $15,000 |
| Total | $7,500 | $45,000 |

### Value Delivered

#### Time Savings
| Automation | Hours Saved/Week | Monthly Value* |
|------------|------------------|----------------|
| Lead routing | 8 | $1,600 |
| Report generation | 4 | $800 |
| Email sequences | 6 | $1,200 |
| **Total** | **18** | **$3,600** |

*Calculated at $50/hour equivalent

#### ROI Calculation
- Monthly investment: $7,500
- Monthly value delivered: $3,600 direct + estimated indirect
- Payback: 2.1 months

### Active Initiatives

1. **Sales Automation (Phase 2)**
   - Status: 75% complete
   - Expected completion: [Date]
   
2. **Analytics Dashboard**
   - Status: Planning
   - Start date: [Date]

### Recommendations

1. **Expand email automation** - Current 3 sequences → 7
   - Estimated additional value: $800/month
   - Investment: $3,000 one-time

2. **Add Slack integration** - Real-time alerts
   - Estimated time savings: 2 hours/week
   - Investment: Included in retainer

### Next Month Focus
- Complete sales automation
- Begin analytics dashboard
- Q1 planning session

---

**Questions?** Schedule time: [Calendly link]

interface ROIDashboard {
  client_id: string;
  period: {
    start: string;
    end: string;
  };
  
  investment: {
    retainer_total: number;
    project_total: number;
    total: number;
  };
  
  value: {
    time_saved_hours: number;
    time_saved_value: number;  // hours × hourly_rate
    revenue_attributed: number;
    cost_avoided: number;
    total_value: number;
  };
  
  roi: {
    ratio: number;  // value / investment
    payback_months: number;
  };
  
  automations: {
    name: string;
    executions: number;
    success_rate: number;
    time_saved_per_execution: number;
    total_time_saved: number;
  }[];
}

-- Generated reports
CREATE TABLE agency_reports (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  canonical_id text UNIQUE,
  
  -- Relationships
  client_id uuid REFERENCES agency_clients(id),
  project_id uuid REFERENCES agency_projects(id),  -- NULL for client-level reports
  
  -- Report type
  report_type text NOT NULL,  -- 'weekly_status', 'monthly_executive', 'deliverable', 'custom'
  
  -- Content
  title text NOT NULL,
  period_start date,
  period_end date,
  
  -- Generated content (markdown or JSON)
  content jsonb NOT NULL,
  
  -- Output
  file_url text,  -- PDF export URL
  
  -- Delivery tracking
  sent_at timestamptz,
  sent_to text[],  -- Email addresses
  opened_at timestamptz,
  
  -- Status
  status text DEFAULT 'draft',  -- 'draft', 'sent', 'viewed'
  
  created_at timestamptz DEFAULT now(),
  created_by uuid REFERENCES auth.users(id)
);

-- Report metrics for ROI tracking
CREATE TABLE agency_report_metrics (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  client_id uuid REFERENCES agency_clients(id),
  period_start date NOT NULL,
  period_end date NOT NULL,
  
  -- Investment
  retainer_amount numeric DEFAULT 0,
  project_amount numeric DEFAULT 0,
  
  -- Value metrics
  hours_saved numeric DEFAULT 0,
  hourly_rate_used numeric DEFAULT 50,  -- For calculations
  revenue_attributed numeric DEFAULT 0,
  cost_avoided numeric DEFAULT 0,
  
  -- Automation stats
  total_executions integer DEFAULT 0,
  successful_executions integer DEFAULT 0,
  
  -- Calculated
  total_investment numeric GENERATED ALWAYS AS (retainer_amount + project_amount) STORED,
  total_value numeric GENERATED ALWAYS AS (
    (hours_saved * hourly_rate_used) + revenue_attributed + cost_avoided
  ) STORED,
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  
  UNIQUE(client_id, period_start, period_end)
);

-- Automation execution logs (for metrics)
CREATE TABLE agency_automation_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  client_id uuid REFERENCES agency_clients(id),
  automation_name text NOT NULL,
  
  -- Execution details
  executed_at timestamptz DEFAULT now(),
  success boolean NOT NULL,
  execution_time_ms integer,
  
  -- Value attribution
  time_saved_minutes numeric,
  
  -- Error tracking
  error_message text,
  
  -- Metadata
  metadata jsonb DEFAULT '{}'
);

-- Index for reporting queries
CREATE INDEX idx_automation_logs_client_date 
ON agency_automation_logs(client_id, executed_at);

interface WeeklyStatusData {
  project: AgencyProject;
  milestones: Milestone[];
  deliverables: Deliverable[];
  timeEntries: TimeEntry[];
  blockers: string[];
}

async function generateWeeklyStatus(projectId: string): Promise<string> {
  const data = await fetchWeeklyData(projectId);
  
  const statusEmoji = getStatusEmoji(data);
  const completionPercent = calculateCompletion(data.milestones);
  
  const report = `
## Weekly Status Report

**Project:** ${data.project.name}
**Period:** ${formatDateRange(getWeekRange())}
**Overall Status:** ${statusEmoji}

### Progress Summary

${renderMilestoneTable(data.milestones)}

### This Week
${data.deliverables
  .filter(d => d.updated_this_week)
  .map(d => `- ${d.is_completed ? '✅' : '🔄'} ${d.title}`)
  .join('\n')}

### Next Week
${getUpcomingWork(data.milestones, data.deliverables)
  .map(item => `- ${item}`)
  .join('\n')}

### Blockers
${data.blockers.length > 0 
  ? data.blockers.map(b => `- ⚠️ ${b}`).join('\n')
  : '- None currently'}

### Hours
- This week: ${sumHours(data.timeEntries, 'this_week')}
- Total: ${sumHours(data.timeEntries, 'all')} / ${data.project.estimated_hours || '?'} estimated

### Deliverables

${renderDeliverableTable(data.deliverables)}
  `;
  
  return report;
}

function getStatusEmoji(data: WeeklyStatusData): string {
  const hasBlockers = data.blockers.length > 0;
  const overdueCount = data.deliverables.filter(
    d => !d.is_completed && new Date(d.due_date) < new Date()
  ).length;
  
  if (hasBlockers || overdueCount > 2) return '🔴 Blocked';
  if (overdueCount > 0) return '🟡 At Risk';
  return '🟢 On Track';
}

async function generateMonthlyExecutive(clientId: string, month: Date): Promise<string> {
  const [
    investment,
    metrics,
    automationStats,
    activeProjects,
    completedDeliverables
  ] = await Promise.all([
    getMonthlyInvestment(clientId, month),
    getMonthlyMetrics(clientId, month),
    getAutomationStats(clientId, month),
    getActiveProjects(clientId),
    getCompletedDeliverables(clientId, month)
  ]);
  
  const roi = calculateROI(investment, metrics);
  
  return `
## Monthly Executive Summary

**Client:** ${metrics.client.company_name}
**Period:** ${format(month, 'MMMM yyyy')}
**Account Lead:** ${metrics.account_lead}

---

### Investment Summary

| Category | This Month | YTD |
|----------|------------|-----|
| Retainer | ${formatCurrency(investment.retainer)} | ${formatCurrency(investment.ytd_retainer)} |
| Projects | ${formatCurrency(investment.projects)} | ${formatCurrency(investment.ytd_projects)} |
| **Total** | **${formatCurrency(investment.total)}** | **${formatCurrency(investment.ytd_total)}** |

### Value Delivered

#### Time Savings
| Automation | Hours Saved/Week | Monthly Value |
|------------|------------------|---------------|
${automationStats.map(a => 
  `| ${a.name} | ${a.hours_per_week} | ${formatCurrency(a.monthly_value)} |`
).join('\n')}
| **Total** | **${sumBy(automationStats, 'hours_per_week')}** | **${formatCurrency(metrics.total_time_value)}** |

#### ROI Calculation
- Monthly investment: ${formatCurrency(investment.total)}
- Monthly value delivered: ${formatCurrency(metrics.total_value)}
- ROI: ${roi.ratio.toFixed(1)}x
- Payback period: ${roi.payback_months.toFixed(1)} months

### Active Initiatives

${activeProjects.map((p, i) => `
${i + 1}. **${p.name}**
   - Status: ${p.completion_percent}% complete
   - Expected completion: ${format(p.end_date, 'MMM d, yyyy')}
`).join('\n')}

### Recommendations

${generateRecommendations(metrics, automationStats).map((r, i) => `
${i + 1}. **${r.title}**
   - ${r.description}
   - Estimated value: ${formatCurrency(r.estimated_value)}/month
   - Investment: ${formatCurrency(r.investment)}
`).join('\n')}

---

**Questions?** Schedule time: ${metrics.calendly_link}
  `;
}

-- Calculate client ROI for a period
CREATE OR REPLACE FUNCTION calculate_client_roi(
  p_client_id uuid,
  p_start_date date,
  p_end_date date
) RETURNS jsonb AS $$
DECLARE
  result jsonb;
  total_investment numeric;
  total_value numeric;
BEGIN
  -- Get investment
  SELECT 
    COALESCE(SUM(r.monthly_amount), 0) + COALESCE(SUM(p.budget), 0)
  INTO total_investment
  FROM agency_clients c
  LEFT JOIN agency_retainers r ON c.id = r.client_id AND r.is_active
  LEFT JOIN agency_projects p ON c.id = p.client_id 
    AND p.start_date >= p_start_date 
    AND p.start_date <= p_end_date
  WHERE c.id = p_client_id;
  
  -- Get value from metrics
  SELECT COALESCE(SUM(total_value), 0)
  INTO total_value
  FROM agency_report_metrics
  WHERE client_id = p_client_id
    AND period_start >= p_start_date
    AND period_end <= p_end_date;
  
  result := jsonb_build_object(
    'investment', total_investment,
    'value', total_value,
    'roi_ratio', CASE WHEN total_investment > 0 THEN total_value / total_investment ELSE 0 END,
    'net_value', total_value - total_investment
  );
  
  RETURN result;
END;
$$ LANGUAGE plpgsql;

interface TimeSavingsConfig {
  automation_name: string;
  manual_time_minutes: number;  // Time if done manually
  automated_time_minutes: number;  // Time with automation
  frequency_per_week: number;
  hourly_rate: number;  // Client's equivalent hourly cost
}

function calculateTimeSavings(config: TimeSavingsConfig, executions: number): {
  hours_saved: number;
  monetary_value: number;
} {
  const minutesSavedPerExecution = config.manual_time_minutes - config.automated_time_minutes;
  const totalMinutesSaved = minutesSavedPerExecution * executions;
  const hoursSaved = totalMinutesSaved / 60;
  const monetaryValue = hoursSaved * config.hourly_rate;
  
  return {
    hours_saved: hoursSaved,
    monetary_value: monetaryValue
  };
}

// Track execution and log savings
async function logAutomationExecution(
  clientId: string,
  automationName: string,
  success: boolean,
  config: TimeSavingsConfig
) {
  const savings = calculateTimeSavings(config, 1);
  
  await supabase.from('agency_automation_logs').insert({
    client_id: clientId,
    automation_name: automationName,
    success,
    time_saved_minutes: success ? (config.manual_time_minutes - config.automated_time_minutes) : 0
  });
}

async function sendReport(report: AgencyReport) {
  const client = await getClient(report.client_id);
  const recipients = getReportRecipients(client, report.report_type);
  
  // Generate PDF
  const pdfUrl = await generatePDF(report);
  
  // Send via email
  await sendEmail({
    to: recipients,
    subject: `${report.title} - ${format(new Date(), 'MMM d, yyyy')}`,
    template: 'agency-report',
    data: {
      client_name: client.company_name,
      report_title: report.title,
      report_preview: extractPreview(report.content),
      pdf_url: pdfUrl,
      dashboard_url: `${APP_URL}/clients/${client.canonical_id}/reports`
    }
  });
  
  // Update report status
  await supabase
    .from('agency_reports')
    .update({
      status: 'sent',
      sent_at: new Date().toISOString(),
      sent_to: recipients,
      file_url: pdfUrl
    })
    .eq('id', report.id);
}

async function postReportToSlack(report: AgencyReport) {
  const client = await getClient(report.client_id);
  const channelId = client.slack_channel_id;
  
  if (!channelId) return;
  
  await slack.chat.postMessage({
    channel: channelId,
    blocks: [
      {
        type: 'header',
        text: { type: 'plain_text', text: report.title }
      },
      {
        type: 'section',
        text: { 
          type: 'mrkdwn', 
          text: extractSlackPreview(report.content) 
        }
      },
      {
        type: 'actions',
        elements: [
          {
            type: 'button',
            text: { type: 'plain_text', text: 'View Full Report' },
            url: `${APP_URL}/reports/${report.canonical_id}`
          }
        ]
      }
    ]
  });
}

// n8n workflow or cron job
const reportSchedule = [
  {
    type: 'weekly_status',
    schedule: 'every Friday at 4pm',
    cron: '0 16 * * 5',
    generator: generateWeeklyStatus,
    recipients: 'project_stakeholders'
  },
  {
    type: 'monthly_executive',
    schedule: 'first Monday of month at 9am',
    cron: '0 9 1-7 * 1',  // First Monday
    generator: generateMonthlyExecutive,
    recipients: 'client_executives'
  }
];

// Scheduled job
async function runScheduledReports() {
  const activeClients = await getActiveClients();
  
  for (const client of activeClients) {
    // Weekly status for each active project
    const projects = await getActiveProjects(client.id);
    for (const project of projects) {
      const report = await generateWeeklyStatus(project.id);
      await saveAndSendReport(report, client, 'weekly_status');
    }
    
    // Monthly executive (if it's the right time)
    if (isFirstMondayOfMonth()) {
      const execReport = await generateMonthlyExecutive(client.id, new Date());
      await saveAndSendReport(execReport, client, 'monthly_executive');
    }
  }
}

// Client reporting dashboard
interface ClientReportingDashboard {
  // Summary cards
  summary: {
    active_projects: number;
    completed_deliverables: number;
    hours_this_month: number;
    roi_ratio: number;
  };
  
  // Project status
  projects: {
    id: string;
    name: string;
    status: 'on_track' | 'at_risk' | 'blocked';
    completion_percent: number;
    next_milestone: string;
    next_milestone_date: string;
  }[];
  
  // Recent deliverables
  recent_deliverables: {
    id: string;
    title: string;
    status: 'delivered' | 'in_review' | 'in_progress';
    delivered_at?: string;
  }[];
  
  // Value metrics
  value_metrics: {
    hours_saved_this_month: number;
    executions_this_month: number;
    success_rate: number;
  };
  
  // Report history
  reports: {
    id: string;
    title: string;
    type: string;
    created_at: string;
    file_url: string;
  }[];
}

-- Dashboard data for client
CREATE OR REPLACE FUNCTION get_client_dashboard(p_client_id uuid)
RETURNS jsonb AS $$
DECLARE
  result jsonb;
BEGIN
  SELECT jsonb_build_object(
    'summary', (
      SELECT jsonb_build_object(
        'active_projects', COUNT(*) FILTER (WHERE status = 'active'),
        'total_projects', COUNT(*)
      )
      FROM agency_projects WHERE client_id = p_client_id
    ),
    'projects', (
      SELECT jsonb_agg(jsonb_build_object(
        'id', p.canonical_id,
        'name', p.name,
        'status', p.status,
        'budget', p.budget,
        'spent', p.spent,
        'deliverable_count', (SELECT COUNT(*) FROM agency_deliverables WHERE project_id = p.id),
        'completed_count', (SELECT COUNT(*) FROM agency_deliverables WHERE project_id = p.id AND is_completed)
      ))
      FROM agency_projects p
      WHERE p.client_id = p_client_id AND p.status = 'active'
    ),
    'recent_deliverables', (
      SELECT jsonb_agg(jsonb_build_object(
        'id', d.canonical_id,
        'title', d.title,
        'is_completed', d.is_completed,
        'approved_at', d.approved_at,
        'due_date', d.due_date
      ) ORDER BY COALESCE(d.completed_at, d.due_date) DESC)
      FROM agency_deliverables d
      JOIN agency_projects p ON d.project_id = p.id
      WHERE p.client_id = p_client_id
      LIMIT 10
    ),
    'recent_reports', (
      SELECT jsonb_agg(jsonb_build_object(
        'id', r.canonical_id,
        'title', r.title,
        'type', r.report_type,
        'created_at', r.created_at,
        'file_url', r.file_url
      ) ORDER BY r.created_at DESC)
      FROM agency_reports r
      WHERE r.client_id = p_client_id
      LIMIT 5
    )
  ) INTO result;
  
  RETURN result;
END;
$$ LANGUAGE plpgsql;