SELECT
    policy_id,
    -- first charge
    JSON_EXTRACT_SCALAR(charges, '$[0].name')
        AS "charge_0_name",
    CAST(JSON_EXTRACT(charges, '$[0].amount') AS INTEGER)
        AS "charge_0_amount",
    -- second charge
    JSON_EXTRACT_SCALAR(charges, '$[1].name')
        AS "charge_1_name",
    CAST(JSON_EXTRACT(charges, '$[1].amount') AS INTEGER)
        AS "charge_1_amount",
    -- last charge
    JSON_ARRAY_GET(charges, -1)
        AS "charges_last",
    JSON_EXTRACT_SCALAR(JSON_ARRAY_GET(charges, -1), '$.name')
        AS "charges_last_name",
    CAST(JSON_EXTRACT(JSON_ARRAY_GET(charges, -1), '$.amount') AS INTEGER)
        AS "charges_last_amount"
FROM
    policies
LIMIT 100
;

SELECT
    count(*)
FROM
    policies
WHERE
    -- specify dates relative to now (eg. not older than 1 month)
    AND created_at > NOW() - INTERVAL '1' MONTH

SELECT
    policy_id,
    JSON_ARRAY_GET(schedule_versions, -1)
        AS "last_schedule_version",
    -- parse string to ensure valid date
    from_iso8601_timestamp(
        JSON_EXTRACT_SCALAR(
            JSON_ARRAY_GET(schedule_versions, -1),
            '$.createdAt'
        )
    ) AS "last_schedule_created",
    -- cast to timestamp for further comparison
    CAST (
        from_iso8601_timestamp(
            JSON_EXTRACT_SCALAR(
                JSON_ARRAY_GET(schedule_versions, -1),
                '$.createdAt'
            )
        )
        AS timestamp
    ) AS "last_schedule_created_timestamp"
FROM
    policies
LIMIT 100

SELECT
    policy_id,
    created_at,
    -- display timestamp in specific timezone
    created_at AT TIME ZONE 'UTC' AS "created_at_utc",
    created_at AT TIME ZONE 'Africa/Johannesburg' AS "created_at_jhb",
    created_at AT TIME ZONE 'America/Los_Angeles' AS "created_at_la",
    -- format timestamp
    format_datetime(
        created_at AT TIME ZONE 'Africa/Johannesburg',
        'E, d MMMM y H:m:s Z'
    ) AS "created_at_format"
FROM
    policies
WHERE
    -- specify range in specific timezone
    created_at
        BETWEEN timestamp '2022-11-01 00:00:00 Africa/Johannesburg'
        AND timestamp '2022-12-01 00:00:00 Africa/Johannesburg'
    -- specify dates relative to now (eg. not older than 1 year)
    AND created_at > NOW() - INTERVAL '1' YEAR
LIMIT 100

SELECT
    regexp_replace(
        persistence_key,
        '^.+-policy:',
        ''
    ) AS "policy_id",
    created_at,
    json_extract_scalar(data, '$.type') AS "type",
    sequence,
    version,
    data,
    CASE
        json_extract_scalar(data, '$.metadata.requested_by.type')
        WHEN 'user' THEN json_extract_scalar(
            data,
            '$.metadata.requested_by.id'
        )
    END AS "requested_by_user_id",
    persistence_key
FROM
    policy_events
LIMIT 100

CREATE VIEW policy_events_view AS
    SELECT
        regexp_replace(
            persistence_key,
            '^.+-policy:',
            ''
        ) AS "policy_id",
        created_at,
        json_extract_scalar(data, '$.type') AS "type",
        sequence,
        version,
        data,
        CASE
            json_extract_scalar(data, '$.metadata.requested_by.type')
            WHEN 'user' THEN json_extract_scalar(data, '$.metadata.requested_by.id')
        END AS "requested_by_user_id",
        persistence_key
    FROM
        policy_events

SELECT
    pol.policy_id,
    ev.created_at AS "activated_at",
    usr.email AS "activated_by"
