#!/usr/bin/env python3
"""Read-only Analit period comparison anchored to an existing KPC snapshot."""
import argparse
from datetime import date, datetime, timedelta, timezone
import hashlib
import json
from pathlib import Path
import sqlite3
import sys

FAMILY = Path.home() / '.agents/skills/keywords-performance-control-setup/lib'
sys.path.insert(0, str(FAMILY))
import ads_client
import config

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument('--project', type=Path, required=True)
    parser.add_argument('--output', type=Path, required=True)
    args = parser.parse_args()
    cfg = config.load_config(args.project / 'ads-control/config.yaml')
    if str(cfg['google_ads']['customer_id']) != '1938291268':
        raise SystemExit('Analit KPC requires customer 1938291268')
    credentials = json.loads(Path(cfg['credential_source']).expanduser().read_text())
    credentials['login_customer_id'] = '9494412337'
    client = ads_client.ReadOnlyAdsClient.from_config(cfg, credentials)
    receipts = []
    def pull(route, query, start=None, end=None):
        rows = list(client.search(query))
        receipts.append(dict(route=route, query_sha256=hashlib.sha256(query.encode()).hexdigest(),
                             row_count=len(rows), start=start, end=end,
                             queried_at=datetime.now(timezone.utc).isoformat()))
        return rows
    row = pull('identity', 'SELECT customer.id, customer.descriptive_name, customer.currency_code, customer.time_zone, customer.manager FROM customer LIMIT 1')[0].customer
    identity = dict(customer_id=str(row.id), name=row.descriptive_name, currency=row.currency_code,
                    timezone=row.time_zone, manager=row.manager, login_customer_id='9494412337')
    assert identity['customer_id'] == '1938291268' and identity['currency'] == 'HUF'
    assert identity['timezone'] == 'Europe/Budapest' and not identity['manager']
    db = sqlite3.connect(f"file:{args.project / 'ads-control/data/ads_control.db'}?mode=ro", uri=True)
    db.row_factory = sqlite3.Row
    snap = dict(db.execute('SELECT * FROM snapshots ORDER BY extracted_at DESC, rowid DESC LIMIT 1').fetchone())
    assert snap['customer_id'] == identity['customer_id'], 'Stored snapshot belongs to another customer'
    assert snap['account_timezone'] == identity['timezone'], 'Stored snapshot timezone differs from live account'
    end = date.fromisoformat(snap['window_end'])
    periods = [('latest_30d', end - timedelta(days=29), end),
               ('prior_30d', end - timedelta(days=59), end - timedelta(days=30))]
    result = dict(identity=identity, snapshot=snap, periods={}, receipts=receipts)
    def metrics(r):
        m = r.metrics
        return dict(spend_micros=int(m.cost_micros), impressions=int(m.impressions),
                    clicks=int(m.clicks), conversions=float(m.conversions),
                    all_conversions=float(m.all_conversions))
    for name, start, finish in periods:
        start, finish = start.isoformat(), finish.isoformat()
        where = f" WHERE segments.date BETWEEN '{start}' AND '{finish}'"
        fields = 'metrics.cost_micros, metrics.impressions, metrics.clicks, metrics.conversions, metrics.all_conversions'
        rows = pull(name + '_account', 'SELECT ' + fields + ' FROM customer' + where, start, finish)
        account = metrics(rows[0]) if rows else None
        rows = pull(name + '_campaign', 'SELECT campaign.id, campaign.name, campaign.advertising_channel_type, campaign.status, ' + fields + ' FROM campaign' + where, start, finish)
        campaigns = [dict(campaign_id=str(r.campaign.id), name=r.campaign.name,
                          type=r.campaign.advertising_channel_type.name, status=r.campaign.status.name,
                          **metrics(r)) for r in rows]
        assert account is not None, 'Account aggregation unavailable, never replace with zero'
        for key in ['spend_micros', 'clicks', 'impressions']:
            assert account[key] == sum(r[key] for r in campaigns), (name, key)
        rows = pull(name + '_conversion_actions', 'SELECT segments.conversion_action_name, metrics.conversions, metrics.all_conversions FROM customer' + where, start, finish)
        actions = [dict(name=r.segments.conversion_action_name, conversions=float(r.metrics.conversions),
                        all_conversions=float(r.metrics.all_conversions)) for r in rows]
        keywords = [dict(r) for r in db.execute('''SELECT k.criterion_id, k.campaign_id, k.campaign_name,
            k.ad_group_name, k.keyword_text, k.match_type, k.delivery_status, k.landing_page,
            COALESCE(SUM(d.spend_micros),0) spend_micros, COALESCE(SUM(d.clicks),0) clicks,
            COALESCE(SUM(d.impressions),0) impressions FROM keyword_performance k
            LEFT JOIN daily_performance_segments d ON d.snapshot_id=k.snapshot_id
            AND d.criterion_id=k.criterion_id AND d.date BETWEEN ? AND ?
            WHERE k.snapshot_id=? GROUP BY k.criterion_id ORDER BY spend_micros DESC''',
            (start, finish, snap['snapshot_id']))]
        keyword_totals = {k: sum(r[k] for r in keywords) for k in ['spend_micros', 'clicks', 'impressions']}
        result['periods'][name] = dict(start=start, end=finish, account=account,
            campaigns=campaigns, conversion_actions=actions, keywords=keywords,
            keyword_totals=keyword_totals,
            traffic_outside_keyword_scope={k: account[k] - keyword_totals[k] for k in keyword_totals},
            reconciliation='account equals campaign sums in exact micros, clicks, impressions')
    rows = pull('conversion_action_inventory', 'SELECT conversion_action.id, conversion_action.name, conversion_action.type, conversion_action.category, conversion_action.status, conversion_action.primary_for_goal FROM conversion_action')
    result['conversion_action_inventory'] = [dict(id=str(r.conversion_action.id), name=r.conversion_action.name,
        type=r.conversion_action.type.name, category=r.conversion_action.category.name,
        status=r.conversion_action.status.name, primary_for_goal=r.conversion_action.primary_for_goal) for r in rows]
    rows = pull('quality_score_presence', "SELECT ad_group.id, ad_group_criterion.criterion_id, ad_group_criterion.quality_info.quality_score FROM ad_group_criterion WHERE ad_group_criterion.type = 'KEYWORD' AND ad_group_criterion.negative = FALSE AND campaign.advertising_channel_type = 'SEARCH' AND campaign.status IN ('ENABLED', 'PAUSED') AND ad_group.status IN ('ENABLED', 'PAUSED') AND ad_group_criterion.status IN ('ENABLED', 'PAUSED')")
    result['quality_score_presence'] = [dict(criterion_id=f'{r.ad_group.id}~{r.ad_group_criterion.criterion_id}',
        retrieved_value=r.ad_group_criterion.quality_info.quality_score,
        field_present=r.ad_group_criterion.quality_info._pb.HasField('quality_score')) for r in rows]
    assert {r['criterion_id'] for r in result['quality_score_presence']} == {k['criterion_id'] for k in result['periods']['latest_30d']['keywords']}
    args.output.parent.mkdir(parents=True, exist_ok=True)
    args.output.write_text(json.dumps(result, ensure_ascii=False, indent=2))
    print(json.dumps(dict(snapshot_id=snap['snapshot_id'], output=str(args.output),
        periods={k: dict(start=v['start'], end=v['end'], account=v['account']) for k,v in result['periods'].items()})))

if __name__ == '__main__':
    main()