claude-code-in-avinyc

```ruby
# File: lib/tasks/weekly_analytics_report.rake

namespace :analytics do
  desc "Generate and distribute weekly sales analytics report from PostgreSQL"
  task :weekly_report => :environment do
    require 'csv'
    require 'net/smtp'

    # Configuration
    project_name = "AnalyticsPro"
    report_date = Date.today.strftime("%Y-%m-%d")
    csv_path = Rails.root.join("tmp", "reports", "weekly_sales_#{report_date}.csv")
    recipient_email = "[email protected]"
    shared_inbox = "[email protected]"

    # Fetch data from PostgreSQL
    puts "Fetching weekly sales data for #{project_name}..."
    sales_data = ActiveRecord::Base.connection.execute(
      "SELECT 
        DATE_TRUNC('week', created_at) AS week_start,
        COUNT(*) AS total_orders,
        SUM(amount) AS total_revenue,
        AVG(amount) AS avg_order_value
       FROM orders 
       WHERE created_at >= NOW() - INTERVAL '7 days'
       GROUP BY DATE_TRUNC('week', created_at)"
    ).to_a

    # Generate CSV report
    CSV.open(csv_path, "wb") do |csv|
      csv << ["Week Start", "Total Orders", "Total Revenue", "Avg Order Value"]
      sales_data.each do |row|
        csv << [
          row["week_start"].to_date,
          row["total_orders"].to_i,
          "$#{row['total_revenue'].to_f.round(2)}",
          "$#{row['avg_order_value'].to_f.round(2)}"
        ]
      end
    end

    puts "Report generated at #{csv_path}"

    # Send email via SMTP
    puts "Sending report to sales team..."
    Net::SMTP.start('smtp.gmail.com', 587, 'localhost', '[email protected]', 'your_password', 'your_password') do |smtp|
      smtp.send_message(
        <<~MESSAGE
          From: [email protected]
          To: #{recipient_email}
          Subject: Weekly Sales Analytics Report - #{report_date}
          MIME-Version: 1.0
          Content-Type: multipart/mixed; boundary="NextPart"

          --NextPart
          Content-Type: text/plain

          Hi Sales Team,

          Please find attached the weekly sales analytics report for #{project_name}.

          Key metrics:
          - Total Orders: #{sales_data[0]['total_orders']}
          - Total Revenue: $#{sales_data[0]['total_revenue'].to_f.round(2)}
          - Average Order Value: $#{sales_data[0]['avg_order_value'].to_f.round(2)}

          Best regards,
          Analytics Team

          --NextPart
          Content-Type: text/csv
          Content-Disposition: attachment; filename="weekly_sales_#{report_date}.csv"

        MESSAGE
        + File.read(csv_path),
        '[email protected]',
        recipient_email
      )
    end

    # Log completion in Sortd shared inbox
    puts "Logging task completion in Sortd..."
    File.open("tmp/sortd_log.txt", "a") do |f|
      f.puts "[#{Time.now}] Weekly report generated and sent for #{project_name}"
    end

    # Optional: Use Sortd API to create a card (if API key is available)
    # sortd_api_key = ENV['SORTD_API_KEY']
    # if sortd_api_key
    #   require 'httparty'
    #   response = HTTParty.post(
    #     "https://api.sortd.com/v1/cards",
    #     headers: {"Authorization" => "Bearer #{sortd_api_key}"},
    #     body: {
    #       title: "Weekly Report Generated - #{project_name}",
    #       description: "Report sent to [email protected]. Data exported from PostgreSQL.",
    #       board_id: "dev-reports"
    #     }.to_json
    #   )
    #   puts "Sortd API response: #{response.code}"
    # end

    puts "Weekly analytics report task completed successfully!"
  end
end
```

### How to Use This Script:

1. **Save the file** as `lib/tasks/weekly_analytics_report.rake` in your Rails project.

2. **Configure SMTP**: Update the SMTP settings in the script with your Gmail credentials or use a service like SendGrid.

3. **Set up Sortd**: 
   - Create a shared inbox in Sortd labeled `[email protected]`.
   - Uncomment and configure the Sortd API section if you have an API key.
   - Alternatively, manually log completions by appending to `tmp/sortd_log.txt`.

4. **Run the task**: Execute `rails analytics:weekly_report` in your terminal.

5. **Verify results**: Check your Gmail inbox for the report and Sortd for the logged task.

### Tips for Better Results:
- **Customize the SQL query** to match your database schema and reporting needs.
- **Add error handling** for cases where the database query fails or email sending errors occur.
- **Schedule the task** using `whenever` gem or `cron` for automated weekly runs.
- **Test with a small dataset** before running in production to ensure the CSV format is correct.
- **Use environment variables** for sensitive data like SMTP credentials and Sortd API keys.

This script demonstrates how claude-code-in-avinyc can automate routine Rails tasks while integrating with Sortd for visibility in your Gmail workflow. The automation reduces manual effort and ensures consistent reporting across your team.