FROM policies AS pol
LEFT JOIN policy_events_view AS ev
    ON ev.policy_id = pol.policy_id
    AND ev.type = 'policy_activated'
LEFT JOIN users AS usr
    ON usr.id = ev.requested_by_user_id
LIMIT 100

DROP TABLE policy_events_view

SELECT
	*
FROM
	payments
WHERE
	environment = 'production' -- Limits results to the production environment
	AND finalized_at >= CURRENT_DATE - INTERVAL '1' month -- Limits results to where the finalized_at is within the last month
	AND status = 'failed' -- Limits results to payments with a status of failed
ORDER BY
	finalized_at; -- Orders the results from oldest to most recent finalized payment.

SELECT
        policy_id,
        payment_method_id,
        collection_type,
        payment_id,
        description AS payment_description,
        amount, -- In cents
        currency,
        status AS payment_status,
        failure_code,
        failure_reason,
        json_extract_scalar(created_by, '$.type') AS created_by,
        cast(billing_date AS date) AS billing_date,
        cast(created_at AS date) AS payment_created_at,
        cast(action_date AS date) AS action_date,
        cast(submitted_at AS date) AS submitted_date,
        cast(finalized_at AS date) AS finalized_at,
        json_extract_scalar(finalized_by, '$.type') AS finalized_by,
        json_extract_scalar(submitted_by, '$.type') AS submitted_by
FROM
        payments
WHERE
        environment = 'production'  -- Limits results to the production environment.
        AND status = 'failed' -- Limits results to only failed payments.
        AND finalized_at >= CURRENT_DATE - INTERVAL '1' month -- Limits the results by only including a finalized_at date of within the last month.
ORDER BY finalized_at -- Orders the results from oldest to most recent finalized payment.
;

SELECT
        pm.payment_method_id,
        pm.type,
        pm.banv_status,
        pm.blocked_reason,
        pm.updated_at,
        pm.policyholder_id,
        ph.policy_ids AS linked_policies,
        pm.account_holder
FROM
        payment_methods AS pm
        LEFT JOIN policyholders AS ph ON ph.policyholder_id = pm.policyholder_id -- Linking the policyholders table to return linked data
WHERE
        pm.environment = 'production' -- Limits results to the production environment.
        AND pm.banv_status = 'blocked' -- Limits results to return payment methods with blocked status.
ORDER BY
        pm.updated_at DESC;  -- Orders the results from most recent to oldest updated payment method.

SELECT
        id AS refund_id,
        cast(created_at AS date) AS created_at,
        json_extract_scalar(created_by, '$.type') AS created_by_type, -- Extracts the type from the created_by object.
        json_extract_scalar(created_by, '$.id') AS created_by_id, -- Extracts the type from the created_by object.
        status AS refund_status, 
        type,
        amount AS refund_amount, -- Returns an amount in cents.
        description,
        json_extract_scalar(linked_entities, '$.policyId') AS policy_id, -- Extracts the policy_id from the linked_entities object.
        --        finalised_at, -- Commented out because it will be a null value if status is pending.
        --        json_extract_scalar(finalised_by, '$.type') AS finalized_by_type, -- Commented out because it will be a null value if status is pending.
        --        json_extract_scalar(finalised_by, '$.id') AS finalized_by_id, -- Commented out because it will be a null value if status is pending.
        --        rejection_reason, -- Commented out because it will be a null value if status is pending.
        --        proof_of_payment_id, -- Commented out because it will be a null value if status is pending.
        action_date,
        payee AS payee_details
FROM
        payout_requests
WHERE
        type = 'policy_refund'
        AND status = 'pending'
        AND environment = 'prodcution' -- Limits results to the production environment.
ORDER BY
        created_at -- Orders the results from oldest to most recent refund request.
        ;

