-- ============================================================
-- BATCH 3: Reconciliation Models for Westin Healthcare
-- Database: westin-v19 | Date: 2026-04-03
-- ============================================================
-- Tax IDs: 20 = HST PURCHASE (13%), 32 = NO TAX PURCHASE (0%)
-- ============================================================

BEGIN;

-- Helper function to create writeoff models in one shot
CREATE OR REPLACE FUNCTION _tmp_create_writeoff(
  p_name text, p_seq int, p_match text,
  p_account_id int, p_tax_id int, p_label text
) RETURNS void AS $$
DECLARE
  v_model_id int;
  v_line_id int;
BEGIN
  INSERT INTO account_reconcile_model (name, sequence, company_id, trigger, match_label, match_label_param, active, can_be_proposed, create_uid, write_uid, create_date, write_date)
  VALUES (jsonb_build_object('en_US', p_name), p_seq, 1, 'auto_reconcile', 'contains', p_match, true, false, 2, 2, NOW(), NOW())
  RETURNING id INTO v_model_id;

  INSERT INTO account_reconcile_model_line (model_id, company_id, sequence, account_id, amount_type, amount, amount_string, label, create_uid, write_uid, create_date, write_date)
  VALUES (v_model_id, 1, 10, p_account_id, 'percentage', 100, '100', jsonb_build_object('en_US', p_label), 2, 2, NOW(), NOW())
  RETURNING id INTO v_line_id;

  INSERT INTO account_reconcile_model_line_account_tax_rel (account_reconcile_model_line_id, account_tax_id)
  VALUES (v_line_id, p_tax_id);
END;
$$ LANGUAGE plpgsql;

-- Helper function for partner-mapping models
CREATE OR REPLACE FUNCTION _tmp_create_partner_map(
  p_name text, p_seq int, p_match text, p_partner_id int
) RETURNS void AS $$
BEGIN
  INSERT INTO account_reconcile_model (name, sequence, company_id, trigger, match_label, match_label_param, mapped_partner_id, active, can_be_proposed, create_uid, write_uid, create_date, write_date)
  VALUES (jsonb_build_object('en_US', p_name), p_seq, 1, 'auto_reconcile', 'contains', p_match, p_partner_id, true, false, 2, 2, NOW(), NOW());
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- PART 1: WRITEOFF MODELS (36 models)
-- ============================================================
-- Acct 495=Computer/IT, 496=Advertising, 497=Car/Van, 499=Bank Charges
-- Acct 501=Dues/Subs, 506=Meals, 507=Office, 518=Shipping
-- Acct 523=Telephone, 526=Utilities, 552=Gas, 557=Security

-- Rideshare / Transportation
SELECT _tmp_create_writeoff('Uber Rides',               200, 'uber',              497, 20, 'Uber Rideshare');
SELECT _tmp_create_writeoff('Lyft Rides',               201, 'Lyft',              497, 20, 'Lyft Rideshare');
SELECT _tmp_create_writeoff('407 ETR Highway Tolls',    202, '407 ETR',           497, 20, '407 ETR Highway Tolls');
SELECT _tmp_create_writeoff('Klassic Car Wash',         203, 'KLASSIC CAR WASH',  497, 20, 'Klassic Car Wash');

-- Web Hosting / IT (NO TAX - foreign companies)
SELECT _tmp_create_writeoff('Cloud Clusters Hosting',   210, 'CLOUD CLUSTERS',    495, 32, 'Cloud Clusters Web Hosting');
SELECT _tmp_create_writeoff('Siteground Web Hosting',   211, 'SITEGROUND',        495, 32, 'Siteground Web Hosting');
SELECT _tmp_create_writeoff('WP Media / Imagify Plugin',212, 'WP MEDIA',          495, 32, 'WP Media Imagify Image Optimization');
SELECT _tmp_create_writeoff('Railway.app Cloud Hosting',213, 'RAILWAY',           495, 32, 'Railway.app Cloud Hosting');
SELECT _tmp_create_writeoff('Fiverr Freelance Services',214, 'FIVERR',            495, 32, 'Fiverr Freelance Services');

-- IT Services (HST - Canadian)
SELECT _tmp_create_writeoff('Microsoft 365 Subscription',215, 'Microsoft',        495, 20, 'Microsoft 365 Subscription');
SELECT _tmp_create_writeoff('Webware Website Platform', 216, 'Webware',           495, 20, 'Webware Website Platform');
SELECT _tmp_create_writeoff('Google Workspace',         217, 'WORKSPACE',         495, 20, 'Google Workspace Subscription');

-- Advertising (NO TAX - foreign companies)
SELECT _tmp_create_writeoff('Yelp Advertising',         220, 'YELP',              496, 32, 'Yelp Online Advertising');
SELECT _tmp_create_writeoff('ClickCease Ad Protection', 221, 'CLICKCEASE',        496, 32, 'ClickCease Ad Fraud Protection');
SELECT _tmp_create_writeoff('Kliken / SiteWit Ads',     222, 'KLIKEN',            496, 32, 'Kliken SiteWit Online Advertising');
SELECT _tmp_create_writeoff('Constant Contact Email',   223, 'CONSTANT CONTACT',  496, 32, 'Constant Contact Email Marketing');

