-- Run after loading schema: psql -d <db> -v ON_ERROR_STOP=0 -f tests/schema_rules_test.sql
-- Each block prints PASS or FAIL.
\set QUIET on
CREATE OR REPLACE FUNCTION _expect_error(label text, stmt text, must_contain text DEFAULT NULL) RETURNS void AS $$
BEGIN
    EXECUTE stmt;
    RAISE NOTICE 'FAIL  %  (statement was allowed)', label;
EXCEPTION WHEN OTHERS THEN
    IF must_contain IS NOT NULL AND position(must_contain in SQLERRM) = 0 THEN
        RAISE NOTICE 'FAIL  %  (rejected for the wrong reason: %)', label, left(SQLERRM, 80);
    ELSE
        RAISE NOTICE 'PASS  %  [%]', label, left(SQLERRM, 70);
    END IF;
END $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION _expect_ok(label text, stmt text) RETURNS void AS $$
BEGIN
    EXECUTE stmt;
    RAISE NOTICE 'PASS  %', label;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'FAIL  %  [%]', label, SQLERRM;
END $$ LANGUAGE plpgsql;

BEGIN;
-- staff rules
SELECT _expect_ok('first (primary) admin can be created',
 $$INSERT INTO staff_users(name,email,password_hash,role,is_primary) VALUES ('Primary','a1@x.ng','h','admin',true)$$);
SELECT _expect_error('second primary admin is blocked',
 $$INSERT INTO staff_users(name,email,password_hash,role,is_primary) VALUES ('P2','a2@x.ng','h','admin',true)$$, 'staff_one_primary');
SELECT _expect_ok('second admin can be created',
 $$INSERT INTO staff_users(name,email,password_hash,role) VALUES ('Second','a3@x.ng','h','admin')$$);
SELECT _expect_error('third admin is blocked (max 2)',
 $$INSERT INTO staff_users(name,email,password_hash,role) VALUES ('Third','a4@x.ng','h','admin')$$, 'at most 2 Admins');
SELECT _expect_error('primary admin cannot be soft-deleted',
 $$UPDATE staff_users SET deleted_at = now() WHERE email='a1@x.ng'$$);
SELECT _expect_error('primary admin cannot be hard-deleted',
 $$DELETE FROM staff_users WHERE email='a1@x.ng'$$);
SELECT _expect_error('primary admin cannot be demoted',
 $$UPDATE staff_users SET role='manager' WHERE email='a1@x.ng'$$, 'cannot be demoted');
SELECT _expect_error('primary flag must belong to an admin',
 $$INSERT INTO staff_users(name,email,password_hash,role,is_primary) VALUES ('Bad','a5@x.ng','h','manager',true)$$);
SELECT _expect_ok('second admin CAN be deleted by primary (soft delete)',
 $$UPDATE staff_users SET deleted_at = now() WHERE email='a3@x.ng'$$);
SELECT _expect_ok('after deleting second admin, a new second admin fits',
 $$INSERT INTO staff_users(name,email,password_hash,role) VALUES ('Second B','a6@x.ng','h','admin')$$);
SELECT _expect_ok('manager and supervisor can be created',
 $$INSERT INTO staff_users(name,email,password_hash,role) VALUES ('M','m@x.ng','h','manager'),('S','s@x.ng','h','supervisor')$$);

-- audit log immutability
SELECT _expect_ok('audit log accepts inserts',
 $$INSERT INTO audit_logs(actor_type,action) VALUES ('system','test')$$);
SELECT _expect_error('audit log cannot be updated',  $$UPDATE audit_logs SET action='x'$$);
SELECT _expect_error('audit log cannot be deleted',  $$DELETE FROM audit_logs$$);

-- users
SELECT _expect_ok('valid user', $$INSERT INTO users(username,display_name,email,referral_code) VALUES ('avantgreen','Avant','u1@x.ng','R1')$$);
SELECT _expect_error('username is case-insensitive unique', $$INSERT INTO users(username,display_name,email,referral_code) VALUES ('AvantGreen','Dup','u2@x.ng','R2')$$);
SELECT _expect_error('username with spaces rejected',  $$INSERT INTO users(username,display_name,email,referral_code) VALUES ('bad name','X','u3@x.ng','R3')$$);
SELECT _expect_error('username too short rejected',    $$INSERT INTO users(username,display_name,email,referral_code) VALUES ('ab','X','u4@x.ng','R4')$$);
SELECT _expect_error('user needs email or phone',      $$INSERT INTO users(username,display_name,referral_code) VALUES ('nocontact','X','R5')$$);
SELECT _expect_ok('phone-only user allowed',           $$INSERT INTO users(username,display_name,phone,referral_code) VALUES ('phoneuser','P','+2348012345678','R6')$$);

-- posts: anonymity, location, search vector
SELECT _expect_ok('LGA + town for post tests',
 $$WITH l AS (INSERT INTO lgas(state_id,name,slug) VALUES (33,'Ikeja','ikeja') RETURNING id)
   INSERT INTO towns(lga_id,name,slug) SELECT id,'Alausa','alausa' FROM l$$);
SELECT _expect_ok('normal post accepted',
 $$INSERT INTO posts(author_id,title,slug,body,state_id,lga_id,town_id)
   SELECT u.id,'Pothole the size of a pool','pothole-pool','Large pothole on main road',33,l.id,t.id
   FROM users u, lgas l, towns t WHERE u.username='avantgreen' AND l.slug='ikeja' AND t.slug='alausa'$$);