WITH cte_ledger AS ( -- Creating a common table expression to simplify data in the main query.
        SELECT
                policy_id,
                sum(amount) AS balance
        FROM
                policy_ledger
        GROUP BY
                policy_id
)
SELECT
        pol.policy_number,
        pol.policy_id,
        pol.policyholder_id,
        pol.product_module_id,
        json_extract_scalar (pol.module, '$.type') AS module_type,
        cast(pol.start_date AS date) AS policy_start_date,
        cast(pol.end_date AS date) AS policy_end_date,
        pol.status,
        pol.billing_day,
        pol.monthly_premium / 100.0 AS monthly_premium,
        pl.balance / 100.0 AS ledger_balance
FROM
        policies pol
        LEFT JOIN cte_ledger pl ON pl.policy_id = pol.policy_id
WHERE
        pol.environment = 'production' -- Limits results to the production environment.
        AND pl.balance < 0 -- Limits data returned to policies with balances less than zero.
ORDER BY 
        pl.policy_id
;

WITH cte_ledger_balance AS ( -- Creating a common table expression to simplify data in the main query.
        SELECT
                pol.policy_id,
                json_extract_scalar(module, '$.type') AS module_type, -- Returns the module type extracted from the module object.
                pol.currency,
                pol.monthly_premium,
                sum(coalesce(pl.amount, 0)) AS balance,
                CASE WHEN pol.monthly_premium = 0 THEN
                        0
                ELSE
                        (sum(cast(coalesce(pl.amount, 0) AS double)) / cast(coalesce(monthly_premium, 0) AS double))
                END AS arrears_num,
                (sum(coalesce(pl.amount, 0)) / coalesce(monthly_premium, 0))
        FROM
                policies pol
                LEFT JOIN policy_ledger pl ON pl.policy_id = pol.policy_id
        WHERE
                pol.environment = 'production' -- Limits results to the production environment.
                AND pol.status = 'active' -- Limits results to active policies.
        GROUP BY -- Group like cases together.
                pol.policy_id,
                json_extract_scalar (module, '$.type'),
                pol.currency,
                pol.monthly_premium
),
cte_aging AS ( -- Creating a common table expression to simplify data in the main query.
        SELECT
                module_type,
                CASE WHEN arrears_num >= 0 THEN
                        'paidUp'
                ELSE
                        concat(cast(cast(- floor(arrears_num) AS int) AS varchar), '_month')
                END AS arrears_month,
                count(policy_id) AS policy_count,
                currency,
                sum(balance) AS total_amount_cents
        FROM
                cte_ledger_balance AS lb
        GROUP BY
                module_type,
                CASE WHEN arrears_num >= 0 THEN
                        'paidUp'
                ELSE
                        concat(cast(cast(- floor(arrears_num) AS int) AS varchar), '_month')
                END,
                currency
)
SELECT -- Main query.
        * -- Returns everything from the cte_aging expression.
FROM
        cte_aging
ORDER BY -- Orders the data based of the below-mentioned columns.
        module_type,
        length(arrears_month),
        arrears_month;

SELECT
	*
FROM
	claims
WHERE
	environment = 'production' -- Limits results to the production environment
ORDER BY created_at; -- Orders the results from oldest to newest created claim

SELECT
        id AS claim_id,
        cast(created_at AS date) AS created_at,
        json_extract_scalar(created_by, '$.type') AS created_by_type, -- Extracts the type from the created_by object.
        json_extract_scalar(created_by, '$.id') AS created_by_id, -- Extracts the type from the created_by object.
        status AS claim_status, 
        type,
        amount AS claim_amount, -- Returns an amount in cents.
        description,
        json_extract_scalar(linked_entities, '$.policyId') AS policy_id, -- Extracts the policy_id from the linked_entities object.
        --        finalised_at, -- Commented out because it will be a null value if status is pending.
        --        json_extract_scalar(finalised_by, '$.type') AS finalized_by_type, -- Commented out because it will be a null value if status is pending.
        --        json_extract_scalar(finalised_by, '$.id') AS finalized_by_id, -- Commented out because it will be a null value if status is pending.
        --        rejection_reason, -- Commented out because it will be a null value if status is pending.
        --        proof_of_payment_id, -- Commented out because it will be a null value if status is pending.
        action_date,
        payee AS payee_details
