1  #!/usr/bin/env node
  2  
  3  /**
  4   * One-time script: Re-export pending outreaches to the persistent sheet
  5   * with the new 12-column layout (Country + Google Domain instead of Score)
  6   */
  7  
  8  import 'dotenv/config';
  9  import { google } from 'googleapis';
 10  import { createDatabaseConnection } from '../src/utils/db.js';
 11  
 12  const dbPath = process.env.DATABASE_PATH || './db/sites.db';
 13  const spreadsheetId = process.env.GOOGLE_SHEETS_SPREADSHEET_ID;
 14  
 15  if (!spreadsheetId) {
 16    console.error('GOOGLE_SHEETS_SPREADSHEET_ID not set in .env');
 17    process.exit(1);
 18  }
 19  
 20  const auth = new google.auth.JWT({
 21    email: process.env.GOOGLE_SHEETS_CLIENT_EMAIL,
 22    key: process.env.GOOGLE_SHEETS_PRIVATE_KEY?.replace(/\\n/g, '\n'),
 23    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
 24  });
 25  const sheets = google.sheets({ version: 'v4', auth });
 26  const db = createDatabaseConnection(dbPath);
 27  
 28  try {
 29    const spreadsheet = await sheets.spreadsheets.get({ spreadsheetId });
 30    const sheetName = spreadsheet.data.sheets[0].properties.title;
 31    const tabId = spreadsheet.data.sheets[0].properties.sheetId;
 32  
 33    const outreaches = db
 34      .prepare(
 35        `SELECT o.id, s.domain, s.keyword,
 36          COALESCE(s.country_code, 'AU') as country_code,
 37          COALESCE(s.google_domain, 'google.com.au') as google_domain,
 38          o.contact_method, o.contact_uri, o.subject_line, o.proposal_text
 39        FROM outreaches o JOIN sites s ON o.site_id = s.id
 40        WHERE o.status = 'pending'
 41        ORDER BY o.id ASC`
 42      )
 43      .all();
 44  
 45    console.log(`Found ${outreaches.length} pending outreaches`);
 46  
 47    const headers = [
 48      'ID',
 49      'Domain',
 50      'Keyword',
 51      'Country',
 52      'Google Domain',
 53      'Method',
 54      'Contact',
 55      'Subject',
 56      'Proposal Text',
 57      'Action',
 58      'Rework Instructions',
 59      'Processed',
 60    ];
 61  
 62    const rows = outreaches.map(o => [
 63      o.id,
 64      o.domain,
 65      o.keyword,
 66      o.country_code,
 67      o.google_domain,
 68      o.contact_method,
 69      o.contact_uri,
 70      o.subject_line || '',
 71      o.proposal_text,
 72      '',
 73      '',
 74      '',
 75    ]);
 76  
 77    // Clear and rewrite
 78    await sheets.spreadsheets.values.clear({ spreadsheetId, range: sheetName });
 79    await sheets.spreadsheets.values.update({
 80      spreadsheetId,
 81      range: `${sheetName}!A1`,
 82      valueInputOption: 'RAW',
 83      requestBody: { values: [headers, ...rows] },
 84    });
 85  
 86    // Format sheet
 87    await sheets.spreadsheets.batchUpdate({
 88      spreadsheetId,
 89      requestBody: {
 90        requests: [
 91          // Header styling
 92          {
 93            repeatCell: {
 94              range: { sheetId: tabId, startRowIndex: 0, endRowIndex: 1 },
 95              cell: {
 96                userEnteredFormat: {
 97                  backgroundColor: { red: 0.2, green: 0.2, blue: 0.2 },
 98                  textFormat: { foregroundColor: { red: 1, green: 1, blue: 1 }, bold: true },
 99                  horizontalAlignment: 'CENTER',
100                },
101              },
102              fields: 'userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)',
103            },
104          },
105          // Widen Proposal Text (col I, index 8)
106          {
107            updateDimensionProperties: {
108              range: { sheetId: tabId, dimension: 'COLUMNS', startIndex: 8, endIndex: 9 },
109              properties: { pixelSize: 400 },
110              fields: 'pixelSize',
111            },
112          },
113          // Wrap Proposal Text
114          {
115            repeatCell: {
116              range: { sheetId: tabId, startColumnIndex: 8, endColumnIndex: 9, startRowIndex: 1 },
117              cell: { userEnteredFormat: { wrapStrategy: 'WRAP' } },
118              fields: 'userEnteredFormat.wrapStrategy',
119            },
120          },
121          // Action dropdown (col J, index 9)
122          {
123            setDataValidation: {
124              range: {
125                sheetId: tabId,
126                startColumnIndex: 9,
127                endColumnIndex: 10,
128                startRowIndex: 1,
129                endRowIndex: rows.length + 1,
130              },
131              rule: {
132                condition: {
133                  type: 'ONE_OF_LIST',
134                  values: [
135                    { userEnteredValue: 'approve' },
136                    { userEnteredValue: 'rework' },
137                    { userEnteredValue: 'reject' },
138                  ],
139                },
140                showCustomUi: true,
141                strict: true,
142              },
143            },
144          },
145          // Green for approve
146          {
147            addConditionalFormatRule: {
148              rule: {
149                ranges: [
150                  { sheetId: tabId, startColumnIndex: 9, endColumnIndex: 10, startRowIndex: 1 },
151                ],
152                booleanRule: {
153                  condition: { type: 'TEXT_EQ', values: [{ userEnteredValue: 'approve' }] },
154                  format: { backgroundColor: { red: 0.7, green: 1, blue: 0.7 } },
155                },
156              },
157              index: 0,
158            },
159          },
160          // Yellow for rework
161          {
162            addConditionalFormatRule: {
163              rule: {
164                ranges: [
165                  { sheetId: tabId, startColumnIndex: 9, endColumnIndex: 10, startRowIndex: 1 },
166                ],
167                booleanRule: {
168                  condition: { type: 'TEXT_EQ', values: [{ userEnteredValue: 'rework' }] },
169                  format: { backgroundColor: { red: 1, green: 1, blue: 0.7 } },
170                },
171              },
172              index: 1,
173            },
174          },
175          // Red for reject
176          {
177            addConditionalFormatRule: {
178              rule: {
179                ranges: [
180                  { sheetId: tabId, startColumnIndex: 9, endColumnIndex: 10, startRowIndex: 1 },
181                ],
182                booleanRule: {
183                  condition: { type: 'TEXT_EQ', values: [{ userEnteredValue: 'reject' }] },
184                  format: { backgroundColor: { red: 1, green: 0.7, blue: 0.7 } },
185                },
186              },
187              index: 2,
188            },
189          },
190        ],
191      },
192    });
193  
194    // Mark all as exported
195    const stmt = db.prepare("UPDATE outreaches SET exported_at = datetime('now') WHERE id = ?");
196    const tx = db.transaction(ids => {
197      for (const id of ids) stmt.run(id);
198    });
199    tx(outreaches.map(o => o.id));
200  
201    console.log(`Done! Sheet updated with ${rows.length} rows and new 12-column layout.`);
202  } finally {
203    db.close();
204  }