<?php
declare(strict_types=1);

/**
 * Demo records so every Staff Portal section has multiple rows to check.
 * Expected: $pdo (PDO), $hash (password hash for Welcome1!).
 */
/** @var PDO $pdo */
/** @var string $hash */

$pdo->exec("INSERT INTO roles (id, code, name, description) VALUES
(1,'salesperson','Salesperson','Daily sales: customers, leads, deals, tasks, calendar, email'),
(2,'manager','Manager','Team pipeline, forecast, late work, lead assignment'),
(3,'admin','Admin','Users, roles, industry setup, email tools'),
(4,'marketing','Marketing person','Campaigns, lists, templates, unsubscribes, campaign reports'),
(5,'service','Service person','Customer pages, visit fields, notes after a visit')");

$pdo->prepare('INSERT INTO tenants (id, name, slug, industry_code, status) VALUES (1,?,?,?,?)')
    ->execute(['AOSWARE Demo', 'aosware-demo', 'distribution', 'active']);

$insU = $pdo->prepare('INSERT INTO users (id,email,password_hash,first_name,last_name,phone,status) VALUES (?,?,?,?,?,?,?)');
$users = [
    [1, 'john.manager@aosware.local', $hash, 'John', 'Smith', '555-0101', 'active'],
    [2, 'jane.rep@aosware.local', $hash, 'Jane', 'Doe', '555-0102', 'active'],
    [3, 'admin@aosware.local', $hash, 'Alex', 'Admin', '555-0100', 'active'],
    [4, 'sam.service@aosware.local', $hash, 'Sam', 'Lee', '555-0103', 'active'],
    [5, 'priya.patel@aosware.local', $hash, 'Priya', 'Patel', '555-0104', 'active'],
    [6, 'marcus.webb@aosware.local', $hash, 'Marcus', 'Webb', '555-0105', 'active'],
    [7, 'maya.chen@aosware.local', $hash, 'Maya', 'Chen', '555-0106', 'active'],
    [8, 'owen.blake@aosware.local', $hash, 'Owen', 'Blake', '555-0107', 'inactive'],
];
foreach ($users as $u) {
    $insU->execute($u);
}

$pdo->exec('INSERT INTO tenant_users (tenant_id, user_id, status) VALUES
(1,1,"active"),(1,2,"active"),(1,3,"active"),(1,4,"active"),
(1,5,"active"),(1,6,"active"),(1,7,"active"),(1,8,"inactive")');

$pdo->exec('INSERT INTO user_roles (tenant_id, user_id, role_id) VALUES
(1,1,1),(1,1,2),(1,2,1),(1,3,3),(1,4,5),(1,5,1),(1,6,1),(1,7,4),(1,8,1)');

$pdo->exec("INSERT INTO territories (id, tenant_id, name) VALUES (1,1,'East'),(2,1,'West'),(3,1,'Central')");
$pdo->exec('INSERT INTO territory_users (tenant_id, territory_id, user_id) VALUES
(1,1,1),(1,1,2),(1,2,2),(1,2,5),(1,3,6),(1,1,4)');

$pdo->exec("INSERT INTO pipeline_stages (id, tenant_id, name, code, sort_order, probability, is_won, is_lost) VALUES
(1,1,'Lead','lead',1,10,0,0),
(2,1,'Qualified Account','good_account',2,25,0,0),
(3,1,'Quote','quote',3,50,0,0),
(4,1,'First Order','first_order',4,75,0,0),
(5,1,'Active Customer','active_customer',5,100,1,0),
(6,1,'Reorder / Growth','reorder',6,100,1,0),
(7,1,'Closed Lost','closed_lost',7,0,0,1)");

$dt = static function (int $days, string $time = '09:00:00'): string {
    return date('Y-m-d ', strtotime(sprintf('%+d days', $days))) . $time;
};
$day = static function (int $dom, string $time = '10:00:00'): string {
    $y = (int) date('Y');
    $m = (int) date('n');
    $last = (int) date('t');
    $d = max(1, min($dom, $last));
    return sprintf('%04d-%02d-%02d %s', $y, $m, $d, $time);
};
$close = static function (int $days): string {
    return date('Y-m-d', strtotime(sprintf('%+d days', $days)));
};
$inMonth = static function (int $daysAgo, string $time = '10:00:00'): string {
    $dom = max(1, (int) date('j') - $daysAgo);
    return sprintf('%s-%02d %s', date('Y-m'), $dom, $time);
};
$closeInMonth = static function (int $daysAgo): string {
    $dom = max(1, (int) date('j') - $daysAgo);
    return sprintf('%s-%02d', date('Y-m'), $dom);
};

// owner: 1 John, 2 Jane, 4 Sam, 5 Priya, 6 Marcus
// Jane owns 16 so her Accounts list paginates (15 per page).
$accounts = [
    [1, 'ABC Office Supplies', '555-1001', 'buying@abcoffice.example', 'https://abcoffice.example', 'Distribution', 1, 1, 'active', '120 Market St', 'Boston', 'MA', '02108', 'Follow up on furniture upgrade', $dt(1, '10:00:00')],
    [2, 'Acme Corporation', '555-1002', 'purchasing@acme.example', 'https://acme.example', 'Manufacturing', 1, 2, 'active', '88 Industrial Way', 'Newark', 'NJ', '07102', 'Send proposal', $dt(2, '14:00:00')],
    [3, 'Global Industrial', '555-1003', 'orders@globalind.example', 'https://globalind.example', 'MRO', 2, 1, 'active', '410 Plant Rd', 'Chicago', 'IL', '60607', 'Contract review', $dt(3, '09:00:00')],
    [4, 'Tech Solutions Inc.', '555-1004', 'hello@techsolutions.example', 'https://techsolutions.example', 'Software', 3, 2, 'active', '900 Congress Ave', 'Austin', 'TX', '78701', 'Demo follow-up', $dt(4, '11:00:00')],
    [5, 'Bright Retailers', '555-1005', 'ops@bright.example', 'https://bright.example', 'Retail', 2, 1, 'active', '55 Colfax Ave', 'Denver', 'CO', '80202', 'Send quote', $dt(5, '16:00:00')],
    [6, 'Northwind Traders', '555-1006', 'buy@northwind.example', 'https://northwind.example', 'Wholesale', 1, 2, 'active', '12 Harbor Blvd', 'Portland', 'ME', '04101', 'Catalog review', $dt(1, '13:00:00')],
    [7, 'Blue Harbor Foods', '555-1007', 'ops@blueharbor.example', 'https://blueharbor.example', 'Foodservice', 1, 2, 'active', '40 Pier St', 'Providence', 'RI', '02903', 'Site visit', $dt(-1, '10:00:00')],
    [8, 'Cedar Medical Group', '555-1008', 'admin@cedarmed.example', 'https://cedarmed.example', 'Healthcare', 1, 2, 'active', '210 Clinic Dr', 'Hartford', 'CT', '06103', 'Intro call', $dt(2, '09:30:00')],
    [9, 'Lakeside Hotels', '555-1009', 'gm@lakesidehotels.example', 'https://lakesidehotels.example', 'Hospitality', 1, 2, 'active', '1 Lakeview Rd', 'Burlington', 'VT', '05401', 'Renewal talk', $dt(6, '15:00:00')],
    [10, 'Pioneer Logistics', '555-1010', 'fleet@pioneerlog.example', 'https://pioneerlog.example', 'Logistics', 1, 2, 'active', '800 Dock Rd', 'Philadelphia', 'PA', '19148', 'Warehouse walkthrough', $dt(0, '11:00:00')],
    [11, 'Summit Athletics', '555-1011', 'coach@summitath.example', 'https://summitath.example', 'Recreation', 1, 2, 'active', '77 Stadium Way', 'Albany', 'NY', '12207', 'Budget check', $dt(7, '10:00:00')],
    [12, 'Oakridge Schools', '555-1012', 'purchasing@oakridgek12.example', 'https://oakridgek12.example', 'Education', 1, 2, 'active', '15 District Ave', 'Worcester', 'MA', '01608', 'Board presentation', $dt(8, '14:00:00')],
    [13, 'Metro Print Shop', '555-1013', 'hello@metroprint.example', 'https://metroprint.example', 'Print', 1, 2, 'active', '9 Press Lane', 'New Haven', 'CT', '06510', 'Supplies reorder', $dt(-2, '16:00:00')],
    [14, 'Horizon Insurance', '555-1014', 'ops@horizonins.example', 'https://horizonins.example', 'Insurance', 1, 2, 'active', '300 State St', 'Trenton', 'NJ', '08608', 'Office refresh quote', $dt(3, '11:30:00')],
    [15, 'Pacific Dental', '555-1015', 'office@pacificdental.example', 'https://pacificdental.example', 'Healthcare', 1, 2, 'active', '44 Smile Ave', 'Stamford', 'CT', '06901', 'Equipment demo', $dt(4, '09:00:00')],
    [16, 'GreenLeaf Organics', '555-1016', 'buy@greenleaf.example', 'https://greenleaf.example', 'Retail', 1, 2, 'active', '18 Farm Market', 'Manchester', 'NH', '03101', 'Seasonal order', $dt(9, '13:00:00')],
    [17, 'Atlas Construction', '555-1017', 'estimating@atlascon.example', 'https://atlascon.example', 'Construction', 1, 2, 'active', '5 Crane Rd', 'Springfield', 'MA', '01103', 'Job-site visit', $dt(2, '08:00:00')],
    [18, 'Beacon Financial', '555-1018', 'facilities@beaconfin.example', 'https://beaconfin.example', 'Finance', 1, 2, 'active', '1 Federal St', 'Boston', 'MA', '02110', 'Floor plan review', $dt(5, '10:00:00')],
    [41, 'Silverline Apparel', '555-1041', 'buy@silverline.example', 'https://silverline.example', 'Retail', 1, 2, 'active', '22 Fashion Ave', 'Boston', 'MA', '02111', 'Showroom appointment', $dt(4, '15:00:00')],
    [19, 'Harbor Freight Partners', '555-1019', 'ops@harborfp.example', 'https://harborfp.example', 'Distribution', 1, 1, 'active', '70 Wharf St', 'Baltimore', 'MD', '21202', 'Price match', $dt(1, '15:00:00')],
    [20, 'City Hall Supplies', '555-1020', 'procurement@cityhall.example', 'https://cityhall.example', 'Government', 1, 1, 'active', '1 Government Ctr', 'Boston', 'MA', '02201', 'Bid follow-up', $dt(4, '09:00:00')],
    [21, 'Evergreen University', '555-1021', 'facilities@evergreenu.example', 'https://evergreenu.example', 'Education', 1, 1, 'active', '400 Campus Dr', 'Amherst', 'MA', '01002', 'Campus walk', $dt(6, '13:00:00')],
    [22, 'Redwood Clinics', '555-1022', 'admin@redwoodclinics.example', 'https://redwoodclinics.example', 'Healthcare', 1, 1, 'active', '88 Care Way', 'Portland', 'ME', '04102', 'Multi-site quote', $dt(-1, '11:00:00')],
    [23, 'Midwest Packaging', '555-1023', 'buy@midwestpack.example', 'https://midwestpack.example', 'Manufacturing', 3, 1, 'active', '250 Box St', 'Cleveland', 'OH', '44113', 'Line trial', $dt(8, '10:00:00')],
    [24, 'West Coast Grocers', '555-1024', 'dc@westcoastgrocers.example', 'https://westcoastgrocers.example', 'Retail', 2, 5, 'active', '900 Market St', 'San Francisco', 'CA', '94103', 'DC tour', $dt(2, '14:00:00')],
    [25, 'Cascade Coffee Co', '555-1025', 'ops@cascadecoffee.example', 'https://cascadecoffee.example', 'Foodservice', 2, 5, 'active', '12 Roast Ave', 'Seattle', 'WA', '98101', 'Tasting + proposal', $dt(3, '09:00:00')],
    [26, 'Sierra Auto Group', '555-1026', 'parts@sierraauto.example', 'https://sierraauto.example', 'Automotive', 2, 5, 'active', '400 Dealer Row', 'Sacramento', 'CA', '95814', 'Shop supplies', $dt(0, '16:00:00')],
    [27, 'Golden State Labs', '555-1027', 'lab@gslabs.example', 'https://gslabs.example', 'Healthcare', 2, 5, 'active', '77 Science Park', 'San Diego', 'CA', '92121', 'Compliance pack', $dt(7, '11:00:00')],
    [28, 'Bayview Property Mgmt', '555-1028', 'ops@bayviewpm.example', 'https://bayviewpm.example', 'Real estate', 2, 5, 'active', '5 Embarcadero', 'Oakland', 'CA', '94607', 'Portfolio quote', $dt(5, '10:00:00')],
    [29, 'Napa Hospitality', '555-1029', 'gm@napahosp.example', 'https://napahosp.example', 'Hospitality', 2, 5, 'active', '1 Vineyard Rd', 'Napa', 'CA', '94559', 'Seasonal kit', $dt(9, '15:00:00')],
    [30, 'Heartland Farms', '555-1030', 'ops@heartlandfarms.example', 'https://heartlandfarms.example', 'Agriculture', 3, 6, 'active', '80 County Rd', 'Des Moines', 'IA', '50309', 'Harvest plan', $dt(2, '08:30:00')],
    [31, 'Plains Manufacturing', '555-1031', 'buy@plainsmfg.example', 'https://plainsmfg.example', 'Manufacturing', 3, 6, 'active', '600 Factory Blvd', 'Kansas City', 'MO', '64105', 'Safety audit', $dt(4, '13:00:00')],
    [32, 'Capitol Office Group', '555-1032', 'admin@capitoloffice.example', 'https://capitoloffice.example', 'Distribution', 3, 6, 'active', '10 Capitol Mall', 'St. Paul', 'MN', '55101', 'State contract', $dt(1, '11:00:00')],
    [33, 'Twin Cities Retail', '555-1033', 'ops@twincitiesretail.example', 'https://twincitiesretail.example', 'Retail', 3, 6, 'active', '50 Nicollet', 'Minneapolis', 'MN', '55401', 'Store reset', $dt(6, '16:00:00')],
    [34, 'Great Lakes MRO', '555-1034', 'purchasing@glmro.example', 'https://glmro.example', 'MRO', 3, 6, 'active', '22 Dockside', 'Milwaukee', 'WI', '53202', 'Blanket PO', $dt(-3, '09:00:00')],
    [35, 'Prairie Health System', '555-1035', 'facilities@prairiehealth.example', 'https://prairiehealth.example', 'Healthcare', 3, 6, 'active', '1 Wellness Pkwy', 'Omaha', 'NE', '68102', 'Multi-campus bid', $dt(8, '10:00:00')],
    [36, 'CopierCare Downtown', '555-1036', 'service@copiercare.example', 'https://copiercare.example', 'Service', 1, 4, 'active', '33 Copy St', 'Boston', 'MA', '02111', 'Meter read visit', $dt(1, '09:00:00')],
    [37, 'QuickPrint Service Hub', '555-1037', 'desk@quickprint.example', 'https://quickprint.example', 'Service', 1, 4, 'active', '8 Binder Ln', 'Cambridge', 'MA', '02139', 'Preventive maintenance', $dt(0, '14:00:00')],
    [38, 'Campus Facilities West', '555-1038', 'plant@campuswest.example', 'https://campuswest.example', 'Education', 2, 4, 'active', '200 Quad', 'Amherst', 'MA', '01003', 'After-hours call', $dt(3, '17:00:00')],
    [39, 'Riverside Medical Devices', '555-1039', 'biomed@riversidemd.example', 'https://riversidemd.example', 'Healthcare', 1, 4, 'active', '15 River Rd', 'Hartford', 'CT', '06106', 'Install check', $dt(2, '10:00:00')],
    [40, 'Inactive Sample Co', '555-1040', 'closed@inactivesample.example', null, 'Retail', 3, 1, 'inactive', '99 Closed Ave', 'Buffalo', 'NY', '14202', null, null],
];

$accStmt = $pdo->prepare('INSERT INTO accounts (id, tenant_id, name, phone, email, website, industry, territory_id, owner_id, status, address_line, city, state, postal_code, country, next_action, next_action_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
foreach ($accounts as $a) {
    $accStmt->execute([
        $a[0], 1, $a[1], $a[2], $a[3], $a[4], $a[5], $a[6], $a[7], $a[8], $a[9], $a[10], $a[11], $a[12], 'USA', $a[13], $a[14],
    ]);
}

$people = [
    [1, 'Mike', 'Johnson', 'mike@abcoffice.example', '555-2001', 'Purchasing Manager', 'Buyer', 1, 1],
    [1, 'Dana', 'Ruiz', 'dana@abcoffice.example', '555-2011', 'Facilities Lead', 'Influencer', 1, 0],
    [1, 'Pat', 'Nguyen', 'pat@abcoffice.example', '555-2021', 'CFO', 'Decision maker', 1, 0],
    [2, 'Sarah', 'Williams', 'sarah@acme.example', '555-2002', 'Director', 'Decision maker', 2, 1],
    [2, 'Eli', 'Brooks', 'eli@acme.example', '555-2012', 'Buyer', 'Buyer', 2, 0],
    [3, 'Tom', 'Davis', 'tom@globalind.example', '555-2003', 'Facilities', 'Influencer', 1, 1],
    [3, 'Nina', 'Kaur', 'nina@globalind.example', '555-2013', 'Plant Manager', 'Decision maker', 1, 0],
    [4, 'Lisa', 'Anderson', 'lisa@techsolutions.example', '555-2004', 'CTO', 'Decision maker', 2, 1],
    [4, 'Owen', 'Park', 'owen@techsolutions.example', '555-2014', 'IT Manager', 'Influencer', 2, 0],
    [5, 'Chris', 'Patel', 'chris@bright.example', '555-2005', 'Store Ops', 'Buyer', 1, 1],
    [6, 'Haley', 'Stone', 'haley@northwind.example', '555-2006', 'Buyer', 'Buyer', 2, 1],
    [6, 'Greg', 'Ito', 'greg@northwind.example', '555-2016', 'Warehouse Manager', 'Influencer', 2, 0],
    [7, 'Marisol', 'Vega', 'marisol@blueharbor.example', '555-2007', 'Chef / Ops', 'Decision maker', 2, 1],
    [8, 'Dr. Ellen', 'Cho', 'ellen@cedarmed.example', '555-2008', 'Practice Manager', 'Decision maker', 2, 1],
    [8, 'Ben', 'Ortiz', 'ben@cedarmed.example', '555-2018', 'Office Admin', 'Buyer', 2, 0],
    [9, 'Claire', 'Hughes', 'claire@lakesidehotels.example', '555-2009', 'General Manager', 'Decision maker', 2, 1],
    [10, 'Andre', 'Walsh', 'andre@pioneerlog.example', '555-2010', 'Fleet Director', 'Decision maker', 2, 1],
    [11, 'Kim', 'Foster', 'kim@summitath.example', '555-2022', 'Athletic Director', 'Decision maker', 2, 1],
    [12, 'Jordan', 'Ellis', 'jordan@oakridgek12.example', '555-2023', 'Business Manager', 'Buyer', 2, 1],
    [13, 'Riley', 'Quinn', 'riley@metroprint.example', '555-2024', 'Owner', 'Decision maker', 2, 1],
    [14, 'Sofia', 'Martinez', 'sofia@horizonins.example', '555-2025', 'Office Manager', 'Buyer', 2, 1],
    [15, 'Dr. Alan', 'Singh', 'alan@pacificdental.example', '555-2026', 'Owner', 'Decision maker', 2, 1],
    [16, 'Nora', 'Green', 'nora@greenleaf.example', '555-2027', 'Buyer', 'Buyer', 2, 1],
    [17, 'Tyler', 'Brooks', 'tyler@atlascon.example', '555-2028', 'Estimator', 'Influencer', 2, 1],
    [18, 'Amina', 'Hassan', 'amina@beaconfin.example', '555-2029', 'Facilities', 'Buyer', 2, 1],
    [41, 'Lena', 'Cho', 'lena@silverline.example', '555-2052', 'Buyer', 'Buyer', 2, 1],
    [19, 'Carl', 'Diaz', 'carl@harborfp.example', '555-2030', 'Ops Manager', 'Decision maker', 1, 1],
    [20, 'Helen', 'Wu', 'helen@cityhall.example', '555-2031', 'Procurement', 'Buyer', 1, 1],
    [21, 'Prof. Ian', 'Cole', 'ian@evergreenu.example', '555-2032', 'Facilities VP', 'Decision maker', 1, 1],
    [22, 'Maya', 'Grant', 'maya@redwoodclinics.example', '555-2033', 'Admin Director', 'Decision maker', 1, 1],
    [23, 'Vince', 'Keller', 'vince@midwestpack.example', '555-2034', 'Plant Buyer', 'Buyer', 1, 1],
    [24, 'Priya', 'Shah', 'pshah@westcoastgrocers.example', '555-2035', 'DC Manager', 'Decision maker', 5, 1],
    [24, 'Leo', 'Nguyen', 'leo@westcoastgrocers.example', '555-2045', 'Buyer', 'Buyer', 5, 0],
    [25, 'Jess', 'Morales', 'jess@cascadecoffee.example', '555-2036', 'Owner', 'Decision maker', 5, 1],
    [26, 'Hank', 'Reed', 'hank@sierraauto.example', '555-2037', 'Parts Manager', 'Buyer', 5, 1],
    [27, 'Dr. Priya', 'Mehta', 'priya.m@gslabs.example', '555-2038', 'Lab Director', 'Decision maker', 5, 1],
    [28, 'Olivia', 'Chen', 'olivia@bayviewpm.example', '555-2039', 'Portfolio Manager', 'Decision maker', 5, 1],
    [29, 'Marco', 'Rossi', 'marco@napahosp.example', '555-2040', 'GM', 'Decision maker', 5, 1],
    [30, 'Dale', 'Hansen', 'dale@heartlandfarms.example', '555-2041', 'Ops', 'Buyer', 6, 1],
    [31, 'Ruth', 'Okoye', 'ruth@plainsmfg.example', '555-2042', 'Purchasing', 'Buyer', 6, 1],
    [32, 'Neil', 'Baker', 'neil@capitoloffice.example', '555-2043', 'Director', 'Decision maker', 6, 1],
    [33, 'Ivy', 'Larson', 'ivy@twincitiesretail.example', '555-2044', 'Ops', 'Influencer', 6, 1],
    [34, 'Stan', 'Miller', 'stan@glmro.example', '555-2046', 'Buyer', 'Buyer', 6, 1],
    [35, 'Dr. Hope', 'Adams', 'hope@prairiehealth.example', '555-2047', 'Facilities VP', 'Decision maker', 6, 1],
    [36, 'Sam', 'Lee', 'sam.site@copiercare.example', '555-2048', 'Site Contact', 'Buyer', 4, 1],
    [36, 'Gina', 'Torres', 'gina@copiercare.example', '555-2058', 'Office Manager', 'Influencer', 4, 0],
    [37, 'Paul', 'Kim', 'paul@quickprint.example', '555-2049', 'Shop Lead', 'Buyer', 4, 1],
    [38, 'Deb', 'ONeil', 'deb@campuswest.example', '555-2050', 'Plant Engineer', 'Decision maker', 4, 1],
    [39, 'Chris', 'Vale', 'chris.v@riversidemd.example', '555-2051', 'Biomed', 'Buyer', 4, 1],
];

$conStmt = $pdo->prepare('INSERT INTO contacts (tenant_id, account_id, first_name, last_name, email, phone, title, role_label, owner_id, is_primary) VALUES (1,?,?,?,?,?,?,?,?,?)');
foreach ($people as $p) {
    $conStmt->execute($p);
}

$leads = [
    // Unassigned new — Lead assignment
    ['Riley', 'Chen', 'Harbor Schools', 'riley@harbor.example', '555-3001', 'Website', 'new', null, 1, $dt(-4)],
    ['Morgan', 'Blake', 'Pine Clinics', 'morgan@pine.example', '555-3002', 'Referral', 'new', null, 2, $dt(-3)],
    ['Jamie', 'Cole', 'Riverbank Cafe', 'jamie@river.example', '555-3004', 'Trade show', 'new', null, 3, $dt(-2)],
    ['Casey', 'Holt', 'Lumen Libraries', 'casey@lumen.example', '555-3005', 'Website', 'new', null, 1, $dt(-2)],
    ['Drew', 'Singh', 'North Pier Marina', 'drew@northpier.example', '555-3006', 'Call', 'new', null, 1, $dt(-1)],
    ['Quinn', 'Adams', 'Maple Daycare', 'quinn@maple.example', '555-3007', 'Referral', 'new', null, 2, $dt(-1)],
    ['Reese', 'Park', 'Ironclad Welding', 'reese@ironclad.example', '555-3008', 'LinkedIn', 'new', null, 3, $dt(0)],
    ['Skyler', 'Jones', 'Coastal Yoga', 'skyler@coastalyoga.example', '555-3009', 'Website', 'new', null, 2, $dt(0)],
    ['Finley', 'Ross', 'BrightPath Tutoring', 'finley@brightpath.example', '555-3010', 'Partner', 'new', null, 1, $dt(-5)],
    ['Harper', 'Diaz', 'Sunset Veterinary', 'harper@sunsetvet.example', '555-3011', 'Trade show', 'new', null, 2, $dt(-6)],
    ['Rowan', 'Lee', 'Civic Theater', 'rowan@civictheater.example', '555-3012', 'Call', 'new', null, 3, $dt(-7)],
    ['Sage', 'Patel', 'Hilltop Bakery', 'sage@hilltop.example', '555-3013', 'Website', 'new', null, 1, $dt(-8)],
    // Jane — enough for two list pages
    ['Avery', 'Ng', 'Summit Legal', 'avery@summit.example', '555-3003', 'Call', 'qualified', 2, 1, $dt(-10)],
    ['Jordan', 'West', 'Harborview CPA', 'jordan@harborviewcpa.example', '555-3014', 'Website', 'new', 2, 1, $dt(-1)],
    ['Taylor', 'Brooks', 'Eastside Pharmacy', 'taylor@eastsidepharm.example', '555-3015', 'Referral', 'new', 2, 1, $dt(-2)],
    ['Cameron', 'Young', 'Beacon Day Spa', 'cameron@beacondayspa.example', '555-3016', 'Trade show', 'new', 2, 1, $dt(-3)],
    ['Alexis', 'Reed', 'Union Credit Union', 'alexis@unioncu.example', '555-3017', 'LinkedIn', 'new', 2, 1, $dt(-4)],
    ['Peyton', 'Clark', 'Lighthouse Realty', 'peyton@lhrealty.example', '555-3018', 'Website', 'new', 2, 1, $dt(-5)],
    ['Sidney', 'Moore', 'Craft & Co', 'sidney@craftco.example', '555-3019', 'Call', 'new', 2, 1, $dt(-6)],
    ['Emerson', 'Hall', 'River Arts Council', 'emerson@riverarts.example', '555-3020', 'Partner', 'new', 2, 1, $dt(-7)],
    ['Parker', 'Allen', 'North End Deli', 'parker@northenddeli.example', '555-3021', 'Website', 'new', 2, 1, $dt(-8)],
    ['Hayden', 'Scott', 'City Bike Shop', 'hayden@citybike.example', '555-3022', 'Referral', 'qualified', 2, 1, $dt(-9)],
    ['Logan', 'King', 'Apex Staffing', 'logan@apexstaff.example', '555-3023', 'LinkedIn', 'qualified', 2, 1, $dt(-11)],
    ['Riley', 'Wright', 'Oak & Iron Furniture', 'riley.w@oakiron.example', '555-3024', 'Trade show', 'qualified', 2, 1, $dt(-12)],
    ['Morgan', 'Hill', 'Tiny Tots Academy', 'morgan.h@tinytots.example', '555-3025', 'Website', 'unqualified', 2, 1, $dt(-13)],
    ['Casey', 'Green', 'QuickStop Fuels', 'casey.g@quickstop.example', '555-3026', 'Call', 'unqualified', 2, 1, $dt(-14)],
    ['Jamie', 'Baker', 'Metro Print Shop', 'jamie.b@metroprint.example', '555-3027', 'Website', 'converted', 2, 1, $dt(-20)],
    ['Chris', 'Nelson', 'Acme Corporation', 'chris.n@acme.example', '555-3028', 'Referral', 'converted', 2, 1, $dt(-25)],
    ['Dana', 'Perez', 'Shoreline Architects', 'dana@shorelinearch.example', '555-3029', 'Website', 'new', 2, 1, $dt(-1)],
    ['Blair', 'Cox', 'Harbor Yoga Loft', 'blair@harboryoga.example', '555-3030', 'Trade show', 'new', 2, 1, $dt(0)],
    // John
    ['Kelly', 'Morgan', 'State College Aux', 'kelly@stateaux.example', '555-3031', 'Website', 'new', 1, 1, $dt(-2)],
    ['Shawn', 'Foster', 'Eastbay Charter', 'shawn@eastbay.example', '555-3032', 'Call', 'qualified', 1, 1, $dt(-8)],
    ['Pat', 'Rivera', 'Harbor Freight Partners', 'pat.r@harborfp.example', '555-3033', 'Partner', 'new', 1, 1, $dt(-1)],
    ['Lynn', 'Hughes', 'City Hall Supplies', 'lynn@cityhall.example', '555-3034', 'Website', 'qualified', 1, 1, $dt(-6)],
    ['Terry', 'Bennett', 'Evergreen University', 'terry@evergreenu.example', '555-3035', 'Referral', 'new', 1, 1, $dt(-3)],
    ['Robin', 'Price', 'Redwood Clinics', 'robin@redwoodclinics.example', '555-3036', 'LinkedIn', 'unqualified', 1, 1, $dt(-15)],
    ['Sam', 'Walsh', 'Midwest Packaging', 'sam.w@midwestpack.example', '555-3037', 'Trade show', 'converted', 1, 3, $dt(-18)],
    ['Alex', 'Murray', 'Bright Retailers', 'alex.m@bright.example', '555-3038', 'Call', 'new', 1, 2, $dt(0)],
    // Priya
    ['Nina', 'Shah', 'West Coast Grocers', 'nina.s@westcoastgrocers.example', '555-3039', 'Website', 'new', 5, 2, $dt(-1)],
    ['Leo', 'Kim', 'Cascade Coffee Co', 'leo.k@cascadecoffee.example', '555-3040', 'Referral', 'qualified', 5, 2, $dt(-5)],
    ['Ava', 'Cruz', 'Sierra Auto Group', 'ava@sierraauto.example', '555-3041', 'Call', 'new', 5, 2, $dt(-2)],
    ['Mia', 'Ortiz', 'Golden State Labs', 'mia@gslabs.example', '555-3042', 'LinkedIn', 'new', 5, 2, $dt(-4)],
    ['Noah', 'Bennett', 'Bayview Property Mgmt', 'noah@bayviewpm.example', '555-3043', 'Website', 'unqualified', 5, 2, $dt(-12)],
    ['Zoe', 'Lin', 'Napa Hospitality', 'zoe@napahosp.example', '555-3044', 'Trade show', 'qualified', 5, 2, $dt(-7)],
    // Marcus
    ['Hank', 'Olsen', 'Heartland Farms', 'hank.o@heartlandfarms.example', '555-3045', 'Website', 'new', 6, 3, $dt(-1)],
    ['Ruth', 'Meyer', 'Plains Manufacturing', 'ruth.m@plainsmfg.example', '555-3046', 'Call', 'qualified', 6, 3, $dt(-9)],
    ['Ivy', 'Novak', 'Capitol Office Group', 'ivy.n@capitoloffice.example', '555-3047', 'Partner', 'new', 6, 3, $dt(-3)],
    ['Stan', 'Keller', 'Great Lakes MRO', 'stan.k@glmro.example', '555-3048', 'Referral', 'new', 6, 3, $dt(-2)],
    ['Hope', 'Andes', 'Prairie Health System', 'hope.a@prairiehealth.example', '555-3049', 'Website', 'qualified', 6, 3, $dt(-6)],
];

$leadStmt = $pdo->prepare('INSERT INTO leads (tenant_id, first_name, last_name, company, email, phone, source, status, owner_id, territory_id, notes, converted_account_id, converted_at, created_at) VALUES (1,?,?,?,?,?,?,?,?,?,?,?,?,?)');
foreach ($leads as $l) {
    $convertedAccount = null;
    $convertedAt = null;
    if ($l[6] === 'converted') {
        $convertedAt = $l[9];
        if (str_contains((string) $l[2], 'Metro Print')) {
            $convertedAccount = 13;
        } elseif (str_contains((string) $l[2], 'Acme')) {
            $convertedAccount = 2;
        } elseif (str_contains((string) $l[2], 'Midwest')) {
            $convertedAccount = 23;
        }
    }
    $notes = $l[6] === 'unqualified' ? 'Not a fit for current catalog.' : ($l[6] === 'qualified' ? 'Budget confirmed. Ready for discovery.' : null);
    $leadStmt->execute([
        $l[0], $l[1], $l[2], $l[3], $l[4], $l[5], $l[6], $l[7], $l[8], $notes, $convertedAccount, $convertedAt, $l[9],
    ]);
}

// stage_id 1 Lead, 2 Qualified Account, 3 Quote, 4 First Order, 5 Active Customer, 6 Reorder / Growth, 7 Lost
$opps = [
    // Jane — 21 deals so list paginates; every open stage has several cards
    [2, 'Breakroom Refresh', 88000, 3, 50, 2, 'Send quote', $dt(-1, '13:00:00'), $close(24)],
    [4, 'Software Implementation', 150000, 4, 75, 2, 'Negotiation meeting', $dt(1, '15:00:00'), $close(19)],
    [6, 'Northwind Catalog Rollout', 64000, 1, 10, 2, 'Qualify budget', $dt(-2, '11:00:00'), $close(30)],
    [7, 'Blue Harbor Kitchen Fit-out', 92000, 2, 25, 2, 'Needs analysis call', $dt(0, '10:00:00'), $close(28)],
    [8, 'Cedar Medical Waiting Room', 41000, 3, 50, 2, 'Send furniture proposal', $dt(-3, '09:00:00'), $close(22)],
    [9, 'Lakeside Hotels Housekeeping', 73000, 4, 75, 2, 'Price discussion', $dt(2, '15:00:00'), $close(18)],
    [10, 'Pioneer Dock Supplies', 56000, 1, 10, 2, 'Intro meeting', $dt(-4, '08:00:00'), $close(35)],
    [11, 'Summit Athletics Locker Refresh', 48000, 2, 25, 2, 'Walk gym', $dt(3, '13:00:00'), $close(40)],
    [12, 'Oakridge Classroom Kits', 125000, 3, 50, 2, 'Board packet', $dt(1, '14:00:00'), $close(45)],
    [13, 'Metro Print Toner Program', 18000, 6, 100, 2, 'Kickoff', $dt(-10, '09:00:00'), $closeInMonth(4)],
    [14, 'Horizon Insurance Floor 3', 67000, 4, 75, 2, 'Legal review', $dt(-1, '11:00:00'), $close(16)],
    [15, 'Pacific Dental Operatory', 54000, 2, 25, 2, 'Clinical walkthrough', $dt(4, '09:00:00'), $close(33)],
    [16, 'GreenLeaf Seasonal Endcaps', 22000, 1, 10, 2, 'Confirm assortment', $dt(5, '13:00:00'), $close(20)],
    [17, 'Atlas Jobsite Trailers', 81000, 3, 50, 2, 'Prepare proposal', $dt(-2, '08:00:00'), $close(26)],
    [18, 'Beacon Financial Reception', 39000, 1, 10, 2, 'Qualify brand standards', $dt(0, '10:00:00'), $close(29)],
    [6, 'Northwind Safety PPE', 27000, 7, 0, 2, 'Lost to incumbent', $dt(-20, '09:00:00'), $close(-12)],
    [7, 'Blue Harbor Disposable Program', 31000, 5, 100, 2, 'First delivery', $dt(-8, '10:00:00'), $closeInMonth(2)],
    [8, 'Cedar Medical Records Storage', 19000, 7, 0, 2, 'No budget this year', $dt(-18, '11:00:00'), $close(-9)],
    [9, 'Lakeside Mini-bar Refresh', 14000, 2, 25, 2, 'Sample kit', $dt(-1, '16:00:00'), $close(21)],
    [10, 'Pioneer Uniforms', 36000, 4, 75, 2, 'Final pricing', $dt(0, '15:00:00'), $close(12)],
    [4, 'Tech Solutions Expansion Seats', 96000, 3, 50, 2, 'Send SOW', $dt(-5, '11:00:00'), $close(14)],
    [2, 'Acme Jan/San Annual', 45000, 5, 100, 2, 'Q4 kickoff', $dt(-6, '09:00:00'), $closeInMonth(1)],
    [41, 'Silverline Showroom Fit-out', 58000, 1, 10, 2, 'Showroom appointment', $dt(4, '15:00:00'), $close(31)],
    // John
    [1, 'Office Furniture Upgrade', 250000, 3, 50, 1, 'Prepare proposal', $dt(-1, '09:00:00'), $close(29)],
    [3, 'MRO Supply Contract', 180000, 2, 25, 1, 'Needs analysis call', $dt(2, '10:00:00'), $close(44)],
    [5, 'Annual Supply Agreement', 120000, 1, 10, 1, 'Qualify budget', $dt(7, '11:00:00'), $close(30)],
    [1, 'Jan/San Reorder Program', 42000, 6, 100, 1, 'Kickoff', $dt(-3, '09:00:00'), $closeInMonth(3)],
    [19, 'Harbor Freight Pallet Program', 99000, 4, 75, 1, 'Contract redlines', $dt(1, '14:00:00'), $close(17)],
    [20, 'City Hall Bid Package', 210000, 3, 50, 1, 'Submit bid', $dt(0, '09:00:00'), $close(10)],
    [21, 'Evergreen Dorm Refresh', 175000, 2, 25, 1, 'Campus walk', $dt(5, '13:00:00'), $close(50)],
    [22, 'Redwood Multi-site', 88000, 1, 10, 1, 'Map locations', $dt(-2, '11:00:00'), $close(38)],
    [23, 'Midwest Line Trial', 61000, 4, 75, 1, 'Trial recap', $dt(-1, '10:00:00'), $close(15)],
    [5, 'Bright Holiday Displays', 33000, 7, 0, 1, 'Lost on price', $dt(-14, '16:00:00'), $close(-7)],
    [19, 'Harbor Safety Audit Kit', 28000, 5, 100, 1, 'Closed this month', $dt(-4, '09:00:00'), $closeInMonth(2)],
    // Priya
    [24, 'West Coast DC Replenishment', 140000, 3, 50, 5, 'Send volume pricing', $dt(-1, '14:00:00'), $close(25)],
    [25, 'Cascade Cafe Build-out', 52000, 2, 25, 5, 'Tasting + proposal', $dt(2, '09:00:00'), $close(32)],
    [26, 'Sierra Shop Consumables', 24000, 1, 10, 5, 'Qualify SKUs', $dt(0, '16:00:00'), $close(27)],
    [27, 'Golden State Cleanroom', 118000, 4, 75, 5, 'Compliance pack', $dt(3, '11:00:00'), $close(20)],
    [28, 'Bayview 12-property Kit', 76000, 3, 50, 5, 'Portfolio quote', $dt(-2, '10:00:00'), $close(36)],
    [29, 'Napa Harvest Season', 43000, 5, 100, 5, 'Delivered', $dt(-8, '15:00:00'), $closeInMonth(2)],
    [26, 'Sierra Detail Supplies', 11000, 7, 0, 5, 'Too small', $dt(-11, '09:00:00'), $close(-6)],
    // Marcus
    [30, 'Heartland Harvest Pack', 67000, 2, 25, 6, 'Harvest plan', $dt(1, '08:30:00'), $close(34)],
    [31, 'Plains Safety Upgrade', 89000, 3, 50, 6, 'Send proposal', $dt(-1, '13:00:00'), $close(23)],
    [32, 'Capitol State Contract', 155000, 4, 75, 6, 'Procurement meeting', $dt(2, '11:00:00'), $close(19)],
    [33, 'Twin Cities Store Reset', 48000, 1, 10, 6, 'Qualify timeline', $dt(5, '16:00:00'), $close(41)],
    [34, 'Great Lakes Blanket PO', 102000, 2, 25, 6, 'Overdue recap', $dt(-4, '09:00:00'), $close(28)],
    [35, 'Prairie Multi-campus', 198000, 3, 50, 6, 'Facilities walk', $dt(6, '10:00:00'), $close(55)],
    [34, 'Great Lakes Spot Buy', 16000, 5, 100, 6, 'Invoiced', $dt(-5, '10:00:00'), $closeInMonth(1)],
];

$oppStmt = $pdo->prepare('INSERT INTO opportunities (tenant_id, account_id, name, amount, stage_id, probability, owner_id, next_step, next_step_at, close_date, win_loss_reason) VALUES (1,?,?,?,?,?,?,?,?,?,?)');
foreach ($opps as $o) {
    $reason = null;
    if ((int) $o[3] === 6) {
        $reason = (string) $o[6];
    }
    $oppStmt->execute([$o[0], $o[1], $o[2], $o[3], $o[4], $o[5], $o[6], $o[7], $o[8], $reason]);
}

$tasks = [
    // Jane — overdue, today, later, done, cancelled
    ['Follow up with Sarah Williams', 'Confirm breakroom layout.', $dt(-2, '10:00:00'), 'open', 'high', 2, 'account', 2, 2],
    ['Prepare proposal for Acme Corp', null, $dt(0, '12:00:00'), 'open', 'high', 2, 'account', 2, 1],
    ['Call Lisa Anderson', 'Implementation timeline.', $dt(1, '10:00:00'), 'open', 'normal', 2, 'account', 4, 2],
    ['Send catalog to Northwind', null, $dt(2, '09:00:00'), 'open', 'high', 2, 'account', 6, 2],
    ['Site visit Blue Harbor', 'Kitchen measurements.', $dt(0, '14:00:00'), 'open', 'normal', 2, 'account', 7, 2],
    ['Cedar Medical proposal draft', null, $dt(2, '11:00:00'), 'open', 'normal', 2, 'account', 8, 2],
    ['Lakeside renewal checklist', null, $dt(3, '15:00:00'), 'open', 'low', 2, 'account', 9, 2],
    ['Pioneer dock walkthrough', null, $dt(-5, '08:00:00'), 'open', 'high', 2, 'account', 10, 2],
    ['Summit Athletics budget email', null, $dt(4, '10:00:00'), 'open', 'normal', 2, 'account', 11, 2],
    ['Oakridge board packet', null, $dt(5, '14:00:00'), 'open', 'high', 2, 'account', 12, 2],
    ['Metro Print kickoff notes', 'Won deal wrap-up.', $dt(-3, '09:00:00'), 'completed', 'normal', 2, 'account', 13, 2],
    ['Horizon legal redlines', null, $dt(1, '16:00:00'), 'open', 'high', 2, 'account', 14, 2],
    ['Pacific Dental demo recap', null, $dt(2, '09:00:00'), 'open', 'normal', 2, 'account', 15, 2],
    ['GreenLeaf assortment list', null, $dt(6, '13:00:00'), 'open', 'low', 2, 'account', 16, 2],
    ['Atlas job-site photos', null, $dt(5, '08:00:00'), 'open', 'normal', 2, 'account', 17, 2],
    ['Beacon brand standards', null, $dt(0, '11:00:00'), 'open', 'normal', 2, 'account', 18, 2],
    ['Log email to Haley Stone', null, $dt(1, '09:00:00'), 'open', 'low', 2, 'contact', 11, 2],
    ['Qualify Harborview CPA lead', null, $dt(0, '15:00:00'), 'open', 'high', 2, 'lead', 14, 2],
    ['Eastside Pharmacy intro', null, $dt(2, '10:00:00'), 'open', 'normal', 2, 'lead', 15, 2],
    ['Cancel duplicate Northwind task', 'Created twice.', $dt(-7, '09:00:00'), 'cancelled', 'low', 2, 'account', 6, 2],
    ['Acme Jan/San Q4 kickoff', null, $dt(-1, '09:00:00'), 'completed', 'normal', 2, 'account', 2, 2],
    ['Update Tech Solutions SOW', null, $dt(3, '11:00:00'), 'open', 'high', 2, 'account', 4, 2],
    ['Call Avery Ng at Summit Legal', null, $dt(0, '16:00:00'), 'open', 'normal', 2, 'lead', 13, 2],
    // John
    ['Follow up with Mike Johnson', null, $dt(-1, '09:00:00'), 'open', 'high', 1, 'account', 1, 1],
    ['Demo for New Deal', null, $dt(2, '11:00:00'), 'open', 'normal', 1, 'account', 4, 1],
    ['Send quote to Bright Retailers', null, $dt(2, '16:00:00'), 'open', 'normal', 1, 'account', 5, 1],
    ['City Hall bid checklist', null, $dt(0, '09:00:00'), 'open', 'high', 1, 'account', 20, 1],
    ['Evergreen campus walk prep', null, $dt(4, '13:00:00'), 'open', 'normal', 1, 'account', 21, 1],
    ['Redwood multi-site map', null, $dt(3, '11:00:00'), 'open', 'high', 1, 'account', 22, 1],
    ['Harbor Freight contract review', null, $dt(1, '14:00:00'), 'open', 'normal', 1, 'account', 19, 1],
    ['Midwest trial recap', null, $dt(0, '10:00:00'), 'open', 'normal', 1, 'account', 23, 1],
    ['Assign unassigned East leads', null, $dt(0, '17:00:00'), 'open', 'low', 1, null, null, 1],
    ['ABC furniture proposal v2', null, $dt(-6, '09:00:00'), 'completed', 'high', 1, 'account', 1, 1],
    // Priya
    ['West Coast volume pricing', null, $dt(-1, '14:00:00'), 'open', 'high', 5, 'account', 24, 5],
    ['Cascade tasting samples', null, $dt(1, '09:00:00'), 'open', 'normal', 5, 'account', 25, 5],
    ['Sierra SKU list', null, $dt(0, '16:00:00'), 'open', 'normal', 5, 'account', 26, 5],
    ['Golden State compliance pack', null, $dt(3, '11:00:00'), 'open', 'high', 5, 'account', 27, 5],
    ['Bayview property list', null, $dt(4, '10:00:00'), 'open', 'normal', 5, 'account', 28, 5],
    ['Napa seasonal wrap-up', null, $dt(-4, '15:00:00'), 'completed', 'low', 5, 'account', 29, 5],
    // Marcus
    ['Heartland harvest call', null, $dt(1, '08:30:00'), 'open', 'normal', 6, 'account', 30, 6],
    ['Plains safety proposal', null, $dt(0, '13:00:00'), 'open', 'high', 6, 'account', 31, 6],
    ['Capitol procurement meeting', null, $dt(2, '11:00:00'), 'open', 'high', 6, 'account', 32, 6],
    ['Twin Cities reset plan', null, $dt(5, '16:00:00'), 'open', 'low', 6, 'account', 33, 6],
    ['Great Lakes overdue recap', null, $dt(-4, '09:00:00'), 'open', 'high', 6, 'account', 34, 6],
    ['Prairie campus walk', null, $dt(6, '10:00:00'), 'open', 'normal', 6, 'account', 35, 6],
    // Sam (service)
    ['CopierCare meter read', 'Quarterly visit.', $dt(1, '09:00:00'), 'open', 'high', 4, 'account', 36, 4],
    ['QuickPrint PM visit', null, $dt(0, '14:00:00'), 'open', 'normal', 4, 'account', 37, 4],
    ['Campus West after-hours ticket', null, $dt(-1, '17:00:00'), 'open', 'high', 4, 'account', 38, 4],
    ['Riverside install check', null, $dt(2, '10:00:00'), 'open', 'normal', 4, 'account', 39, 4],
    ['CopierCare toner swap', null, $dt(3, '11:00:00'), 'open', 'low', 4, 'account', 36, 4],
    ['QuickPrint fuser notes', null, $dt(-2, '14:00:00'), 'completed', 'normal', 4, 'account', 37, 4],
    ['Campus West parts order', null, $dt(4, '09:00:00'), 'open', 'normal', 4, 'account', 38, 4],
    ['Riverside training recap', null, $dt(0, '16:00:00'), 'open', 'low', 4, 'account', 39, 4],
];

$taskStmt = $pdo->prepare('INSERT INTO tasks (tenant_id, title, description, due_at, status, priority, owner_id, related_type, related_id, created_by, completed_at) VALUES (1,?,?,?,?,?,?,?,?,?,?)');
foreach ($tasks as $t) {
    $done = $t[3] === 'completed' ? $t[2] : null;
    $taskStmt->execute([$t[0], $t[1], $t[2], $t[3], $t[4], $t[5], $t[6], $t[7], $t[8], $done]);
}

$meetings = [
    ['Demo with Tech Solutions', $dt(1, '10:00:00'), $dt(1, '11:00:00'), 'Zoom', 'meeting', 'account', 4, 2, 'Implementation scope.'],
    ['Follow up call - Acme Corp', $dt(1, '14:00:00'), $dt(1, '14:30:00'), 'Phone', 'call', 'account', 2, 2, null],
    ['Proposal Review ABC', $dt(2, '11:00:00'), $dt(2, '12:00:00'), 'Office', 'meeting', 'account', 1, 1, null],
    ['Blue Harbor site visit', $dt(0, '09:00:00'), $dt(0, '10:30:00'), 'Kitchen', 'visit', 'account', 7, 2, 'Measure counters.'],
    ['Cedar Medical intro', $dt(3, '09:30:00'), $dt(3, '10:00:00'), 'Phone', 'call', 'account', 8, 2, null],
    ['Lakeside GM lunch', $dt(5, '12:00:00'), $dt(5, '13:00:00'), 'Hotel restaurant', 'meeting', 'account', 9, 2, null],
    ['Pioneer dock walk', $dt(4, '08:00:00'), $dt(4, '09:30:00'), 'Warehouse', 'visit', 'account', 10, 2, null],
    ['Oakridge board preview', $dt(8, '14:00:00'), $dt(8, '15:00:00'), 'District office', 'meeting', 'account', 12, 2, null],
    ['Pacific Dental demo', $dt(6, '09:00:00'), $dt(6, '10:00:00'), 'Clinic', 'meeting', 'account', 15, 2, null],
    ['Horizon redline call', $dt(2, '16:00:00'), $dt(2, '16:45:00'), 'Teams', 'call', 'account', 14, 2, null],
    ['Northwind catalog review', $dt(7, '13:00:00'), $dt(7, '14:00:00'), 'Zoom', 'meeting', 'account', 6, 2, null],
    ['Beacon floor walk', $dt(9, '10:00:00'), $dt(9, '11:00:00'), '1 Federal St', 'visit', 'account', 18, 2, null],
    ['City Hall bid Q&A', $dt(3, '09:00:00'), $dt(3, '10:00:00'), 'City Hall', 'meeting', 'account', 20, 1, null],
    ['Evergreen campus tour', $dt(6, '13:00:00'), $dt(6, '15:00:00'), 'Campus', 'visit', 'account', 21, 1, null],
    ['Harbor Freight pricing', $dt(1, '15:00:00'), $dt(1, '15:45:00'), 'Phone', 'call', 'account', 19, 1, null],
    ['Midwest trial recap', $dt(4, '10:00:00'), $dt(4, '11:00:00'), 'Plant', 'meeting', 'account', 23, 1, null],
    ['Redwood clinics call', $dt(0, '11:00:00'), $dt(0, '11:30:00'), 'Phone', 'call', 'account', 22, 1, null],
    ['West Coast DC tour', $dt(2, '14:00:00'), $dt(2, '16:00:00'), 'San Francisco DC', 'visit', 'account', 24, 5, null],
    ['Cascade tasting', $dt(3, '09:00:00'), $dt(3, '10:00:00'), 'Roastery', 'meeting', 'account', 25, 5, null],
    ['Golden State compliance', $dt(7, '11:00:00'), $dt(7, '12:00:00'), 'Zoom', 'meeting', 'account', 27, 5, null],
    ['Capitol procurement', $dt(2, '11:00:00'), $dt(2, '12:00:00'), 'St. Paul', 'meeting', 'account', 32, 6, null],
    ['Prairie facilities walk', $dt(8, '10:00:00'), $dt(8, '12:00:00'), 'Omaha campus', 'visit', 'account', 35, 6, null],
    ['CopierCare PM', $dt(1, '09:00:00'), $dt(1, '10:00:00'), 'Downtown', 'visit', 'account', 36, 4, 'Meter + supplies.'],
    ['QuickPrint service call', $dt(0, '14:00:00'), $dt(0, '15:00:00'), 'Shop', 'visit', 'account', 37, 4, null],
    ['Campus West ticket', $dt(3, '17:00:00'), $dt(3, '18:00:00'), 'Plant', 'visit', 'account', 38, 4, null],
    ['Riverside install', $dt(2, '10:00:00'), $dt(2, '11:30:00'), 'Biomed', 'visit', 'account', 39, 4, null],
    ['Team pipeline review', $dt(1, '09:00:00'), $dt(1, '09:45:00'), 'Conference room', 'meeting', null, null, 1, 'Weekly.'],
    ['Next month planning', $dt(20, '10:00:00'), $dt(20, '11:00:00'), 'Zoom', 'meeting', null, null, 1, null],
];

$mtgStmt = $pdo->prepare('INSERT INTO meetings (tenant_id, title, starts_at, ends_at, location, meeting_type, related_type, related_id, owner_id, notes) VALUES (1,?,?,?,?,?,?,?,?,?)');
foreach ($meetings as $m) {
    $mtgStmt->execute($m);
}

$actStmt = $pdo->prepare('INSERT INTO activities (tenant_id, type, subject, body, related_type, related_id, owner_id, activity_at) VALUES (1,?,?,?,?,?,?,?)');
$timelineSeeds = [
    ['email', 'Re: Proposal for Office Furniture', 'Sent pricing options for floors 2 and 3.', 'account', 1, 1, $dt(0, '10:34:00')],
    ['call', 'Spoke with Mike Johnson', 'Interested in ergonomic chairs. Budget in Q3.', 'account', 1, 1, $dt(0, '11:20:00')],
    ['note', 'ABC decision map', 'Mike buys, Dana influences, Pat signs.', 'account', 1, 1, $dt(-5, '09:00:00')],
    ['meeting', 'On-site furniture walk', 'Measured 42 workstations.', 'account', 1, 1, $dt(-5, '14:00:00')],
    ['email', 'Your quote is ready', 'Breakroom refresh quote attached.', 'account', 2, 2, $dt(-1, '10:00:00')],
    ['email', 'Re: Sandbox users for demo', 'Lisa asked about extra seats.', 'account', 4, 2, $dt(0, '10:34:00')],
    ['call', 'Quick check with Haley Stone', 'Confirmed catalog questions.', 'account', 6, 2, $dt(0, '14:15:00')],
    ['call', 'Call Sarah Williams', 'Wants samples on site next week.', 'account', 2, 2, $dt(-2, '16:00:00')],
    ['meeting', 'Meeting reminder — Tech Solutions', 'Confirm Zoom for the demo.', 'account', 4, 2, $dt(-1, '09:15:00')],
    ['note', 'Acme competitors', 'Incumbent is 8% lower on liners.', 'account', 2, 2, $dt(-4, '11:00:00')],
    ['visit', 'Acme plant visit', 'Breakroom is 400 sq ft.', 'account', 2, 2, $dt(-6, '13:00:00')],
    ['meeting', 'Walk-through with Tom Davis', 'MRO crib is overcrowded.', 'account', 3, 1, $dt(-2, '09:00:00')],
    ['email', 'MRO contract outline', 'Sent 24-month terms.', 'account', 3, 1, $dt(-3, '17:00:00')],
    ['call', 'Call summary - new opportunity', 'Lisa interested in implementation.', 'account', 4, 2, $dt(-5, '11:00:00')],
    ['email', 'Tech Solutions demo recap', 'Seats, SSO, and training hours.', 'account', 4, 2, $dt(-1, '18:00:00')],
    ['note', 'Site notes', 'Warehouse prefers monthly delivery.', 'account', 5, 1, $dt(-3, '10:00:00')],
    ['email', 'Northwind catalog', 'Spring book emailed.', 'account', 6, 2, $dt(-2, '09:30:00')],
    ['call', 'Haley Stone intro', 'Wants safety and jan/san.', 'account', 6, 2, $dt(-4, '14:00:00')],
    ['visit', 'Blue Harbor kitchen', 'Need NSF listed carts.', 'account', 7, 2, $dt(-1, '10:00:00')],
    ['note', 'Cedar waiting room photos', 'Uploaded to the record.', 'account', 8, 2, $dt(-2, '16:40:00')],
    ['email', 'Lakeside housekeeping SKUs', 'Par levels attached.', 'account', 9, 2, $dt(-3, '08:15:00')],
    ['meeting', 'Pioneer safety huddle', 'Dock team wants better mats.', 'account', 10, 2, $dt(-4, '07:45:00')],
    ['task', 'Oakridge packet started', 'Need three references.', 'account', 12, 2, $dt(-1, '19:00:00')],
    ['email', 'Metro Print welcome', 'Won toner program.', 'account', 13, 2, $dt(-4, '09:00:00')],
    ['call', 'Horizon counsel', 'MSA still in review.', 'account', 14, 2, $dt(-1, '11:20:00')],
    ['visit', 'CopierCare meter read', 'Black 12k, color 3k.', 'account', 36, 4, $dt(-2, '09:10:00')],
    ['note', 'QuickPrint fuser replaced', 'Customer signed ticket.', 'account', 37, 4, $dt(-2, '15:00:00')],
    ['call', 'Campus West after hours', 'Left voicemail with Deb.', 'account', 38, 4, $dt(-1, '18:05:00')],
    ['email', 'West Coast volume grid', 'Tiers at 10k / 25k cases.', 'account', 24, 5, $dt(-1, '14:20:00')],
    ['meeting', 'Cascade tasting notes', 'They liked the compostable cups.', 'account', 25, 5, $dt(-3, '09:40:00')],
    ['email', 'Plains safety proposal', 'Draft v1 sent.', 'account', 31, 6, $dt(-1, '13:10:00')],
    ['call', 'Great Lakes overdue recap', 'Buyer traveling until Friday.', 'account', 34, 6, $dt(-4, '09:05:00')],
    ['stage', 'ABC moved to Proposal', 'Ready for pricing.', 'opportunity', 1, 1, $dt(-6, '10:00:00')],
    ['call', 'Intro call with Avery Ng', 'Budget confirmed. Ready for discovery.', 'lead', 13, 2, $dt(-9, '11:00:00')],
    ['email', 'Summit Legal intro', 'Thanks for taking my call.', 'lead', 13, 2, $dt(-10, '16:00:00')],
    ['stage', 'New → Qualified', null, 'lead', 13, 2, $dt(-8, '09:30:00')],
    ['email', 'Harborview CPA — next step', 'Are you free Thursday?', 'lead', 14, 2, $dt(-1, '09:40:00')],
    ['stage', 'New → Qualified', 'Walked the shop. Catalog is a fit.', 'lead', 22, 2, $dt(-7, '14:00:00')],
    ['call', 'Hayden Scott follow-up', 'Wants a sample kit next week.', 'lead', 22, 2, $dt(-6, '10:15:00')],
    ['stage', 'New → Not a fit', 'Not a fit for current catalog.', 'lead', 25, 2, $dt(-12, '15:20:00')],
    ['stage', 'New → Qualified', null, 'lead', 27, 2, $dt(-18, '11:00:00')],
    ['stage', 'Qualified → Converted', 'Won toner program.', 'lead', 27, 2, $dt(-16, '09:00:00')],
    ['stage', 'New → Converted', 'Existing Acme relationship.', 'lead', 28, 2, $dt(-24, '10:00:00')],
];
foreach ($timelineSeeds as $a) {
    $actStmt->execute($a);
}

// Spread extra activity on each day of this month through today so Home charts have a line.
$types = ['call', 'email', 'note', 'meeting', 'visit'];
$owners = [1, 2, 5, 6];
$accountIds = range(1, 35);
$todayDom = max(1, (int) date('j'));
for ($d = 1; $d <= $todayDom; $d++) {
    $count = ($d % 3) + 3;
    for ($i = 0; $i < $count; $i++) {
        $type = $types[($d + $i) % count($types)];
        $owner = $owners[($d + $i) % count($owners)];
        $aid = $accountIds[($d * 3 + $i) % count($accountIds)];
        $at = $day($d, sprintf('%02d:%02d:00', 8 + ($i % 8), ($d * 7 + $i) % 60));
        $actStmt->execute([
            $type,
            ucfirst($type) . " with account #{$aid}",
            'Logged from demo seed so Home and Reports show a busy month.',
            'account',
            $aid,
            $owner,
            $at,
        ]);
    }
}

$emailStmt = $pdo->prepare('INSERT INTO emails (tenant_id, related_type, related_id, direction, subject, body, to_email, from_email, owner_id, provider, sent_at, status) VALUES (1,?,?,?,?,?,?,?,?,?,?,?)');
$emails = [
    ['account', 1, 'out', 'Re: Proposal for Office Furniture', 'Please see options for floors 2 and 3.', 'mike@abcoffice.example', 'john.manager@aosware.local', 1, 'outlook', $dt(0, '11:05:00'), 'logged'],
    ['account', 2, 'out', 'Your quote is ready', 'Quote attached for breakroom refresh.', 'sarah@acme.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-1, '10:02:00'), 'logged'],
    ['account', 2, 'in', 'Re: Your quote is ready', 'Can you add recycling bins?', 'jane.rep@aosware.local', 'sarah@acme.example', 2, 'gmail', $dt(-1, '15:40:00'), 'logged'],
    ['account', 4, 'out', 'Demo recap — implementation', 'SSO, seats, and training hours.', 'lisa@techsolutions.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-1, '18:10:00'), 'logged'],
    ['account', 6, 'out', 'Northwind spring catalog', 'Link to the flipbook.', 'haley@northwind.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-2, '09:35:00'), 'logged'],
    ['account', 7, 'out', 'Blue Harbor visit confirmation', 'Tuesday 9:00 at the kitchen.', 'marisol@blueharbor.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-2, '16:00:00'), 'logged'],
    ['account', 8, 'out', 'Cedar waiting room options', 'Three layouts attached.', 'ellen@cedarmed.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-3, '11:00:00'), 'sent'],
    ['account', 9, 'out', 'Lakeside housekeeping par levels', 'Spreadsheet attached.', 'claire@lakesidehotels.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-3, '08:20:00'), 'logged'],
    ['account', 10, 'out', 'Pioneer dock mats', 'Anti-fatigue options.', 'andre@pioneerlog.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-4, '12:00:00'), 'logged'],
    ['account', 11, 'out', 'Summit locker colors', 'Please pick from the three chips.', 'kim@summitath.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-5, '09:00:00'), 'logged'],
    ['account', 12, 'out', 'Oakridge references', 'Three K-12 references.', 'jordan@oakridgek12.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-5, '14:30:00'), 'logged'],
    ['account', 13, 'out', 'Welcome — toner program', 'Kickoff next Monday.', 'riley@metroprint.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-4, '09:05:00'), 'sent'],
    ['account', 14, 'out', 'Horizon MSA', 'Legal can mark this up.', 'sofia@horizonins.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-1, '11:25:00'), 'logged'],
    ['account', 15, 'out', 'Pacific Dental demo hold', 'Held Thursday 9am.', 'alan@pacificdental.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-6, '10:00:00'), 'logged'],
    ['account', 16, 'out', 'GreenLeaf endcap plan', 'Fall SKUs.', 'nora@greenleaf.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-7, '13:00:00'), 'logged'],
    ['account', 17, 'out', 'Atlas trailer quote', 'Two configurations.', 'tyler@atlascon.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-2, '08:10:00'), 'logged'],
    ['account', 18, 'out', 'Beacon reception lookbook', 'Brand-safe options.', 'amina@beaconfin.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-8, '10:00:00'), 'logged'],
    ['lead', 13, 'out', 'Summit Legal intro', 'Thanks for taking my call.', 'avery@summit.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-10, '16:00:00'), 'logged'],
    ['lead', 14, 'out', 'Harborview CPA — next step', 'Are you free Thursday?', 'jordan@harborviewcpa.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(-1, '09:40:00'), 'logged'],
    ['account', 4, 'in', 'Re: Demo recap', 'Can we add sandbox users?', 'jane.rep@aosware.local', 'lisa@techsolutions.example', 2, 'gmail', $dt(0, '08:15:00'), 'logged'],
    ['account', 6, 'in', 'Catalog question', 'Do you have recycled copy paper?', 'jane.rep@aosware.local', 'haley@northwind.example', 2, 'gmail', $dt(-2, '13:22:00'), 'logged'],
    ['opportunity', 2, 'out', 'Implementation pricing', 'Year 1 vs year 2.', 'lisa@techsolutions.example', 'jane.rep@aosware.local', 2, 'gmail', $dt(0, '09:50:00'), 'logged'],
    ['account', 3, 'out', 'MRO contract outline', '24-month terms attached.', 'tom@globalind.example', 'john.manager@aosware.local', 1, 'outlook', $dt(-3, '17:05:00'), 'logged'],
    ['account', 5, 'out', 'Bright holiday displays', 'We could not meet the price.', 'chris@bright.example', 'john.manager@aosware.local', 1, 'outlook', $dt(-7, '16:10:00'), 'logged'],
    ['account', 19, 'out', 'Harbor pallet program', 'Draft MSA.', 'carl@harborfp.example', 'john.manager@aosware.local', 1, 'outlook', $dt(-1, '15:00:00'), 'sent'],
    ['account', 20, 'out', 'City Hall bid Q&A', 'Answers to addendum 2.', 'helen@cityhall.example', 'john.manager@aosware.local', 1, 'outlook', $dt(0, '09:20:00'), 'logged'],
    ['account', 21, 'out', 'Evergreen tour confirm', 'Thursday 1pm.', 'ian@evergreenu.example', 'john.manager@aosware.local', 1, 'outlook', $dt(-2, '12:00:00'), 'logged'],
    ['account', 22, 'out', 'Redwood location list', 'Please confirm 6 sites.', 'maya@redwoodclinics.example', 'john.manager@aosware.local', 1, 'outlook', $dt(-3, '11:00:00'), 'logged'],
    ['account', 23, 'out', 'Midwest trial recap', 'Defect rate was 0.4%.', 'vince@midwestpack.example', 'john.manager@aosware.local', 1, 'outlook', $dt(-1, '10:05:00'), 'logged'],
    ['account', 24, 'out', 'West Coast volume grid', 'Tiers attached.', 'pshah@westcoastgrocers.example', 'priya.patel@aosware.local', 5, null, $dt(-1, '14:22:00'), 'logged'],
    ['account', 25, 'out', 'Cascade tasting follow-up', 'Compostable cups sample.', 'jess@cascadecoffee.example', 'priya.patel@aosware.local', 5, null, $dt(-3, '10:00:00'), 'logged'],
    ['account', 27, 'out', 'Golden State cleanroom', 'ISO notes.', 'priya.m@gslabs.example', 'priya.patel@aosware.local', 5, null, $dt(-2, '11:00:00'), 'logged'],
    ['account', 31, 'out', 'Plains safety proposal v1', 'Draft for Ruth.', 'ruth@plainsmfg.example', 'marcus.webb@aosware.local', 6, null, $dt(-1, '13:12:00'), 'logged'],
    ['account', 32, 'out', 'Capitol contract questions', 'Prevailing wage?', 'neil@capitoloffice.example', 'marcus.webb@aosware.local', 6, null, $dt(-4, '09:00:00'), 'logged'],
    ['account', 34, 'out', 'Great Lakes recap', 'When are you back?', 'stan@glmro.example', 'marcus.webb@aosware.local', 6, null, $dt(-4, '09:10:00'), 'failed'],
];
$monthStartTs = strtotime(date('Y-m-01 00:00:00'));
foreach ($emails as $e) {
    $sentTs = strtotime((string) $e[9]);
    if ($sentTs !== false && $sentTs < $monthStartTs) {
        $e[9] = $inMonth(min(6, max(0, (int) date('j') - 1)), date('H:i:s', $sentTs));
    }
    $emailStmt->execute($e);
}

$pdo->exec("INSERT INTO integrations (tenant_id, user_id, provider, kind, status, connected_at) VALUES
(1,2,'gmail','email','connected', DATE_SUB(NOW(), INTERVAL 12 DAY)),
(1,2,'gmail','calendar','connected', DATE_SUB(NOW(), INTERVAL 12 DAY)),
(1,1,'outlook','email','connected', DATE_SUB(NOW(), INTERVAL 20 DAY)),
(1,1,'outlook','calendar','connected', DATE_SUB(NOW(), INTERVAL 20 DAY)),
(1,5,'outlook','email','disconnected', NULL),
(1,6,'gmail','email','disconnected', NULL)");

$pdo->exec("INSERT INTO notifications (tenant_id, user_id, title, body, url) VALUES
(1,1,'New lead assigned','Morgan Blake is still unassigned in the West.','/assignment'),
(1,1,'Overdue follow-up','Redwood Clinics multi-site map is late.','/tasks'),
(1,1,'Deal needs you','City Hall Bid Package next step is today.','/opportunities'),
(1,2,'Task due today','Prepare proposal for Acme Corp.','/tasks'),
(1,2,'New lead','Blair Cox (Harbor Yoga Loft) was assigned to you.','/leads'),
(1,2,'Meeting today','Blue Harbor site visit this morning.','/calendar'),
(1,2,'Inbound email','Lisa Anderson asked about sandbox users.','/emails'),
(1,4,'Visit today','QuickPrint PM visit is on your calendar.','/calendar'),
(1,4,'Overdue ticket','Campus West after-hours ticket is late.','/tasks'),
(1,5,'Pricing follow-up','West Coast volume grid was sent yesterday.','/accounts'),
(1,6,'Overdue recap','Great Lakes MRO buyer has not replied.','/tasks'),
(1,7,'Campaign sent','Spring catalog campaign was delivered. Check opens on the customer page.','/campaigns'),
(1,7,'Draft waiting','Reorder nudge is still a draft.','/campaigns'),
(1,7,'Unsubscribe','chris@bright.example asked to stop email.','/unsubscribes')");

$pdo->exec("INSERT INTO audit_logs (tenant_id, user_id, action, entity_type, entity_id, ip, meta) VALUES
(1,3,'user.create','user',5,'127.0.0.1','{\"note\":\"demo seed\"}'),
(1,3,'user.create','user',6,'127.0.0.1','{\"note\":\"demo seed\"}'),
(1,1,'lead.assign','lead',NULL,'127.0.0.1','{\"note\":\"demo seed\"}'),
(1,2,'opportunity.stage','opportunity',2,'127.0.0.1','{\"to\":\"Quote\"}')");

$pdo->exec("UPDATE accounts SET attrs = JSON_OBJECT(
  'customer_type','active',
  'account_potential', 250000,
  'product_categories','Breakroom, Paper, Jan/San',
  'vendor_lines','Kimberly-Clark, 3M',
  'buying_frequency','monthly',
  'last_order_at', DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 120 DAY), '%Y-%m-%d'),
  'reorder_cycle_days', 90,
  'margin', 22,
  'lost_sales', 4500,
  'contract_pricing_status','contract'
) WHERE id IN (1,2,3,5,6,13)");
$pdo->exec("UPDATE accounts SET attrs = JSON_OBJECT(
  'customer_type','reorder',
  'account_potential', 180000,
  'product_categories','Jan/San, MRO',
  'vendor_lines','Ecolab, Grainger',
  'buying_frequency','weekly',
  'last_order_at', DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 20 DAY), '%Y-%m-%d'),
  'reorder_cycle_days', 30,
  'margin', 18,
  'lost_sales', 1200,
  'contract_pricing_status','special'
) WHERE id IN (7,9,24)");
$pdo->exec("UPDATE accounts SET attrs = JSON_OBJECT(
  'customer_type','prospect',
  'account_potential', 80000,
  'product_categories','Office supplies',
  'buying_frequency','as_needed',
  'contract_pricing_status','none'
) WHERE id IN (8,10,11,12,14,15,16,17,18,41)");
$pdo->exec("UPDATE accounts SET next_visit_at = DATE_ADD(NOW(), INTERVAL 1 DAY), service_assignee_id = 4, last_visit_at = DATE_SUB(NOW(), INTERVAL 30 DAY) WHERE id IN (36,37)");
$pdo->exec("UPDATE accounts SET next_visit_at = DATE_SUB(NOW(), INTERVAL 1 DAY), service_assignee_id = 4 WHERE id IN (38,39)");

$pdo->exec("INSERT INTO marketing_connections (tenant_id, provider, status, connected_at) VALUES
(1,'mailchimp','connected', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1,'constant_contact','disconnected', NULL)");

$pdo->exec("INSERT INTO email_templates (id, tenant_id, name, subject, body, created_by) VALUES
(1,1,'Spring catalog','{{first_name}}, new catalog for {{company}}','<p>Hi {{first_name}},</p><p>Our spring catalog is ready for {{company}}.</p><p>Visit https://aosware.example/catalog</p>',7),
(2,1,'Reorder reminder','Time to restock, {{first_name}}','<p>Hello {{first_name}},</p><p>It looks like {{company}} may be due for a reorder.</p>',7),
(3,1,'Event invite','Join us, {{first_name}}','<p>Hi {{first_name}},</p><p>You and {{company}} are invited to our buyer open house.</p>',7),
(4,1,'Win-back','We miss {{company}}','<p>{{first_name}}, it has been a while since {{company}} ordered.</p>',7)");

$pdo->exec("INSERT INTO marketing_lists (id, tenant_id, name, filters, created_by) VALUES
(1,1,'East buyers','{\"territory_id\":1}',7),
(2,1,'No order in 90 days','{\"last_order\":\"none_90\"}',7),
(3,1,'West accounts','{\"territory_id\":2}',7),
(4,1,'Quote-stage deals','{\"stage_id\":3}',7)");

$pdo->exec("INSERT INTO campaigns (id, tenant_id, name, template_id, list_id, subject, body, status, provider, sent_at, created_by) VALUES
(1,1,'Spring catalog','1','1','{{first_name}}, new catalog for {{company}}','<p>Hi {{first_name}},</p><p>Our spring catalog is ready for {{company}}.</p><p>Visit https://aosware.example/catalog</p>','sent','mailchimp', GREATEST(DATE_SUB(NOW(), INTERVAL 2 DAY), TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-01'), '10:00:00')),7),
(2,1,'Reorder nudge','2','2','Time to restock, {{first_name}}','<p>Hello {{first_name}},</p><p>It looks like {{company}} may be due for a reorder.</p>','draft',NULL,NULL,7),
(3,1,'West coast intro','3','3','Join us, {{first_name}}','<p>Hi {{first_name}},</p><p>You and {{company}} are invited to our buyer open house.</p>','sent','mailchimp', GREATEST(DATE_SUB(NOW(), INTERVAL 4 DAY), TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-01'), '11:00:00')),7),
(4,1,'Win-back inactive','4','2','We miss {{company}}','<p>{{first_name}}, it has been a while since {{company}} ordered.</p>','draft',NULL,NULL,7)");

$pdo->exec("INSERT INTO unsubscribes (tenant_id, email, source, campaign_id) VALUES
(1,'chris@bright.example','campaign',1),
(1,'pat@abcoffice.example','campaign',1),
(1,'eli@acme.example','campaign',1),
(1,'coach@summitath.example','manual',NULL),
(1,'ops@horizonins.example','campaign',3),
(1,'gm@napahosp.example','import',NULL),
(1,'buy@greenleaf.example','campaign',3),
(1,'hello@metroprint.example','campaign',1)");

$pdo->exec("UPDATE contacts SET email_status = 'bounced' WHERE email IN ('greg@northwind.example')");

$cr = $pdo->prepare('INSERT INTO campaign_recipients (tenant_id, campaign_id, contact_id, account_id, email, token, status, sent_at, opened_at, clicked_at, bounced_at) VALUES (1,1,?,?,?,?,?,?,?,?,?)');
$sentAt = $inMonth(2, '10:00:00');
$opened = $inMonth(1, '15:00:00');
$contactsForCamp = $pdo->query('SELECT id, account_id, email FROM contacts WHERE email IS NOT NULL AND email <> "" AND email NOT IN ("chris@bright.example","pat@abcoffice.example","greg@northwind.example") ORDER BY id LIMIT 18')->fetchAll(PDO::FETCH_ASSOC);
$i = 0;
foreach ($contactsForCamp as $row) {
    $token = bin2hex(random_bytes(32));
    $openAt = ($i % 3 === 0) ? $opened : null;
    $clickAt = ($i % 6 === 0) ? $opened : null;
    $cr->execute([(int)$row['id'], (int)$row['account_id'], strtolower((string)$row['email']), $token, 'sent', $sentAt, $openAt, $clickAt, null]);
    if ($openAt) {
        $pdo->prepare("INSERT INTO activities (tenant_id, type, subject, related_type, related_id, owner_id, activity_at) VALUES (1,'campaign',?,?,?,7,?)")
            ->execute(['Opened: Spring catalog', 'account', (int)$row['account_id'], $openAt]);
    }
    $i++;
}
$bounceContact = $pdo->query("SELECT id, account_id, email FROM contacts WHERE email = 'greg@northwind.example'")->fetch(PDO::FETCH_ASSOC);
if ($bounceContact) {
    $pdo->prepare("INSERT INTO campaign_recipients (tenant_id, campaign_id, contact_id, account_id, email, token, status, sent_at, bounced_at) VALUES (1,1,?,?,?,?,'bounced',?,?)")
        ->execute([(int)$bounceContact['id'], (int)$bounceContact['account_id'], strtolower((string)$bounceContact['email']), bin2hex(random_bytes(32)), $sentAt, $sentAt]);
}

$sentWest = $inMonth(3, '11:00:00');
$openedWest = $inMonth(2, '16:00:00');
$crWest = $pdo->prepare('INSERT INTO campaign_recipients (tenant_id, campaign_id, contact_id, account_id, email, token, status, sent_at, opened_at, clicked_at, bounced_at) VALUES (1,3,?,?,?,?,?,?,?,?,?)');
$westContacts = $pdo->query('SELECT c.id, c.account_id, c.email FROM contacts c INNER JOIN accounts a ON a.id = c.account_id WHERE a.id BETWEEN 24 AND 29 AND c.email IS NOT NULL AND c.email <> ""')->fetchAll(PDO::FETCH_ASSOC);
$w = 0;
foreach ($westContacts as $row) {
    $token = bin2hex(random_bytes(32));
    $openAt = ($w % 2 === 0) ? $openedWest : null;
    $clickAt = ($w % 4 === 0) ? $openedWest : null;
    $crWest->execute([(int)$row['id'], (int)$row['account_id'], strtolower((string)$row['email']), $token, 'sent', $sentWest, $openAt, $clickAt, null]);
    $w++;
}