FROM
        payout_requests
WHERE
        type = 'claim_payout'
        AND status = 'pending'
        AND environment = 'sandbox' -- Limits results to the production environment.
ORDER BY
        created_at -- Orders the results from oldest to most recent claim request.
        ;

SELECT
	*
FROM
	policies
WHERE
	environment = 'production' -- Limits results to the production environment
	AND status = 'active' -- Limits results to active policies
ORDER BY
	created_at; -- Orders the results from oldest to newest created policy

SELECT
        pol.policy_number,
        pol.policy_id,
        pol.policyholder_id,
        pol.product_module_id,
        pol.package_name,
         cast(pol.created_at AS date) AS created_at,
         cast(pol.start_date AS date) AS policy_start_date,
         cast(pol.end_date AS date) AS policy_end_date,
         cast(pol.status_updated_at AS date) AS status_updated_at,
        status,
        pol.billing_day,
        pol.billing_frequency,
        pol.sum_assured / 100.0 AS sum_assured,
        pol.monthly_premium / 100.0 AS monthly_premium,
        sum(pl.amount)/100.0 as ledger_balance
FROM
        policies pol
        LEFT JOIN policy_ledger pl ON pl.policy_id = pol.policy_id
WHERE
        pol.environment = 'production' -- Limits results to the production environment.
        AND cast(pol.status_updated_at as date) >= CURRENT_DATE - interval '1' day -- Limits the results by only including policies lapsed in the last day.
        AND pol.status IN ('lapsed', 'not_taken_up') -- Limits results to statuses of lapsed and not_taken_up.
GROUP BY 
        pol.policy_number,
        pol.policy_id,
        pol.policyholder_id,
        pol.product_module_id,
    pol.package_name,
         cast(pol.created_at AS date),
         cast(pol.start_date AS date),
         cast(pol.end_date AS date),
         cast(pol.status_updated_at AS date),
        status,
        pol.billing_day,
        pol.billing_frequency,
        pol.sum_assured / 100.0,
        pol.monthly_premium / 100.0
;

WITH cte_ledger AS (
        SELECT
                pol.policy_id,
                pol.policy_number,
                pol.status,
                pol.billing_day,
                pol.monthly_premium,
                sum(pl.amount) AS balance
        FROM
                policy_ledger pl
                LEFT JOIN policies pol ON pol.policy_id = pl.policy_id
        WHERE
                pol.environment = 'production'
                AND pol.status = 'active' -- Include only active policies
                AND pol.monthly_premium != 0 -- Exclude policies with a zero monthly premium
        GROUP BY
                pol.policy_id,
                pol.policy_number,
                pol.status,
                pol.monthly_premium,
                pol.billing_day
)
SELECT
        *
FROM
        cte_ledger
WHERE
date_diff ('day', CURRENT_DATE, date_add ('day', billing_day, date_trunc('month', CURRENT_DATE) - interval '1' day)) = 10 -- Limits results to the set amount of days until the policy should be billed
AND balance / monthly_premium <= -2 -- Limits results to the set amount of premiums in arreas
        ;

SELECT
        sequence,
        persistence_key,
        regexp_replace(persistence_key, '^.+-policy:', '') AS policy_id,
        substr(persistence_key, strpos(persistence_key, ':') + 1, strpos(persistence_key, '-pol') - 5) AS organization_id, -- Includes ""dev_"" in the front of the org_id if entry is in Sandbox.
        from_unixtime (created_at / 1000) AS created_at,
        version,
        data
FROM
        policy_events
WHERE
        persistence_key NOT LIKE '%dev%'
ORDER BY
        sequence;

