tripz-llc/apps/driver/lib/models/ai_query.sql

-- Retrieving data for a specific passenger complaint:
SELECT
    c.id AS complaint_id,
    r.id AS ride_id,
    p.id AS passenger_id,
    p.first_name,
    p.last_name,
    p.phone,
    p.email,
    c.complaint_type,
    c.description,
    c.date_filed,
    c.statusComplaint AS complaint_status,
    c.resolution,
    c.date_resolved
FROM
    complaint c
JOIN ride r ON
    c.ride_id = r.id
JOIN `passengers` p ON
    c.passenger_id = p.id
WHERE
    c.complaint_type = 'Passenger' AND c.passenger_id = '100393163265770158312';

    -- Admin panel dashboard
SELECT
    COALESCE((
        SELECT
            COUNT(`email`)
        FROM
            `passengers`
    ), 0) AS countPassengers,
    COALESCE((
        SELECT
            COUNT(`email`)
        FROM
            `driver`
    ), 0) AS countDriver,
    COALESCE((
        SELECT
            COUNT(`id`)
        FROM
            `ride`
    ), 0) AS countRide,
    COALESCE((
        SELECT
            COUNT(`id`)
        FROM
            `passengers`
        WHERE
            `passengers`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
    ), 0) AS countPassengersThisMonth,
    COALESCE((
        SELECT
            COUNT(`id`)
        FROM
            `ride`
        WHERE
            `ride`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
    ), 0) AS countRideThisMonth,
    COALESCE((
        SELECT
            COUNT(`id`)
        FROM
            `driver`
        WHERE
            `driver`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
    ), 0) AS countDriverThisMonth,
    COALESCE((
        SELECT
            COUNT(`id`)
        FROM
            `CarRegistration`
        WHERE
            `CarRegistration`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
    ), 0) AS countCarRegistrationThisMonth,
    COALESCE((
        SELECT
            COUNT(`id`)
        FROM
            `complaint`
        WHERE
            `complaint`.`date_filed` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
    ), 0) AS countComplaintThisMonth,
    COALESCE((
        SELECT
            COUNT(`id`)
        FROM
            `complaint`
        WHERE
            `complaint`.`date_filed` BETWEEN DATE_FORMAT(
                DATE_SUB(
                    CURRENT_DATE,
                    INTERVAL WEEKDAY(CURRENT_DATE) DAY
                ),
                '%Y-%m-%d'
            ) AND DATE_FORMAT(
                DATE_ADD(
                    DATE_SUB(
                        CURRENT_DATE,
                        INTERVAL WEEKDAY(CURRENT_DATE) DAY
                    ),
                    INTERVAL 6 DAY
                ),
                '%Y-%m-%d'
            )
    ), 0) AS countComplaintThisWeek,
    COALESCE((
        SELECT
            COUNT(`id`)
        FROM
            `complaint`
        WHERE
            `complaint`.`date_filed` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d') AND DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d')
    ), 0) AS countComplaintToday,
    COALESCE((
        SELECT
            SUM(`payments`.`amount`)
        FROM
            `payments`
        WHERE
            `payments`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
        AND
            `payments`.`payment_method` IN('visa-in', 'visa', 'visaRide', 'TransferFrom', 'payout', 'TransferTo')
    ), 0) AS payments,
    COALESCE((
        SELECT
            SUM(`driverWallet`.`amount`)
        FROM
            `driverWallet`
        WHERE
            `driverWallet`.`dateCreated` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
        AND
            `driverWallet`.`paymentMethod` IN('visa', '')
    ), 0) AS driverWallet,
    COALESCE((
        SELECT
            SUM(`passengerWallet`.`balance`)
        FROM
            `passengerWallet`
        WHERE
            `passengerWallet`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
    ), 0) AS passengerWallet,
    COALESCE((
        SELECT
            SUM(`seferWallet`.`amount`)
        FROM
            `seferWallet`
        WHERE
            `seferWallet`.`createdAt` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
    ), 0) AS seferWallet
FROM
    `passengers`
LIMIT 1;