-- 004 authentication read facade. Prerequisite: migrations 001-003.
BEGIN;
SET LOCAL search_path TO optikrapor,pg_catalog;
CREATE OR REPLACE FUNCTION optikrapor.login_lookup(p_email text)
RETURNS TABLE(user_id bigint,password_hash text,display_name varchar(160),is_active boolean)
LANGUAGE sql SECURITY DEFINER
SET search_path = pg_catalog,optikrapor
AS $$
 SELECT id,password_hash,display_name,is_active
 FROM optikrapor.users
 WHERE lower(email)=lower(trim(p_email))
 LIMIT 1
$$;
REVOKE ALL ON FUNCTION optikrapor.login_lookup(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION optikrapor.login_lookup(text) TO oprapr;
CREATE OR REPLACE FUNCTION optikrapor.login_memberships(p_user_id bigint)
RETURNS TABLE(company_id bigint,company_name varchar(200),branch_id bigint,branch_name varchar(200),role_code varchar(40))
LANGUAGE sql SECURITY DEFINER
SET search_path=pg_catalog,optikrapor
AS $$
 SELECT m.company_id,c.name,m.branch_id,b.name,r.code
 FROM optikrapor.user_memberships m
 JOIN optikrapor.companies c ON c.id=m.company_id
 JOIN optikrapor.roles r ON r.id=m.role_id
 LEFT JOIN optikrapor.branches b ON b.id=m.branch_id AND b.company_id=m.company_id
 JOIN optikrapor.users u ON u.id=m.user_id
 WHERE m.user_id=p_user_id AND m.is_active AND u.is_active AND c.status='active'
 ORDER BY c.name,b.name NULLS FIRST
$$;
REVOKE ALL ON FUNCTION optikrapor.login_memberships(bigint) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION optikrapor.login_memberships(bigint) TO oprapr;
INSERT INTO optikrapor.schema_migrations(version,description) VALUES('004','Authentication lookup functions for PHP login');
COMMIT;