\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION expect_fail(sql text, label text) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  BEGIN EXECUTE sql; RAISE EXCEPTION 'FAIL (should have been blocked): %', label;
  EXCEPTION WHEN raise_exception OR check_violation OR unique_violation OR not_null_violation THEN
    IF SQLERRM LIKE 'FAIL (%' THEN RAISE; END IF; RAISE NOTICE 'blocked OK: % -> %', label, SQLERRM; END;
END $$;
INSERT INTO users (name,email,role,access_level) VALUES ('Staff A','staffa@asgb.com.my','operations','OPS'),('Fin','finance@asgb.com.my','finance','FIN');
SELECT set_config('app.user_id', (SELECT id::text FROM users WHERE email='staffa@asgb.com.my'), false);
INSERT INTO aim_branches VALUES ('SA','Shah Alam','Selangor'); INSERT INTO zus_branches VALUES ('S7','ZUS Seksyen 7','Selangor');
INSERT INTO merchants (name,owner_name,ic_or_company_no,contact_no,email,aim_branch,state,assigned_pic_id)
  VALUES ('Kopi Test','Owner','900101-10-1111','0123','o@x.com','SA','Selangor',(SELECT id FROM users WHERE email='staffa@asgb.com.my'));
SELECT merchant_id FROM merchants;
INSERT INTO applications VALUES ('APP-2026-0001','ZS-00001','2026-09-01','Lot 1','ready_for_location_screening');
SELECT expect_fail($$INSERT INTO location_submissions (merchant_id,attempt_no,proposed_location,status) VALUES ('ZS-00001',1,'Lot 1','rejected')$$,'reject without reason');
INSERT INTO location_submissions (merchant_id,attempt_no,proposed_location,status) VALUES ('ZS-00001',1,'Lot 1','approved');
SELECT expect_fail($$INSERT INTO onboarding_steps (merchant_id,step,status) VALUES ('ZS-00001','agreement','in_progress')$$,'onboarding before docs');
INSERT INTO documents (merchant_id,doc_type,file_key,file_name,mime_type,size_bytes,sha256,uploaded_by)
  SELECT 'ZS-00001', code, 'k/'||code, code||'.pdf','application/pdf',1000, md5(code), (SELECT id FROM users WHERE email='staffa@asgb.com.my') FROM document_types WHERE mandatory;
SELECT expect_fail($$INSERT INTO documents (merchant_id,doc_type,version,file_key,file_name,mime_type,size_bytes,sha256,uploaded_by) SELECT 'ZS-00001','ssm',2,'k','a.exe','application/x-msdownload',10,'zz',id FROM users LIMIT 1$$,'bad file type');
SELECT expect_fail($$INSERT INTO document_verifications (document_id,action,verified_by) SELECT d.id,'verify',u.id FROM documents d, users u WHERE u.email='staffa@asgb.com.my' LIMIT 1$$,'ops verifies');
SELECT expect_fail($$INSERT INTO document_verifications (document_id,action,verified_by) SELECT d.id,'reject',u.id FROM documents d, users u WHERE u.email='asyraaf@asgb.com.my' LIMIT 1$$,'reject without reason');
INSERT INTO document_verifications (document_id,action,verified_by) SELECT d.id,'verify',u.id FROM documents d, users u WHERE u.email='asyraaf@asgb.com.my';
SELECT complete FROM v_document_completion WHERE merchant_id='ZS-00001';
INSERT INTO onboarding_steps (merchant_id,step,status) SELECT 'ZS-00001', s, 'completed' FROM unnest(enum_range(NULL::onboarding_step)) s WHERE s <> 'starter_kit';
SELECT expect_fail($$UPDATE merchants SET status='active', activated_at='2026-10-01' WHERE merchant_id='ZS-00001'$$,'activate with step missing');
INSERT INTO onboarding_steps (merchant_id,step,status) VALUES ('ZS-00001','starter_kit','completed');
UPDATE merchants SET status='active', activated_at='2026-10-01', zus_branch='S7' WHERE merchant_id='ZS-00001';
-- tasks
INSERT INTO tasks (merchant_id,title,pic_id,due_date,status,completed_by) SELECT 'ZS-00001','Collect SSM',id,'2026-11-10','pending_verification',id FROM users WHERE email='staffa@asgb.com.my';
SELECT expect_fail($$UPDATE tasks SET verified_by=completed_by, verification_status='verified'$$,'ops verifies own task');
UPDATE tasks SET verified_by=(SELECT id FROM users WHERE email='fatin.syafiqah@asgb.com.my'), verification_status='verified', status='completed';
-- sales & settlement
INSERT INTO sales (sales_date,merchant_id,sales_amount,item_count,pos_reference,uploaded_by) SELECT '2026-10-15','ZS-00001',5000,400,'POS-1',id FROM users WHERE email='staffa@asgb.com.my';
SELECT expect_fail($$INSERT INTO sales (sales_date,merchant_id,sales_amount,item_count,pos_reference,uploaded_by) SELECT '2026-10-15','ZS-00001',100,4,'POS-2',id FROM users LIMIT 1$$,'duplicate sales day');
SELECT expect_fail($$INSERT INTO sales (sales_date,merchant_id,sales_amount,item_count,pos_reference,uploaded_by) SELECT '2026-10-16','ZS-00001',-5,4,'POS-3',id FROM users LIMIT 1$$,'negative amount');
INSERT INTO settlement_batches (settlement_date) VALUES ('2026-10-15');
INSERT INTO settlements (batch_id,settlement_date,merchant_id,pos_sales,expected_amount,destination_account_id) SELECT b.id,'2026-10-15','ZS-00001',5000,5000,1 FROM settlement_batches b;
SELECT status FROM settlements;
UPDATE settlements SET actual_amount=4800, bank_reference='DN1';
SELECT status, difference FROM settlements;
UPDATE settlements SET actual_amount=5000;
SELECT status FROM settlements;
INSERT INTO fund_allocations (settlement_id,merchant_id,allocation_date,rule_id,bucket_key,percentage,base_amount,amount,bank_account_id)
  SELECT s.id,'ZS-00001','2026-10-15',b.rule_id,b.bucket_key,b.percentage,5000,5000*b.percentage/100,b.bank_account_id FROM settlements s, allocation_rule_buckets b;
