CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS organizations (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL, short_name text,
 currency char(3) NOT NULL DEFAULT 'KES', status text NOT NULL DEFAULT 'active',
 created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS users (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), full_name text NOT NULL,
 email text UNIQUE, phone text, password_hash text, status text NOT NULL DEFAULT 'active',
 created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS organization_memberships (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, role text NOT NULL,
 status text NOT NULL DEFAULT 'active', created_at timestamptz NOT NULL DEFAULT now(),
 UNIQUE(organization_id,user_id)
);

-- Platform-level staff are separate from organization memberships.
-- Platform administrators and management team members can oversee multiple tenants.
CREATE TABLE IF NOT EXISTS platform_roles (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 role text NOT NULL CHECK (role IN ('platform_admin','management')),
 status text NOT NULL DEFAULT 'active',
 permissions jsonb NOT NULL DEFAULT '{}'::jsonb,
 created_at timestamptz NOT NULL DEFAULT now(),
 UNIQUE(user_id)
);
CREATE INDEX IF NOT EXISTS idx_platform_roles_role ON platform_roles(role,status);
CREATE TABLE IF NOT EXISTS members (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 member_no text NOT NULL, full_name text NOT NULL, phone text, national_id text, status text NOT NULL DEFAULT 'active',
 created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(organization_id,member_no), UNIQUE(organization_id,national_id)
);
CREATE TABLE IF NOT EXISTS contributions (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 member_id uuid NOT NULL REFERENCES members(id), contribution_date date NOT NULL, amount numeric(14,2) NOT NULL,
 contribution_type text NOT NULL, reference text, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS loans (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 member_id uuid NOT NULL REFERENCES members(id), principal numeric(14,2) NOT NULL, balance numeric(14,2) NOT NULL,
 status text NOT NULL DEFAULT 'pending', approved_by uuid, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS welfare_cases (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 member_id uuid NOT NULL REFERENCES members(id), case_type text NOT NULL, amount numeric(14,2) DEFAULT 0,
 status text NOT NULL DEFAULT 'pending', created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS assets (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 member_id uuid REFERENCES members(id), asset_type text NOT NULL, description text, agreement_ref text,
 status text NOT NULL DEFAULT 'active', created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS audit_logs (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
 user_id uuid REFERENCES users(id), action text NOT NULL, entity_type text, entity_id uuid,
 details jsonb, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_members_org ON members(organization_id);
CREATE INDEX IF NOT EXISTS idx_contributions_org_date ON contributions(organization_id,contribution_date);
CREATE INDEX IF NOT EXISTS idx_loans_org ON loans(organization_id);
CREATE INDEX IF NOT EXISTS idx_audit_org_time ON audit_logs(organization_id,created_at);


CREATE TABLE IF NOT EXISTS loan_products (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 name text NOT NULL, code text NOT NULL, description text, interest_rate numeric(8,4) NOT NULL DEFAULT 0,
 interest_method text NOT NULL DEFAULT 'flat', processing_fee numeric(14,2) NOT NULL DEFAULT 0,
 term_days integer NOT NULL DEFAULT 30, min_amount numeric(14,2) NOT NULL DEFAULT 0, max_amount numeric(14,2) NOT NULL DEFAULT 0,
 enabled boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(organization_id,code)
);
CREATE TABLE IF NOT EXISTS loan_requests (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 member_id uuid NOT NULL REFERENCES members(id), loan_product_id uuid NOT NULL REFERENCES loan_products(id),
 amount numeric(14,2) NOT NULL, purpose text, requested_term_days integer NOT NULL DEFAULT 30,
 status text NOT NULL DEFAULT 'pending', reviewed_by uuid REFERENCES users(id), reviewed_at timestamptz,
 created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS loan_payments (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
 loan_id uuid NOT NULL REFERENCES loans(id), member_id uuid NOT NULL REFERENCES members(id), amount numeric(14,2) NOT NULL,
 payment_method text NOT NULL, reference text, deduction_source text, created_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE loan_requests ADD COLUMN IF NOT EXISTS disbursement_destination_type text;
ALTER TABLE loan_requests ADD COLUMN IF NOT EXISTS disbursement_destination_value text;
ALTER TABLE loans ADD COLUMN IF NOT EXISTS loan_product_id uuid REFERENCES loan_products(id);
ALTER TABLE loans ADD COLUMN IF NOT EXISTS interest_amount numeric(14,2) NOT NULL DEFAULT 0;
ALTER TABLE loans ADD COLUMN IF NOT EXISTS processing_fee numeric(14,2) NOT NULL DEFAULT 0;
ALTER TABLE loans ADD COLUMN IF NOT EXISTS disbursed_at timestamptz;
CREATE INDEX IF NOT EXISTS idx_loan_requests_org_status ON loan_requests(organization_id,status);
CREATE INDEX IF NOT EXISTS idx_loan_payments_loan ON loan_payments(loan_id,created_at);

CREATE TABLE IF NOT EXISTS mpesa_incoming_payments (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 organization_key text NOT NULL,
 reference text NOT NULL UNIQUE,
 amount numeric(14,2) NOT NULL,
 account_number text NOT NULL,
 payment_date text,
 msisdn text,
 first_name text,
 middle_name text,
 last_name text,
 status text NOT NULL DEFAULT 'received',
 created_at timestamptz NOT NULL DEFAULT now(),
 approved_at timestamptz,
 approved_by uuid REFERENCES users(id)
);
CREATE INDEX IF NOT EXISTS idx_mpesa_incoming_account ON mpesa_incoming_payments(account_number,status);
CREATE INDEX IF NOT EXISTS idx_mpesa_incoming_org ON mpesa_incoming_payments(organization_key,status);

CREATE TABLE IF NOT EXISTS incoming_payment_messages (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 organization_key text NOT NULL,
 channel text NOT NULL CHECK (channel IN ('mpesa','bank','other')),
 sender text,
 raw_message text NOT NULL,
 reference text,
 amount numeric(14,2),
 account_number text,
 payment_date text,
 msisdn text,
 status text NOT NULL DEFAULT 'received',
 created_at timestamptz NOT NULL DEFAULT now(),
 processed_at timestamptz,
 UNIQUE(organization_key, reference)
);
CREATE INDEX IF NOT EXISTS idx_incoming_payment_messages_org_status ON incoming_payment_messages(organization_key,status,created_at);
CREATE INDEX IF NOT EXISTS idx_incoming_payment_messages_account ON incoming_payment_messages(account_number,status);