-- Advertising (HST - Canadian)
SELECT _tmp_create_writeoff('Yellow Pages Advertising', 224, 'YELLOW PAGES',      496, 20, 'Yellow Pages Directory Advertising');
SELECT _tmp_create_writeoff('Microsoft Advertising',    225, 'MICROSOFT*ADVERTISING', 496, 20, 'Microsoft Bing Advertising');

-- Telephone / Communications
SELECT _tmp_create_writeoff('Bell Canada Telecom',      230, 'BELL CANADA',       523, 20, 'Bell Canada Telephone & Internet');
SELECT _tmp_create_writeoff('eFax Online Fax Service',  231, 'EFAX',              523, 32, 'eFax Online Fax Service');
SELECT _tmp_create_writeoff('Faxdeck Online Fax',       232, 'FAXDECK',           523, 32, 'Faxdeck Online Fax Service');
SELECT _tmp_create_writeoff('RingCentral Phone',        233, 'RINGCENTRAL',       523, 32, 'RingCentral Cloud Phone Service');

-- Subscriptions / Dues
SELECT _tmp_create_writeoff('Scribd Medical Reference', 240, 'SCRIBD',            501, 32, 'Scribd Medical Reference Subscription');
SELECT _tmp_create_writeoff('Amazon Channels',          241, 'Amazon Channel',    501, 20, 'Amazon Channels Subscription');
SELECT _tmp_create_writeoff('Dominion Insurance',       242, 'DOMINION PREM',     501, 32, 'Dominion Insurance Premium');

-- Meals & Entertainment
SELECT _tmp_create_writeoff('Tim Hortons - Meals',      250, 'Tim Horton',        506, 20, 'Tim Hortons Meals');
SELECT _tmp_create_writeoff('Malton Best Restaurant',   251, 'malton best',       506, 20, 'Malton Best Restaurant Meals');

-- Office / Supplies
SELECT _tmp_create_writeoff('Princess Auto - Supplies', 260, 'Princess Auto',     507, 20, 'Princess Auto Supplies');
SELECT _tmp_create_writeoff('Canadian Tire - Supplies', 261, 'CANADIAN TIRE',     507, 20, 'Canadian Tire Office/Shop Supplies');
SELECT _tmp_create_writeoff('Staples Office Supplies',  262, 'STAPLES',           507, 20, 'Staples Office Supplies');
SELECT _tmp_create_writeoff('MGS Business Registration',263, 'MGS-BUSINESS',      507, 20, 'MGS Ontario Business Registration');

-- Shipping
SELECT _tmp_create_writeoff('DHL Express Shipping',     270, 'DHL',               518, 20, 'DHL Express Shipping');
SELECT _tmp_create_writeoff('FedEx Shipping',           271, 'Fedex',             518, 20, 'FedEx Shipping & Delivery');

-- Bank Fees
SELECT _tmp_create_writeoff('Scotia Service Charge',    280, 'Service Charge',    499, 32, 'Scotia Bank Service Charge');

-- Security / Building
SELECT _tmp_create_writeoff('ADT Canada Security',      290, 'ADT CANADA',        557, 20, 'ADT Canada Security Monitoring');
SELECT _tmp_create_writeoff('Seccan Security',          291, 'seccan',            557, 20, 'Seccan Security Services');

-- Utilities
SELECT _tmp_create_writeoff('Alectra Utilities - Hydro',292, 'ALECTRA',           526, 20, 'Alectra Utilities Hydro Payment');

-- ============================================================
-- PART 2: PARTNER-MAPPING MODELS (9 models)
-- ============================================================
SELECT _tmp_create_partner_map('VGM Canada',              300, 'VGM Canada',       5024);
SELECT _tmp_create_partner_map('Medical Mart',            301, 'Medical Mart',     4991);
SELECT _tmp_create_partner_map('AMG Medical',             302, 'AMG medical',      4934);
SELECT _tmp_create_partner_map('HoMedics Group Canada',   303, 'HOMEDICS',         4975);
SELECT _tmp_create_partner_map('Stevens Company Limited', 304, 'Stevens Company',  5017);
SELECT _tmp_create_partner_map('Ki Mobility Canada',      305, 'Ki Mobility',      4981);
SELECT _tmp_create_partner_map('R82 Inc',                 306, 'R82',              5009);
SELECT _tmp_create_partner_map('Harmony Group / Products',307, 'HARMONY',          6216);
SELECT _tmp_create_partner_map('Continent Globe Freight', 308, 'CONTINENT GLOBE',  NULL);

-- Cleanup temp functions
DROP FUNCTION _tmp_create_writeoff(text, int, text, int, int, text);
DROP FUNCTION _tmp_create_partner_map(text, int, text, int);

COMMIT;