[DM-07] Reports table (performance metrics)

{
  "cost": {
    "input_tokens": 19,
    "estimated_usd": 0.035232,
    "output_tokens": 2345
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 30627,
  "response_text": "I'll help you design a Reports table for tracking performance metrics. Here's a comprehensive structure:\n\n## Reports Table Structure\n\n### Core Table: `reports`\n\n```sql\nCREATE TABLE reports (\n    report_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    report_name VARCHAR(255) NOT NULL,\n    report_type ENUM('sales', 'financial', 'inventory', 'customer', 'employee', 'operational', 'marketing', 'custom') NOT NULL,\n    report_category VARCHAR(100),\n    description TEXT,\n    \n    -- Time Period\n    period_type ENUM('daily', 'weekly', 'monthly', 'quarterly', 'yearly', 'custom') NOT NULL,\n    start_date DATE NOT NULL,\n    end_date DATE NOT NULL,\n    \n    -- Report Generation\n    generated_by BIGINT NOT NULL,\n    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    report_format ENUM('pdf', 'excel', 'csv', 'json', 'html') DEFAULT 'pdf',\n    file_path VARCHAR(500),\n    file_size INT,\n    \n    -- Status and Scheduling\n    status ENUM('pending', 'processing', 'completed', 'failed', 'archived') DEFAULT 'pending',\n    is_scheduled BOOLEAN DEFAULT FALSE,\n    schedule_frequency ENUM('daily', 'weekly', 'monthly', 'quarterly', 'yearly'),\n    next_run_date DATETIME,\n    \n    -- Metadata\n    parameters JSON, -- Report filters and configuration\n    execution_time INT, -- Time taken to generate (seconds)\n    record_count INT, -- Number of records in report\n    \n    -- Audit\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    is_active BOOLEAN DEFAULT TRUE,\n    \n    INDEX idx_report_type (report_type),\n    INDEX idx_period (start_date, end_date),\n    INDEX idx_generated_by (generated_by),\n    INDEX idx_status (status),\n    INDEX idx_schedule (is_scheduled, next_run_date),\n    INDEX idx_created_at (created_at),\n    \n    FOREIGN KEY (generated_by) REFERENCES users(user_id)\n);\n```\n\n### Performance Metrics Table: `report_metrics`\n\n```sql\nCREATE TABLE report_metrics (\n    metric_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    report_id BIGINT NOT NULL,\n    metric_name VARCHAR(255) NOT NULL,\n    metric_category VARCHAR(100),\n    metric_value DECIMAL(15,4),\n    metric_value_text VARCHAR(255), -- For non-numeric values\n    metric_unit VARCHAR(50), -- currency, percentage, count, etc.\n    comparison_period_value DECIMAL(15,4),\n    variance_amount DECIMAL(15,4),\n    variance_percentage DECIMAL(8,4),\n    target_value DECIMAL(15,4),\n    target_achieved BOOLEAN,\n    \n    -- Performance Indicators\n    trend ENUM('increasing', 'decreasing', 'stable', 'volatile'),\n    performance_rating ENUM('excellent', 'good', 'average', 'poor', 'critical'),\n    \n    -- Metadata\n    calculation_method TEXT,\n    data_source VARCHAR(255),\n    notes TEXT,\n    \n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    \n    INDEX idx_report_metric (report_id, metric_name),\n    INDEX idx_metric_category (metric_category),\n    INDEX idx_performance (performance_rating),\n    \n    FOREIGN KEY (report_id) REFERENCES reports(report_id) ON DELETE CASCADE\n);\n```\n\n### Report Templates Table: `report_templates`\n\n```sql\nCREATE TABLE report_templates (\n    template_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    template_name VARCHAR(255) NOT NULL,\n    report_type VARCHAR(100) NOT NULL,\n    description TEXT,\n    \n    -- Template Configuration\n    template_config JSON, -- Metrics, charts, layout configuration\n    default_parameters JSON,\n    required_permissions JSON,\n    \n    -- Template Properties\n    is_system_template BOOLEAN DEFAULT FALSE,\n    is_public BOOLEAN DEFAULT FALSE,\n    created_by BIGINT NOT NULL,\n    \n    -- Usage Statistics\n    usage_count INT DEFAULT 0,\n    last_used_at TIMESTAMP,\n    \n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    is_active BOOLEAN DEFAULT TRUE,\n    \n    INDEX idx_template_type (report_type),\n    INDEX idx_created_by (created_by),\n    INDEX idx_public (is_public, is_active),\n    \n    FOREIGN KEY (created_by) REFERENCES users(user_id)\n);\n```\n\n### Report Sharing & Access Table: `report_access`\n\n```sql\nCREATE TABLE report_access (\n    access_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    report_id BIGINT NOT NULL,\n    user_id BIGINT,\n    role_id BIGINT,\n    department_id BIGINT,\n    \n    -- Access Permissions\n    can_view BOOLEAN DEFAULT TRUE,\n    can_download BOOLEAN DEFAULT FALSE,\n    can_share BOOLEAN DEFAULT FALSE,\n    can_edit BOOLEAN DEFAULT FALSE,\n    \n    -- Access Control\n    access_granted_by BIGINT NOT NULL,\n    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    expires_at TIMESTAMP,\n    \n    -- Access Log\n    last_accessed_at TIMESTAMP,\n    access_count INT DEFAULT 0,\n    \n    is_active BOOLEAN DEFAULT TRUE,\n    \n    INDEX idx_report_access (report_id, user_id),\n    INDEX idx_user_access (user_id),\n    INDEX idx_role_access (role_id),\n    INDEX idx_expires (expires_at),\n    \n    FOREIGN KEY (report_id) REFERENCES reports(report_id) ON DELETE CASCADE,\n    FOREIGN KEY (user_id) REFERENCES users(user_id),\n    FOREIGN KEY (access_granted_by) REFERENCES users(user_id)\n);\n```\n\n## Key Performance Metrics Examples\n\n### Sales Metrics\n```sql\n-- Example metrics that could be stored\nINSERT INTO report_metrics (report_id, metric_name, metric_category, metric_value, metric_unit) VALUES\n(1, 'Total Revenue', 'sales', 150000.00, 'USD'),\n(1, 'Sales Growth Rate', 'sales', 12.5, 'percentage'),\n(1, 'Average Order Value', 'sales', 275.50, 'USD'),\n(1, 'Conversion Rate', 'sales', 3.2, 'percentage'),\n(1, 'Customer Acquisition Cost', 'sales', 45.75, 'USD');\n```\n\n### Operational Metrics\n```sql\nINSERT INTO report_metrics (report_id, metric_name, metric_category, metric_value, metric_unit) VALUES\n(2, 'Order Fulfillment Time', 'operational', 24.5, 'hours'),\n(2, 'Inventory Turnover', 'operational', 8.2, 'times'),\n(2, 'Employee Productivity', 'operational', 95.5, 'percentage'),\n(2, 'Customer Satisfaction Score', 'operational', 4.3, 'rating'),\n(2, 'Return Rate', 'operational', 2.1, 'percentage');\n```\n\n## Sample Queries\n\n### Generate Performance Dashboard\n```sql\nSELECT \n    r.report_name,\n    r.period_type,\n    r.start_date,\n    r.end_date,\n    rm.metric_name,\n    rm.metric_value,\n    rm.metric_unit,\n    rm.variance_percentage,\n    rm.performance_rating,\n    rm.trend\nFROM reports r\nJOIN report_metrics rm ON r.report_id = rm.report_id\nWHERE r.report_type = 'sales'\n    AND r.start_date >= '2024-01-01'\n    AND r.status = 'completed'\nORDER BY r.generated_at DESC, rm.metric_category, rm.metric_name;\n```\n\n### Scheduled Reports Due\n```sql\nSELECT \n    report_id,\n    report_name,\n    report_type,\n    schedule_frequency,\n    next_run_date,\n    DATEDIFF(next_run_date, NOW()) as days_until_run\nFROM reports\nWHERE is_scheduled = TRUE\n    AND status = 'completed'\n    AND next_run_date <= DATE_ADD(NOW(), INTERVAL 7 DAY)\n    AND is_active = TRUE\nORDER BY next_run_date;\n```\n\n### Performance Trends\n```sql\nSELECT \n    rm.metric_name,\n    rm.metric_category,\n    AVG(rm.metric_value) as avg_value,\n    MIN(rm.metric_value) as min_value,\n    MAX(rm.metric_value) as max_value,\n    COUNT(*) as report_count,\n    AVG(rm.variance_percentage) as avg_variance\nFROM report_metrics rm\nJOIN reports r ON rm.report_id = r.report_id\nWHERE r.report_type = 'sales'\n    AND r.start_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)\n    AND rm.metric_unit = 'USD'\nGROUP BY rm.metric_name, rm.metric_category\nORDER BY avg_value DESC;\n```\n\nThis structure provides comprehensive reporting capabilities with performance metrics tracking, trend analysis, and flexible report generation features."
}