import json, subprocess, sys
exec(open('api_test.py').read().split("print('Schema')")[0])
def q1(x): return sql(x)
real_before = q1("SELECT COUNT(*) FROM agents WHERE workspace_id=1 AND (agent_code IS NULL OR agent_code NOT LIKE 'DEMO%')")
c, j = call('POST', '/settings/sample-data', {'action': 'load'})
ok(c == 200 and j['agents'] == 20 and j['leads'] > 1000, 'load: 20 agents with their leads', j)
D = "FROM agents WHERE workspace_id=1 AND agent_code LIKE 'DEMO%'"
ok(q1("SELECT COUNT(*) " + D) == '20', 'exactly 20 sample agents (any earlier samples replaced)')
ok(q1("SELECT COUNT(*) " + D + " AND (birthdate IS NULL OR npn IS NULL OR story_from IS NULL OR story_grew_up IS NULL OR story_before IS NULL OR availability IS NULL OR start_date IS NULL)") == '0', 'every agent: birthday, NPN, start date, full back story, office hours')
ok(int(q1("SELECT COUNT(*) " + D + " AND children_info IS NOT NULL")) >= 8 and int(q1("SELECT COUNT(*) " + D + " AND pets_info IS NOT NULL")) >= 8 and int(q1("SELECT COUNT(*) " + D + " AND married=1")) >= 6, 'families: spouses, children and pets across the agency')
ids = q1("SELECT GROUP_CONCAT(id) " + D)
for t, mn in [('dreams', 4), ('goals', 4), ('licenses', 1), ('appointments', 2)]:
    ok(q1(f"SELECT COUNT(*) FROM agents a WHERE a.id IN ({ids}) AND (SELECT COUNT(*) FROM {t} x WHERE x.agent_id=a.id) < {mn}") == '0', f'every agent has {mn}+ {t}')
ok(q1(f"SELECT COUNT(DISTINCT status) FROM agents WHERE id IN ({ids})") >= '4', 'agents spread across the stages')
ok(q1(f"SELECT COUNT(*) FROM agents a WHERE a.id IN ({ids}) AND (SELECT COUNT(DISTINCT pipeline_id) FROM cards c WHERE c.agent_id=a.id) < 6") == '0', 'every agent has leads in all six pipelines')
ok(int(q1(f"SELECT COUNT(DISTINCT stage_id) FROM cards WHERE agent_id IN ({ids})")) >= 30, 'leads in (nearly) every stage', q1(f"SELECT COUNT(DISTINCT stage_id) FROM cards WHERE agent_id IN ({ids})"))
# every card field filled, per pipeline spec
spec = {'top100': ['rating','relationship','married','occupation','age','children','best_time'], 'recruits': ['married','occupation','age','children','time_zone','appt_date','qualities','objections'],
        'trainings': ['married','occupation','age','children','homeowner','time_zone','referred_by','appt_date','result'], 'partners': ['state','married','occupation','age','recruiter','trainer','npn','start_date','exam_date','licensed'],
        'submitted': ['carrier','product','policy_number','submitted_date','issued_date'], 'delivered': ['carrier','product','policy_number','submitted_date','issued_date','delivered_date','paid_date']}
for pk, keys in spec.items():
    miss = q1(f"SELECT COUNT(*) FROM cards c JOIN pipelines p ON p.id=c.pipeline_id WHERE c.agent_id IN ({ids}) AND p.pkey='{pk}' AND (" + ' OR '.join(f"JSON_EXTRACT(c.fields,'$.{k}') IS NULL" for k in keys) + ")")
    ok(miss == '0', f'{pk}: every card has every field filled', miss)
ok(q1(f"SELECT COUNT(*) FROM cards WHERE agent_id IN ({ids}) AND (phone IS NULL OR email IS NULL OR location IS NULL OR notes IS NULL)") == '0', 'every lead has phone, email, city and background notes')
kinds = q1(f"SELECT GROUP_CONCAT(DISTINCT n.kind ORDER BY n.kind) FROM card_notes n JOIN cards c ON c.id=n.card_id WHERE c.agent_id IN ({ids})")
ok(sorted(kinds.split(',')) == ['call','email','meeting','move','note','text'], 'activity: notes, calls, texts, emails, meetings and stage moves', kinds)
span = q1(f"SELECT DATEDIFF(MAX(n.created_at), MIN(n.created_at)) FROM card_notes n JOIN cards c ON c.id=n.card_id WHERE c.agent_id IN ({ids})")
ok(int(span) >= 50, 'activity spread over the last 60 days', span)
fu = q1(f"SELECT CONCAT(SUM(follow_up_at < UTC_TIMESTAMP()), ',', SUM(follow_up_at > DATE_ADD(UTC_TIMESTAMP(), INTERVAL 2 DAY))) FROM cards WHERE agent_id IN ({ids})").split(',')
ok(int(fu[0]) > 50 and int(fu[1]) > 50, 'follow-ups overdue and upcoming', fu)
days = q1(f"SELECT MIN(n) FROM (SELECT COUNT(DISTINCT stat_date) n FROM daily_stats WHERE agent_id IN ({ids}) AND tracker='production' GROUP BY agent_id) x")
ok(int(days) >= 30, 'every agent has 60 days of production numbers (weekdays)', days)
ok(q1(f"SELECT MIN(stat_date) >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) FROM daily_stats WHERE agent_id IN ({ids})") == '1' and q1(f"SELECT SUM(issued_paid) > 0 AND SUM(points_submitted) > 0 FROM daily_stats WHERE agent_id IN ({ids})") == '1', 'calls, appointments, submitted points and paid sales within the last 60 days')
ok(int(q1(f"SELECT COUNT(DISTINCT manager_agent_id) FROM team_members WHERE manager_agent_id IN ({ids})")) == 4, 'four team leaders with downlines')
c, j = call('GET', '/team'); ok(c == 200, 'All agents loads with the sample agency')
aid0 = ids.split(',')[0]
for path in [f'/agents/{aid0}', f'/agents/{aid0}/cards?pipeline=top100', f'/agents/{aid0}/follow-ups', f'/agents/{aid0}/cards/summary']:
    c, j = call('GET', path); ok(c == 200, 'loads ' + path, j if c != 200 else '')
c, j = call('POST', '/settings/sample-data', {'action': 'clear'})
ok(j.get('removed') == 20 and q1("SELECT COUNT(*) " + D) == '0', 'remove: all 20 gone')
ok(q1("SELECT COUNT(*) FROM agents WHERE workspace_id=1 AND (agent_code IS NULL OR agent_code NOT LIKE 'DEMO%')") == real_before, 'real agents untouched')
ok(q1(f"SELECT COUNT(*) FROM cards WHERE agent_id IN ({ids})") == '0' and q1(f"SELECT COUNT(*) FROM daily_stats WHERE agent_id IN ({ids})") == '0', 'their leads and stats removed too')
c, j = call('POST', '/settings/sample-data', {'action': 'load'}); ok(j.get('agents') == 20, 'load again works')
print(f"\n{sum(R)} passed, {len(R) - sum(R)} failed"); sys.exit(0 if all(R) else 1)