-- =========================================================
-- CUTEHORSE APP ROLE SETUP (SOFT DELETE SAFE)
-- Database: rails_erp
-- Role: cutehorse_app
-- =========================================================

-- 1. Create application role
DO
$$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_roles
        WHERE rolname = 'cutehorse_app'
    ) THEN
        CREATE ROLE cutehorse_app WITH LOGIN PASSWORD 'YourStrongPasswordHere';
    END IF;
END
$$;

-- 2. Allow connection to the database
GRANT CONNECT ON DATABASE rails_erp TO cutehorse_app;

-- =========================================================
-- 3. Schema access
-- =========================================================

GRANT USAGE ON SCHEMA platform TO cutehorse_app;
GRANT USAGE ON SCHEMA iam TO cutehorse_app;
GRANT USAGE ON SCHEMA org TO cutehorse_app;
GRANT USAGE ON SCHEMA master TO cutehorse_app;
GRANT USAGE ON SCHEMA workflow TO cutehorse_app;
GRANT USAGE ON SCHEMA audit TO cutehorse_app;
GRANT USAGE ON SCHEMA notify TO cutehorse_app;
GRANT USAGE ON SCHEMA files TO cutehorse_app;
GRANT USAGE ON SCHEMA crm TO cutehorse_app;
GRANT USAGE ON SCHEMA contacts TO cutehorse_app;
GRANT USAGE ON SCHEMA sales TO cutehorse_app;
GRANT USAGE ON SCHEMA purchases TO cutehorse_app;
GRANT USAGE ON SCHEMA suppliers TO cutehorse_app;
GRANT USAGE ON SCHEMA inventory TO cutehorse_app;
GRANT USAGE ON SCHEMA warehouse TO cutehorse_app;
GRANT USAGE ON SCHEMA manufacturing TO cutehorse_app;
GRANT USAGE ON SCHEMA hr_payroll TO cutehorse_app;
GRANT USAGE ON SCHEMA accounting TO cutehorse_app;
GRANT USAGE ON SCHEMA assets TO cutehorse_app;
GRANT USAGE ON SCHEMA productivity TO cutehorse_app;
GRANT USAGE ON SCHEMA reports TO cutehorse_app;
GRANT USAGE ON SCHEMA settings TO cutehorse_app;
GRANT USAGE ON SCHEMA support TO cutehorse_app;
GRANT USAGE ON SCHEMA pos TO cutehorse_app;

-- =========================================================
-- 4. Table privileges (NO DELETE)
-- Soft delete means application uses UPDATE, not DELETE
-- =========================================================

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA platform TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA iam TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA org TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA master TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA workflow TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA audit TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA notify TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA files TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA crm TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA contacts TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA sales TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA purchases TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA suppliers TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA inventory TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA warehouse TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA manufacturing TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA hr_payroll TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA accounting TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA assets TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA productivity TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA reports TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA settings TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA support TO cutehorse_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA pos TO cutehorse_app;

-- =========================================================
-- 5. Sequence privileges
-- Required for inserts into BIGSERIAL / identity-backed tables
-- =========================================================

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA platform TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA iam TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA org TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA master TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA workflow TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA audit TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA notify TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA files TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA crm TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA contacts TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA sales TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA purchases TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA suppliers TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA inventory TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA warehouse TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA manufacturing TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA hr_payroll TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA accounting TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA assets TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA productivity TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA reports TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA settings TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA support TO cutehorse_app;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA pos TO cutehorse_app;

-- =========================================================
-- 6. Default privileges for FUTURE tables
-- IMPORTANT:
-- Run these as the role that creates tables
-- (often postgres or your migration role)
-- =========================================================

ALTER DEFAULT PRIVILEGES IN SCHEMA platform
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA iam
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA org
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA master
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA workflow
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA audit
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA notify
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA files
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA crm
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA contacts
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA purchases
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA suppliers
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA inventory
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA warehouse
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA manufacturing
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA hr_payroll
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA accounting
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA assets
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA productivity
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA reports
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA settings
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA support
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA pos
GRANT SELECT, INSERT, UPDATE ON TABLES TO cutehorse_app;

-- =========================================================
-- 7. Default privileges for FUTURE sequences
-- =========================================================

ALTER DEFAULT PRIVILEGES IN SCHEMA platform
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA iam
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA org
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA master
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA workflow
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA audit
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA notify
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA files
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA crm
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA contacts
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA purchases
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA suppliers
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA inventory
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA warehouse
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA manufacturing
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA hr_payroll
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA accounting
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA assets
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA productivity
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA reports
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA settings
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA support
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA pos
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;