SELECT
        date_format(date(created_at), 'yyyy-mm') AS created_period,
        json_extract_scalar(module, '$.type') AS module_type,
        status,
        currency,
        round(cast(sum(monthly_premium / 100.0) AS double), 2) AS sum_of_premium,
        round(cast(sum(sum_assured / 100.0) AS double), 2) AS total_sum_assured
FROM
        policies
WHERE
        environment = 'production'
GROUP BY
        date_format(date(created_at), 'yyyy-mm'),
        json_extract_scalar (module, '$.type'),
        status,
        currency
ORDER BY
        date_format(date(created_at), 'yyyy-mm'),
        json_extract_scalar(module, '$.type'),
        status

SELECT
        *
FROM
        policyholders
WHERE
        environment = 'production' -- Limits results to the production environment
        AND element_at(split (replace(replace(policy_ids, '[', ''), ']',''), ','), 1) != '' -- Limits results to policyholders that have a policy linked to them.
        ORDER BY created_at; -- Orders the results from oldest to newest created policyholder.

SELECT
        notification_id,
        cast(created_at AS date) AS created_at,
        json_extract_scalar (created_by, '$.type') AS created_by_type, -- Extracts the type from the created_at object.
        channel,
        notification_type,
        json_extract_scalar (linked_entities, '$.policyId') AS policy_id, -- Extracts the policy_id from the linked_entities object.
        json_extract_scalar (linked_entities, '$.policyholderID') AS policyholder_id, -- Extracts the policyholder_id from the linked_entities object.
        json_extract_scalar (linked_entities, '$.claimId') AS claim_id, -- Extracts the claim_id from the linked_entities object.
        data,
        status,
        failed_at,
        failure_reason
FROM
        notifications
WHERE
        environment = 'production' -- Limits results to the production environment.
ORDER BY
        created_at; -- Orders the results from oldest to most recent notification.

-- Notification query for when a notification have been triggerred but was not successfull
WITH _policyholders AS (
	-- has the policyholder had an unseccessful notification?
	SELECT DISTINCT
		json_extract_scalar (n.linked_entities, '$.policyholderId') AS "policyholder_id",
		cast(n.created_at AS date) AS created_at,
		n.environment,
		n.notification_type,
		n.notification_id,
		n.organization_id
	FROM
		notifications AS n
	WHERE
		date_trunc('month', cast(n.created_at AS date)) = date('period') -- this needs to be adjusted to the period you wish to extract data for,example: '2024-06-01'
		AND cast(n.created_at AS date) < now() - interval '24' hour
		AND n.environment = 'production'
		AND n.status IN ('queued', 'rejected', 'unknown_error')
),
_success_notifications AS (
	-- has the policyholder received a successful notification for the same notification type within a days range
	SELECT DISTINCT
		succ.notification_id,
		json_extract_scalar (succ.linked_entities, '$.policyholderId') AS policyholder_id,
		succ.notification_type,
		cast(succ.created_at AS date) AS created_at
	FROM
		notifications AS succ
		JOIN _policyholders p ON cast(succ.created_at AS date) >= p.created_at - interval '1' day
			AND cast(succ.created_at AS date) <= p.created_at + interval '1' day
			AND succ.organization_id = p.organization_id
			AND succ.environment = p.environment
			AND succ.notification_type = p.notification_type
			AND succ.status NOT IN ('queued', 'rejected', 'unknown_error')
			AND json_extract_scalar (succ.linked_entities, '$.policyholderId') = p.policyholder_id
),
_main_notifications AS (
	SELECT DISTINCT
		p.policyholder_id,
		p.notification_id,
		p.notification_type
	FROM
		_policyholders p
	WHERE
		NOT EXISTS (
			SELECT
				1
			FROM
				_success_notifications s
			WHERE
				s.policyholder_id = p.policyholder_id
				AND s.notification_type = p.notification_type
				AND s.created_at = p.created_at))