SELECT bucket_key, total_allocated FROM v_merchant_fund_balance;
-- restock guard (available = 2000)
INSERT INTO restock_orders (merchant_id) VALUES ('ZS-00001');
INSERT INTO restock_order_items (order_id,item_id,quantity,unit_price) VALUES ('RO-00001',1,20,65);
SELECT restock_available('ZS-00001') AS avail_after_1300;
SELECT expect_fail($$INSERT INTO restock_order_items (order_id,item_id,quantity,unit_price) VALUES ('RO-00001',2,200,7.5)$$,'restock over balance');
SELECT total_amount FROM restock_orders;
-- bank txn unmatched blocks lock
INSERT INTO bank_transactions (txn_date,amount,reference,bank_account_id) VALUES ('2026-10-20',100,'IBG1',1);
SELECT expect_fail($$INSERT INTO monthly_closings (period,status,verified_by,locked_at) SELECT '2026-10-01','locked',id,now() FROM users WHERE email='asyraaf@asgb.com.my'$$,'lock with unmatched bank txn');
UPDATE bank_transactions SET status='matched', matched_settlement_id=(SELECT id FROM settlements), match_evidence='manual';
SELECT expect_fail($$INSERT INTO monthly_closings (period,status,verified_by,locked_at) SELECT '2026-10-01','locked',id,now() FROM users WHERE email='siti@asgb.com.my'$$,'management locks');
INSERT INTO monthly_closings (period,status,verified_by,locked_at) SELECT '2026-10-01','locked',id,now() FROM users WHERE email='asyraaf@asgb.com.my';
SELECT expect_fail($$UPDATE sales SET sales_amount=4900$$,'edit locked sale');
SELECT expect_fail($$UPDATE monthly_closings SET status='open'$$,'reopen month');
-- adjustment path
INSERT INTO adjustment_requests (table_name,record_id,field_name,original_value,correct_value,reason,requested_by) SELECT 'sales','1','sales_amount','5000','4900','POS correction',id FROM users WHERE email='staffa@asgb.com.my';
SELECT expect_fail($$UPDATE adjustment_requests SET status='approved', approved_by=requested_by$$,'self approve adjustment');
UPDATE adjustment_requests SET status='approved', approved_by=(SELECT id FROM users WHERE email='siti@asgb.com.my'), approved_at=now();
BEGIN; SET LOCAL app.adjustment_id='1'; UPDATE sales SET sales_amount=4900; COMMIT;
SELECT sales_amount FROM sales;
-- issues
INSERT INTO issues (merchant_id,category,description,raised_by,resolution,status) SELECT 'ZS-00001','POS','x',id,'fixed','resolved' FROM users WHERE email='staffa@asgb.com.my';
SELECT expect_fail($$UPDATE issues SET status='closed'$$,'ops closes issue');
-- repayment view
INSERT INTO repayment_plans (merchant_id, first_due_date) VALUES ('ZS-00001','2026-11-10');
SELECT instalments_due, outstanding, status FROM v_repayment_status;
-- audit permanence
SELECT count(*) AS audit_rows FROM audit_logs;
SELECT expect_fail($$DELETE FROM audit_logs$$,'delete audit');
SELECT expect_fail($$UPDATE audit_logs SET remarks='x'$$,'edit audit');
SELECT module, action, entity, user_email FROM audit_logs ORDER BY id DESC LIMIT 3;
SELECT merchant_id, documents_complete, onboarding_completed_steps, merchant_status, pic_email FROM v_merchant_master;
SELECT stock_status FROM v_stock LIMIT 1;