SELECT _expect_error('anonymous post may not carry an author_id',
 $$INSERT INTO posts(author_id,is_anonymous,title,slug,body,state_id,lga_id,town_id)
   SELECT u.id,true,'Anon','anon-bad','x',33,l.id,t.id FROM users u, lgas l, towns t
   WHERE u.username='avantgreen' AND l.slug='ikeja' AND t.slug='alausa'$$);
SELECT _expect_ok('anonymous post without author accepted',
 $$INSERT INTO posts(is_anonymous,title,slug,body,state_id,lga_id,town_id)
   SELECT true,'Anon ok','anon-ok','x',33,l.id,t.id FROM lgas l, towns t WHERE l.slug='ikeja' AND t.slug='alausa'$$);
DO $$ BEGIN
  IF (SELECT count(*) FROM posts WHERE search_vector @@ to_tsquery('simple','pothole')) = 1
  THEN RAISE NOTICE 'PASS  full-text search finds the post'; ELSE RAISE NOTICE 'FAIL  full-text search'; END IF;
END $$;

-- themes: only one active
SELECT _expect_ok('theme A active', $$INSERT INTO themes(slug,name,version,thumbnail_path,is_active) VALUES ('aurora','Aurora','1.0.0','thumbnail.jpg',true)$$);
SELECT _expect_error('second active theme blocked', $$INSERT INTO themes(slug,name,version,thumbnail_path,is_active) VALUES ('ember','Ember','1.0.0','thumbnail.jpg',true)$$);
SELECT _expect_ok('inactive theme allowed', $$INSERT INTO themes(slug,name,version,thumbnail_path) VALUES ('ember','Ember','1.0.0','thumbnail.jpg')$$);

-- CMS: alt text mandatory
SELECT _expect_error('page image without alt text rejected', $$INSERT INTO page_images(path,alt_text) VALUES ('a.jpg','')$$, 'page_images_alt_text_check');
SELECT _expect_ok('page image with alt text accepted',       $$INSERT INTO page_images(path,alt_text) VALUES ('a.jpg','Crowd at a town hall')$$);

-- sanctions
SELECT _expect_error('suspension without end date rejected',
 $$INSERT INTO user_sanctions(user_id,type,reason,issued_by_staff_id)
   SELECT u.id,'suspension','r',s.id FROM users u, staff_users s WHERE u.username='avantgreen' AND s.email='m@x.ng'$$);
SELECT _expect_ok('ban without end date accepted',
 $$INSERT INTO user_sanctions(user_id,type,reason,issued_by_staff_id)
   SELECT u.id,'ban','r',s.id FROM users u, staff_users s WHERE u.username='avantgreen' AND s.email='m@x.ng'$$);

-- shop: totals and stock
SELECT _expect_error('order total must equal subtotal - discount + shipping',
 $$INSERT INTO orders(order_number,customer_name,customer_email,customer_phone,shipping_address,subtotal_kobo,discount_kobo,shipping_kobo,total_kobo)
   VALUES ('AB1','n','e@x.ng','1','{}',1000,0,0,999)$$, 'order_total_matches');
SELECT _expect_ok('consistent order accepted',
 $$INSERT INTO orders(order_number,customer_name,customer_email,customer_phone,shipping_address,subtotal_kobo,discount_kobo,shipping_kobo,total_kobo)
   VALUES ('AB2','n','e@x.ng','1','{}',1000,100,500,1400)$$);
SELECT _expect_error('negative wallet balance rejected',
 $$INSERT INTO advertiser_wallets(advertiser_id,balance_kobo) VALUES (1,-5)$$);

-- Champions: official needs record; reaction vocabulary has no dislike
SELECT _expect_error('post reaction "dislike" rejected',
 $$INSERT INTO post_reactions(post_id,user_id,type) SELECT p.id,u.id,'dislike' FROM posts p, users u LIMIT 1$$);

-- defaults that must be OFF at launch
DO $$ BEGIN
  IF (SELECT count(*) FROM site_settings WHERE key IN ('ads_enabled','profit_sharing_enabled','shop_enabled','social_autopost_enabled') AND value = 'false'::jsonb) = 4
  THEN RAISE NOTICE 'PASS  ads, profit sharing, shop and social auto-post all default OFF';
  ELSE RAISE NOTICE 'FAIL  launch defaults'; END IF;
  IF (SELECT count(*) FROM states) = 37 THEN RAISE NOTICE 'PASS  37 states (36 + FCT) seeded'; ELSE RAISE NOTICE 'FAIL  states count'; END IF;
  IF (SELECT count(*) FROM role_permissions WHERE role='admin') = (SELECT count(*) FROM permissions) THEN RAISE NOTICE 'PASS  admin holds every permission'; ELSE RAISE NOTICE 'FAIL  admin permissions'; END IF;
  IF NOT EXISTS (SELECT 1 FROM role_permissions WHERE role='manager' AND permission_code IN ('users.create','users.delete','staff.manage_admin','staff.manage_manager','features.toggle'))
  THEN RAISE NOTICE 'PASS  manager cannot create/delete members, manage admins/managers, or toggle features'; ELSE RAISE NOTICE 'FAIL  manager permissions'; END IF;
  IF NOT EXISTS (SELECT 1 FROM role_permissions WHERE role='supervisor' AND permission_code IN ('users.view_pii','users.suspend','users.create','users.update','users.delete') OR role='supervisor' AND permission_code LIKE 'staff.%')
  THEN RAISE NOTICE 'PASS  supervisor has no PII, user-management or staff-management rights'; ELSE RAISE NOTICE 'FAIL  supervisor permissions'; END IF;
END $$;
ROLLBACK;