SELECT
	cast(date_trunc('month', n.created_at) AS date) AS period,
	o.name,
	json_extract_scalar (n.linked_entities, '$.policyholderId') AS "policyholder_id",
	json_extract_scalar (n.linked_entities, '$.policyId') AS "policy_id",
	n.created_at,
	n.notification_id,
	n.notification_type,
	n.channel,
	n.status,
	json_extract_scalar (json_extract (n.status_updates, concat('$[', cast(json_array_length(json_parse (n.status_updates)) - 1 AS varchar), ']')), '$.description') AS "last_update",
	n.updated_at,
	external_reference
FROM
	notifications AS n
	LEFT JOIN organizations o ON o.organization_id = n.organization_id
WHERE
	cast(date_trunc('month', n.created_at) AS date) = date('period') -- this needs to be adjusted to the period you wish to extract data for,example: '2024-06-01'
	AND n.environment = 'production'
	AND n.notification_id IN (
		SELECT
			notification_id
		FROM
			_main_notifications
		ORDER BY
			policyholder_id ASC)
	AND n.status IN ('unknown_error', 'rejected', 'queued')
ORDER BY
	cast(date_trunc('month', n.created_at) AS date),
	n.notification_type,
	n.channel,
	n.status;

SELECT
        u.id AS user_id,
        u.email,
        u.state,
        u.created_at,
        our.organization_role_id,
        orgr.name,
        orgr.description
FROM
        users u
        LEFT JOIN organization_user_roles our ON our.user_id = u.id
        LEFT JOIN organization_roles orgr ON orgr.organization_role_id = our.organization_role_id;

SELECT
        product_module_definition_id,
        product_module_id,
        version_major,
        version_minor,
        created_at,
        json_extract_scalar(created_by, '$.type') AS created_by_type, -- Extracts the type from the created_by object.
        json_extract_scalar(created_by, '$.id') AS created_by_id, -- Extracts the type from the created_by object.
        json_extract_scalar(published_by, '$.type') AS created_by_type, -- Extracts the type from the created_by object.
        json_extract_scalar(published_by, '$.id') AS created_by_id, -- Extracts the type from the created_by object.
        json_extract_scalar(json_extract(json_extract(settings, '$.billing'), '$.retries'), '$.retryFailedPayments') AS retryFailedPayments,
        json_extract_scalar(json_extract(json_extract(settings, '$.billing'), '$.retries'), '$.daysBetweenFailedPaymentRetries') AS daysBetweenFailedPaymentRetries,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.currency') AS billing_currency,        
        json_extract_scalar(json_extract(settings, '$.billing'), '$.premiumType') AS billing_frequency,
        json_extract(json_extract(settings, '$.billing'), '$.paymentMethodTypes') AS paymentMethodTypes,
        json_extract_scalar(json_extract(json_extract(settings, '$.billing'), '$.primaryMethod'), '$.type') AS primaryMethodType,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.proRataMinimum') AS proRataMinimum,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.billBeforeWeekend') AS billBeforeWeekend,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.allowStartDateChange') AS allowStartDateChange,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.naedoArrearsPolicies') AS naedoArrearsPolicies,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.proRataBillingOnIssue') AS proRataBillingOnIssue,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.enableBillingOnSandbox') AS enableBillingOnSandbox,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.proRataBillingDisabled') AS proRataBillingDisabled,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.paymentSubmissionLeadTime') AS paymentSubmissionLeadTime,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.shouldCreateExternalPayments') AS shouldCreateExternalPayments,
        json_extract_scalar(json_extract(settings, '$.billing'), '$.consecutiveFailedPaymentsAllowed') AS consecutiveFailedPaymentsAllowed
FROM
        product_module_definitions
-- WHERE product_module_id = 'enter product_module_id here' -- Can be included to only extract the billing settings from a single product module.
ORDER BY
        product_module_id, 
        version_major,
        version_minor
;

SELECT
	pmc.payment_method_config_id,
	pmc.payment_method_type,
	pmc.provider_id,
	pmp.name AS provider_name,
	pmp.description AS provider_description,
	pmp.provider_type,
	pmc.payment_method_config_key,
	pmc.is_default,
	pmp.is_default_for_new_orgs,
	cast(pmc.created_at AS date) AS created_at,
	cast(pmc.updated_at AS date) AS updated_at,
	pmc.product_module_id,
	pmc.billing_strategy_settings,
	pmc.config AS payment_method_configuration,
	pmp.config AS provider_configuration
FROM
	payment_method_configs AS pmc
	LEFT JOIN payment_method_providers AS pmp ON pmp.payment_method_provider_id = pmc.provider_id -- Joining an additional table to include more relevant data.
WHERE
	pmc.environment = 'production' -- Limits results to the production environment.
ORDER BY
	pmp.provider_type -- Orders the results from alphabetically using the provider type.
;

SELECT
        pol.policy_number,
        pol.status AS policy_status,
        pay.action_date AS payment_date,
        pay.finalized_at AS finalized_date,
        pay.status AS payment_status,
        cast(pol.start_date AS date) AS policy_start_date,
        date_diff('year', pol.start_date, CURRENT_DATE) AS policy_age,
        json_extract_scalar(pol.module, '$.type') AS module_type,
        date_diff('year', ph.date_of_birth, CURRENT_DATE) AS policyholder_age,
        pay.description AS payment_description,
        pol.monthly_premium / 100.0 AS monthly_premium, -- Returns an amount that has been converted from cents.
        pay.amount / 100.0 AS amount_collected -- Returns an amount that has been converted from cents.
FROM
        payments pay
        LEFT JOIN policies pol ON pay.policy_id = pol.policy_id
        LEFT JOIN policyholders ph ON pol.policyholder_id = ph.policyholder_id
WHERE
        pay.environment = 'production' -- Limits results to the production environment.
        AND pay.status = 'successful' -- Limits results to only successful payments.
        AND pay.finalized_at >= CURRENT_DATE - INTERVAL '1' month -- Limits the results by only including a finalized_at date of within the last month.
        ;

SELECT
	c.claim_number,
	c.claim_id,
	cast(c.created_at AS date) AS claim_created_at,
	cast(c.updated_at AS date) AS claim_updated_at,
	c.status AS claim_status,
	c.approval_status,
	c.policyholder_id,
	c.policy_id,
	json_extract_scalar (pol.module, '$.type') AS module_type,
	pol.policy_number,
	pol.status AS policy_status,
	cast(pol.start_date AS date) AS policy_start_date,
	date_diff ('year', pol.start_date, c.created_at) AS policy_age_at_claim_creation,
	date_diff ('year', ph.date_of_birth, c.created_at) AS policyholder_age_at_claim_creation,
	max(cast(pay.payment_date AS timestamp)) FILTER (WHERE pay.status = 'successful') AS last_successful_payment,
	c.claimant AS claimant_details,
	c.incident_date,
	c.incident_type,
	c.incident_cause,
	c.requested_amount / 100.0 AS requested_amount,
	c.granted_amount / 100.0 AS granted_amount,
	c.rejection_reason
FROM
	claims c
	LEFT JOIN policies pol ON c.policy_id = pol.policy_id
	LEFT JOIN policyholders ph ON c.policyholder_id = ph.policyholder_id
	LEFT JOIN payments pay ON pay.policy_id = c.policy_id
WHERE
	c.environment = 'production' -- Limits results to the production environment.
GROUP BY
	c.claim_number,
	c.claim_id,
	cast(c.created_at AS date),
	cast(c.updated_at AS date),
	c.status,
	c.approval_status,
	c.policyholder_id,
	c.policy_id,
	json_extract_scalar (pol.module, '$.type'),
	pol.policy_number,
	pol.status,
	cast(pol.start_date AS date),
	date_diff ('year', pol.start_date, c.created_at),
	date_diff ('year', ph.date_of_birth, c.created_at),
	c.claimant,
	c.incident_date,
	c.incident_type,
	c.incident_cause,
	c.requested_amount,
	c.granted_amount,
	c.rejection_reason;