-- =====================================================================
-- Read-only reporting access: schema `kapitano_reports`
--
-- Management's reports (daily sales, weekly/monthly customers, sellers,
-- collection, marketing, delivery, customer service) are built by their own
-- assistant straight from the data. This file gives that assistant a
-- read-only window on the live database WITHOUT personal data:
--
--   * a separate schema `kapitano_reports` holding only VIEWS over the real
--     tables; every view keeps the real table's name (kapitano_reports.orders,
--     kapitano_reports.order_details, ...) and only the columns a report needs.
--     Names, phones, emails, addresses, passwords, tokens, bank data, IPs and
--     free-text notes are never selected. Governorate/city of an order exist
--     only inside the shipping_address JSON (next to the name and phone), which
--     is why these are views and not column grants.
--   * a locked account `kapitano_reports_definer`@`localhost` that owns the
--     views (SQL SECURITY DEFINER). It cannot log in and holds SELECT on the
--     listed tables only - so the views can read, never write.
--   * the reporting login itself is created BY HAND (step 2): its password is
--     never written in a file.
--
-- Amounts: orders.grand_total, order_details.price/tax, combined_orders.* are
-- BASE (USD). Each order view carries `syp_rate`, the BASE -> SYP rate in force
-- on that order's date (a SQL copy of exchange_rate_at() in app/Http/Helpers.php),
-- and *_syp columns. Dates: use `order_day` / `order_at_syria` (from the epoch
-- orders.date, Syria time = UTC+3). See docs/codebase/06-admin-reports-marketing.md
-- "Reporting database user" for the meaning of every view and what is NOT stored.
--
-- ---------------------------------------------------------------------
-- RUNBOOK (server team)
-- ---------------------------------------------------------------------
-- 0. Preconditions
--    - `SELECT VERSION();` -> MySQL 8.0+ (or MariaDB 10.4+).
--    - Run as root, or an account with CREATE USER, GRANT OPTION and
--      SET_USER_ID (or SUPER): the views are created with another account as
--      DEFINER. A cPanel/shared-hosting database user cannot do this.
--    - MySQL must listen on TCP 127.0.0.1:3306 (not socket-only), or the SSH
--      tunnel has nothing to reach.
--
-- 1. Run this file WITH THE PRODUCTION DATABASE AS THE DEFAULT DATABASE:
--        mysql -u root -p <production_db_name> < kapitano_reports_readonly_access_2026-10-04.sql
--    Every table below is written unqualified on purpose: MySQL resolves it
--    against that default database when the view is created, so nothing in
--    this file needs the database's name. It only CREATES new objects (one
--    schema, one locked account, views, grants); no existing table or row is
--    touched. Re-running it is safe (it replaces the views with the same ones).
--    The mysql client stops at the first error, e.g. if no database was given.
--
--    Sections C to F at the end need features deployed after 2026-09-28 (order
--    ratings, traffic sources, account deactivation; D: seller pricing; E: seller
--    entry type; F: seller price currency). If not on the
--    server yet, the run stops there with "Table ... doesn't exist": sections
--    A and B are already done and nothing is harmed. Re-run after deploying.
--
-- 2. Create the reporting login by hand (generate the password on the server,
--    e.g. `openssl rand -base64 24`, never send it by SMS or e-mail - hand it
--    over in person):
--        CREATE USER 'kapitano_reports'@'127.0.0.1'
--            IDENTIFIED BY '<generated password>'
--            WITH MAX_USER_CONNECTIONS 2;
--        GRANT SELECT, SHOW VIEW ON kapitano_reports.* TO 'kapitano_reports'@'127.0.0.1';
--    '127.0.0.1', not 'localhost': a tunnel arrives over TCP, and MySQL only
--    matches 'localhost' for socket connections.
--
-- 3. SSH side: a Linux user `reports` with shell /usr/sbin/nologin whose
--    ~/.ssh/authorized_keys holds ONLY management's dedicated public key, as:
--        restrict,port-forwarding,permitopen="127.0.0.1:3306" ssh-ed25519 AAAA... kapitano-reports
--    That key can open the MySQL tunnel and nothing else (no shell, no other
--    port). Client side:
--        ssh -N -L 3307:127.0.0.1:3306 reports@<server>
--    then connect to 127.0.0.1:3307 as kapitano_reports, default database
--    `kapitano_reports` (so `SELECT ... FROM orders` reads the view), and
--    `SET SESSION max_execution_time = 60000;` (MariaDB: max_statement_time = 60).
--    Heavy queries only in the
--    06:00 window.
--
-- 4. Changing anything a view reads (renaming/dropping a column, changing the
--    exchange-rate rule) breaks the reports: update this file, re-run it, bump
--    the version in the `about` view, and tell management BEFORE deploying.
--
-- 5. Rollback (removes everything this file and step 2-3 created):
--        DROP USER IF EXISTS 'kapitano_reports'@'127.0.0.1';
--        DROP USER IF EXISTS 'kapitano_reports_definer'@'localhost';
--        DROP DATABASE IF EXISTS kapitano_reports;
--    and delete the `reports` Linux user.
-- =====================================================================

SET NAMES utf8mb4;

-- Stops the run unless the default database is the real one: `migrations`
-- exists there and nowhere in kapitano_reports.
DO (SELECT COUNT(*) FROM migrations WHERE 1 = 0);


-- =====================================================================
-- A. Schema and the locked account that owns the views
-- =====================================================================

CREATE DATABASE IF NOT EXISTS kapitano_reports
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS 'kapitano_reports_definer'@'localhost' ACCOUNT LOCK;
ALTER USER 'kapitano_reports_definer'@'localhost' ACCOUNT LOCK;

-- SELECT only, table by table (unqualified = the default database). Nothing
-- else in the database is readable through the views.
GRANT SELECT ON orders                     TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON combined_orders            TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON order_details              TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON deleted_orders             TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON products                   TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON product_stocks             TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON product_categories         TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON categories                 TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON brands                     TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON shops                      TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON sellers                    TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON users                      TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON commission_histories       TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON paymera_app_checkouts      TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON payments                   TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON seller_withdraw_requests   TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON wallets                    TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON order_follow_ups           TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON order_follow_up_logs       TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON delivery_agents            TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON cities                     TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON states                     TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON countries                  TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON carts                      TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON cart_retargets             TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON search_queries             TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON search_events              TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON coupons                    TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON coupon_usages              TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON reviews                    TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON tickets                    TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON seller_order_notifications TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON stock_zeroings             TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON whatsapp_campaigns         TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON custom_notification_batches TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON exchange_rate_changes      TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON currencies                 TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON business_settings          TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON custom_home_sections        TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON custom_home_section_products TO 'kapitano_reports_definer'@'localhost';
-- Views built on other views of this schema (rates, order_rates).
GRANT SELECT ON kapitano_reports.*         TO 'kapitano_reports_definer'@'localhost';


-- =====================================================================
-- B. Views
-- =====================================================================

-- ---------------------------------------------------------------------
-- about: bumped whenever a view changes, so the reports can notice.
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.about AS
SELECT '2026-10-04' AS version,
       'Amounts are BASE (USD) unless the column ends in _syp. Use order_day for order dates.' AS readme;

-- ---------------------------------------------------------------------
-- rates: today's BASE -> SYP factors (currency_syp_factor() in Helpers.php).
--   syp_rate_now       SYP exchange_rate / default currency exchange_rate
--                      (1 when either is missing or not positive)
--   syp_market_rate_now  the same with real_rate (market products); falls
--                      back to syp_rate_now while real_rate is 0
--   market_multiplier  RAW price of a `market` product -> BASE
--                      (raw_to_base_multiplier('market'))
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.rates AS
SELECT r.now_rate    AS syp_rate_now,
       r.market_rate AS syp_market_rate_now,
       IF(r.now_rate > 0 AND r.market_rate > 0, r.market_rate / r.now_rate, 1) AS market_multiplier
FROM (
    SELECT
        IF(s.exchange_rate > 0 AND d.exchange_rate > 0, s.exchange_rate / d.exchange_rate, 1) AS now_rate,
        CASE
            WHEN s.real_rate > 0 AND IF(d.real_rate > 0, d.real_rate, d.exchange_rate) > 0
                THEN s.real_rate / IF(d.real_rate > 0, d.real_rate, d.exchange_rate)
            ELSE IF(s.exchange_rate > 0 AND d.exchange_rate > 0, s.exchange_rate / d.exchange_rate, 1)
        END AS market_rate
    FROM (SELECT 1 AS one) AS one_row
    LEFT JOIN currencies AS d
        ON d.id = (SELECT CAST(bs.value AS UNSIGNED) FROM business_settings AS bs
                   WHERE bs.type = 'system_default_currency' ORDER BY bs.id LIMIT 1)
    LEFT JOIN currencies AS s
        ON s.id = (SELECT c.id FROM currencies AS c WHERE c.code = 'SYP' ORDER BY c.id LIMIT 1)
) AS r;

-- ---------------------------------------------------------------------
-- exchange_rate_changes: the rate log. A row with order_rate_old closes a
-- period: orders dated before `at` keep order_rate_old for ever.
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.exchange_rate_changes AS
SELECT e.id, e.at,
       ('1970-01-01 00:00:00' + INTERVAL e.at SECOND) + INTERVAL 3 HOUR AS at_syria,
       e.event, e.currency_id, e.currency_code,
       e.order_rate_old, e.order_rate_new, e.market_rate_old, e.market_rate_new,
       e.source
FROM exchange_rate_changes AS e;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.currencies AS
SELECT c.id, c.code, c.name, c.exchange_rate, c.real_rate, c.status
FROM currencies AS c;

-- Whitelist of business_settings rows (other rows hold API keys).
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.settings AS
SELECT bs.type, bs.value
FROM business_settings AS bs
WHERE bs.type IN (
    'system_default_currency',
    'vendor_commission_activation',
    'seller_commission_type',
    'vendor_commission',
    'category_wise_commission',
    'stock_on_confirm_since'
);

-- ---------------------------------------------------------------------
-- order_rates: BASE -> SYP rate of every order on its own date.
-- Pure-SQL copy of exchange_rate_at(order_timestamp($order)): the first rate
-- period whose end (`at`) is after the order's epoch, else today's rate.
-- An order without `date` falls back to created_at read as Syria time.
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.order_rates AS
SELECT o.id AS order_id,
       COALESCE(
           (SELECT e.order_rate_old
              FROM exchange_rate_changes AS e
             WHERE e.order_rate_old > 0
               AND e.at > COALESCE(NULLIF(o.date, 0), TIMESTAMPDIFF(SECOND, '1970-01-01 03:00:00', o.created_at))
             ORDER BY e.at
             LIMIT 1),
           rt.syp_rate_now
       ) AS syp_rate
FROM orders AS o
CROSS JOIN kapitano_reports.rates AS rt;

-- ---------------------------------------------------------------------
-- orders: one row per seller per checkout. Void = delivery_status
-- 'cancelled' or 'returned'. grand_total excludes the delivery fee (that is
-- on combined_orders.shipping_fee, once per checkout).
-- Since 2026-10-20 the checkout's coupon lives on combined_orders only:
-- grand_total is the seller order's products + tax WITHOUT the coupon, and
-- coupon_discount is 0 (rows from before were moved to that shape). What the
-- customer paid is combined_orders.payable_total, once per checkout; a seller's
-- sales are the sum of its orders' grand_total.
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.orders AS
SELECT o.id,
       o.combined_order_id,
       o.user_id,
       COALESCE(o.guest_id, 0) > 0                              AS is_guest,
       o.seller_id,
       o.code,
       o.order_from,
       o.shipping_type,
       o.delivery_status,
       COALESCE(o.delivery_status, '') IN ('cancelled', 'returned') AS is_void,
       o.payment_type,
       o.payment_status,
       o.payment_settled_by IS NOT NULL                         AS settled_by_staff,
       o.payment_settled_at,
       o.date,
       ('1970-01-01 00:00:00' + INTERVAL o.date SECOND) + INTERVAL 3 HOUR       AS order_at_syria,
       DATE(('1970-01-01 00:00:00' + INTERVAL o.date SECOND) + INTERVAL 3 HOUR) AS order_day,
       NULLIF(NULLIF(IF(JSON_VALID(o.shipping_address),
           JSON_UNQUOTE(JSON_EXTRACT(o.shipping_address, '$.state')), NULL), 'null'), '') AS ship_state,
       NULLIF(NULLIF(IF(JSON_VALID(o.shipping_address),
           JSON_UNQUOTE(JSON_EXTRACT(o.shipping_address, '$.city')), NULL), 'null'), '')  AS ship_city,
       o.grand_total,
       o.coupon_discount,
       o.coupon_id,
       o.coupon_code,
       r.syp_rate,
       o.grand_total * r.syp_rate                               AS grand_total_syp,
       o.coupon_discount * r.syp_rate                           AS coupon_discount_syp,
       o.seller_confirmed_at,
       o.seller_unavailable_at,
       o.delivered_date,
       o.cancel_reason,
       o.cancelled_by_type,
       o.cancelled_at
FROM orders AS o
JOIN kapitano_reports.order_rates AS r ON r.order_id = o.id;

-- ---------------------------------------------------------------------
-- order_details: one row per product line. price/tax are BASE and already
-- the price paid (product discounts included). delivery_status 'returned' on
-- a line = that line came back (partial return).
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.order_details AS
SELECT od.id, od.order_id, od.seller_id, od.product_id, od.variation,
       od.price, od.tax, od.shipping_cost, od.quantity,
       od.payment_status, od.delivery_status, od.shipping_type, od.pickup_point_id,
       od.earn_point, od.seller_unavailable,
       r.syp_rate,
       od.price * r.syp_rate AS price_syp,
       od.tax * r.syp_rate   AS tax_syp,
       od.created_at, od.updated_at
FROM order_details AS od
LEFT JOIN kapitano_reports.order_rates AS r ON r.order_id = od.order_id;

-- ---------------------------------------------------------------------
-- combined_orders: one row per checkout (what the customer paid once).
--   order_date      oldest child order's `date` (order_timestamp())
--   shipping_fee    the delivery fee: shipping_cost, or for older checkouts
--                   the shipping kept on the live orders' lines
--   payable_total   what the customer owes = combined_order_payable_total():
--                   live goods - the coupon (floored at 0) + shipping_fee; 0
--                   when every child order is void; grand_total when it has no
--                   orders. The coupon is coupon_discount (it lives here since
--                   2026-10-20), at most the live goods - or, for rows still in
--                   the old shape, the shares their live orders carry
--   payment_id      Paymera reference of the LAST payment attempt only
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.combined_orders AS
SELECT x.id, x.user_id, x.orders_count, x.live_orders_count,
       x.orders_count > 0 AND x.live_orders_count = 0 AS is_void,
       x.order_date,
       ('1970-01-01 00:00:00' + INTERVAL x.order_date SECOND) + INTERVAL 3 HOUR       AS order_at_syria,
       DATE(('1970-01-01 00:00:00' + INTERVAL x.order_date SECOND) + INTERVAL 3 HOUR) AS order_day,
       x.ship_state, x.ship_city,
       x.grand_total, x.shipping_cost, x.shipping_fee, x.payable_total,
       x.coupon_discount, x.coupon_id, x.coupon_code, x.payment_id,
       x.syp_rate,
       x.grand_total * x.syp_rate     AS grand_total_syp,
       x.shipping_fee * x.syp_rate    AS shipping_fee_syp,
       x.payable_total * x.syp_rate   AS payable_total_syp,
       x.coupon_discount * x.syp_rate AS coupon_discount_syp
FROM (
    SELECT y.*,
           COALESCE(
               (SELECT e.order_rate_old
                  FROM exchange_rate_changes AS e
                 WHERE e.order_rate_old > 0 AND e.at > y.rate_ts
                 ORDER BY e.at
                 LIMIT 1),
               rt.syp_rate_now
           ) AS syp_rate
    FROM (
        SELECT c.id, c.user_id,
               COALESCE(agg.orders_count, 0)      AS orders_count,
               COALESCE(agg.live_orders_count, 0) AS live_orders_count,
               NULLIF(agg.first_date, 0)          AS order_date,
               COALESCE(NULLIF(agg.first_date, 0),
                        TIMESTAMPDIFF(SECOND, '1970-01-01 03:00:00', c.created_at)) AS rate_ts,
               NULLIF(NULLIF(IF(JSON_VALID(c.shipping_address),
                   JSON_UNQUOTE(JSON_EXTRACT(c.shipping_address, '$.state')), NULL), 'null'), '') AS ship_state,
               NULLIF(NULLIF(IF(JSON_VALID(c.shipping_address),
                   JSON_UNQUOTE(JSON_EXTRACT(c.shipping_address, '$.city')), NULL), 'null'), '')  AS ship_city,
               c.grand_total,
               c.shipping_cost,
               IF(c.shipping_cost > 0, c.shipping_cost, COALESCE(agg.live_detail_shipping, 0)) AS shipping_fee,
               CASE
                   WHEN COALESCE(agg.orders_count, 0) = 0 THEN c.grand_total
                   WHEN agg.live_orders_count = 0 THEN 0
                   ELSE ROUND(GREATEST(0, agg.live_goods
                                  - IF(agg.share_rows > 0, agg.live_shares, LEAST(c.coupon_discount, agg.live_goods)))
                              + IF(c.shipping_cost > 0, c.shipping_cost, agg.live_detail_shipping), 2)
               END AS payable_total,
               c.coupon_discount, c.coupon_id, c.coupon_code, c.payment_id
        FROM combined_orders AS c
        LEFT JOIN (
            SELECT o.combined_order_id,
                   COUNT(*)  AS orders_count,
                   MIN(o.date) AS first_date,
                   SUM(COALESCE(o.delivery_status, '') NOT IN ('cancelled', 'returned')) AS live_orders_count,
                   SUM(IF(COALESCE(o.delivery_status, '') NOT IN ('cancelled', 'returned'),
                          COALESCE(ds.shipping, 0), 0)) AS live_detail_shipping,
                   -- products + tax without any coupon (an order of the old shape still carries its share)
                   SUM(IF(COALESCE(o.delivery_status, '') NOT IN ('cancelled', 'returned'),
                          o.grand_total + o.coupon_discount - COALESCE(ds.shipping, 0), 0)) AS live_goods,
                   SUM(IF(COALESCE(o.delivery_status, '') NOT IN ('cancelled', 'returned'), o.coupon_discount, 0)) AS live_shares,
                   SUM(o.coupon_discount <> 0) AS share_rows
            FROM orders AS o
            LEFT JOIN (
                SELECT d.order_id, SUM(d.shipping_cost) AS shipping
                FROM order_details AS d
                GROUP BY d.order_id
            ) AS ds ON ds.order_id = o.id
            WHERE o.combined_order_id IS NOT NULL
            GROUP BY o.combined_order_id
        ) AS agg ON agg.combined_order_id = c.id
    ) AS y
    CROSS JOIN kapitano_reports.rates AS rt
) AS x;

-- Orders deleted by staff (archive). grand_total is BASE.
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.deleted_orders AS
SELECT d.id, d.order_id, d.order_code, d.combined_order_id, d.user_id, d.seller_id,
       d.grand_total, d.payment_type, d.payment_status, d.delivery_status,
       d.order_details_count, d.order_created_at, d.deleted_by_type, d.source, d.created_at
FROM deleted_orders AS d;

-- ---------------------------------------------------------------------
-- Catalogue. unit_price / real_price / purchase_price are RAW (in the
-- product's own rate, products.exchange_type); base_* are BASE, comparable
-- with order amounts. purchase_price is the cost price as it is TODAY
-- (orders do not keep a copy).
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.products AS
SELECT p.id, p.name, p.added_by, p.user_id, p.category_id, p.brand_id,
       p.exchange_type,
       p.unit_price, p.real_price, p.purchase_price,
       p.unit_price * IF(p.exchange_type = 'market', rt.market_multiplier, 1)     AS base_unit_price,
       p.purchase_price * IF(p.exchange_type = 'market', rt.market_multiplier, 1) AS base_purchase_price,
       p.discount, p.discount_type, p.discount_start_date, p.discount_end_date,
       p.published, p.approved, p.current_stock, p.num_of_sale, p.rating,
       -- Today's Deal / Trending: home sections since 2026-10-25 (custom_home_sections.legacy_key), 0/1 as before
       EXISTS (SELECT 1 FROM custom_home_section_products AS hp JOIN custom_home_sections AS hs ON hs.id = hp.custom_home_section_id WHERE hp.product_id = p.id AND hs.legacy_key = 'featured')    AS featured,
       EXISTS (SELECT 1 FROM custom_home_section_products AS hp JOIN custom_home_sections AS hs ON hs.id = hp.custom_home_section_id WHERE hp.product_id = p.id AND hs.legacy_key = 'todays_deal') AS todays_deal,
       p.wholesale_product, p.digital, p.cash_on_delivery,
       p.created_at, p.updated_at
FROM products AS p
CROSS JOIN kapitano_reports.rates AS rt;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.product_stocks AS
SELECT s.id, s.product_id, s.variant, s.sku, s.price, s.real_price, s.qty, s.created_at, s.updated_at
FROM product_stocks AS s;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.product_categories AS
SELECT pc.product_id, pc.category_id
FROM product_categories AS pc;

-- commision_rate: spelled that way in the real table.
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.categories AS
SELECT c.id, c.parent_id, c.level, c.name, c.commision_rate, c.is_hidden, c.featured,
       c.digital, c.created_at
FROM categories AS c;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.brands AS
SELECT b.id, b.name, b.created_at
FROM brands AS b;

-- ---------------------------------------------------------------------
-- Sellers. orders.seller_id is a users.id: join shops.user_id = orders.seller_id.
-- admin_to_pay = running balance owed to the seller (BASE).
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.shops AS
SELECT s.id, s.user_id, s.name, s.rating, s.num_of_reviews, s.num_of_sale,
       s.verification_status, s.cash_on_delivery_status, s.admin_to_pay,
       s.commission_percentage, s.shipping_cost, s.created_at
FROM shops AS s;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.sellers AS
SELECT s.id, s.user_id, s.rating, s.num_of_reviews, s.num_of_sale,
       s.verification_status, s.cash_on_delivery_status, s.admin_to_pay, s.created_at
FROM sellers AS s;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.commission_histories AS
SELECT h.id, h.order_id, h.order_detail_id, h.seller_id, h.admin_commission, h.seller_earning,
       h.created_at
FROM commission_histories AS h;

-- ---------------------------------------------------------------------
-- users: id, type, registration date and location only. created_at is the
-- registration date (stored ~8 h ahead before 2026-09-02, see the docs).
-- Re-created with deactivated_at in section C.
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.users AS
SELECT u.id, u.user_type, u.created_at,
       u.country_id, u.state_id, u.city_id, u.state, u.city,
       u.banned
FROM users AS u;

-- ---------------------------------------------------------------------
-- Payments.
--   paymera_app_checkouts  every Paymera attempt from the app (amount = SYP
--                          sent to Paymera, grand_total = BASE). Web attempts
--                          are not logged: only combined_orders.payment_id
--                          (last attempt) and orders.payment_status.
--   payments               payouts to sellers
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.paymera_app_checkouts AS
SELECT p.id, p.user_id, p.combined_order_id, p.payment_id, p.amount, p.grand_total,
       p.status, p.completed_at, p.created_at, p.updated_at
FROM paymera_app_checkouts AS p;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.payments AS
SELECT p.id, p.seller_id, p.amount, p.payment_method, p.created_at
FROM payments AS p;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.seller_withdraw_requests AS
SELECT w.id, w.user_id, w.amount, w.status, w.created_at
FROM seller_withdraw_requests AS w;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.wallets AS
SELECT w.id, w.user_id, w.amount, w.payment_method, w.created_at
FROM wallets AS w;

-- ---------------------------------------------------------------------
-- Delivery. Who delivered an order: order_follow_ups.delivery_employee_id ->
-- delivery_agents.id (delivery_agent_id is the older column). The cash
-- amount collected per driver is NOT stored; orders.payment_settled_at says
-- when an order was marked paid.
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.order_follow_ups AS
SELECT f.id, f.order_id, f.status,
       f.delivery_employee_id, f.delivery_agent_id, f.accountable_employee_id, f.accountant_user_id,
       f.assigned_at, f.handed_over_at, f.decided_at, f.delivery_assigned_at, f.accountable_assigned_at,
       f.created_at, f.updated_at
FROM order_follow_ups AS f;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.order_follow_up_logs AS
SELECT l.id, l.order_follow_up_id, l.order_id, l.action, l.user_id, l.created_at
FROM order_follow_up_logs AS l;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.delivery_agents AS
SELECT a.id, a.name, a.is_active, a.created_at
FROM delivery_agents AS a;

-- Locations. cities.cost = delivery fee of the area (BASE),
-- carttotal = free-shipping threshold (BASE).
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.cities AS
SELECT c.id, c.name, c.state_id, c.cost, c.carttotal, c.status
FROM cities AS c;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.states AS
SELECT s.id, s.name, s.en_name, s.country_id, s.status
FROM states AS s;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.countries AS
SELECT c.id, c.code, c.name, c.status
FROM countries AS c;

-- ---------------------------------------------------------------------
-- Carts. guest_key = a hash of the guest's cart id (the raw id works as the
-- guest's credential in the app, so it is never exposed).
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.carts AS
SELECT c.id, c.user_id,
       IF(c.temp_user_id IS NULL OR c.temp_user_id = '', NULL, SHA2(c.temp_user_id, 256)) AS guest_key,
       c.product_id, c.quantity, c.price, c.tax, c.discount, c.shipping_cost, c.coupon_code,
       c.status, c.created_at, c.updated_at
FROM carts AS c;

-- Abandoned carts followed up by customer service.
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.cart_retargets AS
SELECT r.id, r.user_id, r.cart_status, r.contact_status, r.not_completed_reason,
       r.order_value, r.cart_value, r.items_count, r.items_qty, r.has_live_cart,
       r.contact_attempts, r.contacted_at, r.follow_up_at,
       r.converted_order_id, r.converted_at,
       r.cart_created_at, r.cart_last_activity_at, r.cart_emptied_at,
       r.created_at, r.updated_at
FROM cart_retargets AS r;

-- ---------------------------------------------------------------------
-- Search. search_queries = aggregated per query; search_events = raw events
-- (search_submitted, search_*_clicked) with no link to the person.
-- No result counts are stored: "searched and found nothing" can only be
-- approximated as a submitted query with no click.
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.search_queries AS
SELECT q.id, q.query, q.locale, q.search_count, q.click_count, q.is_blocked,
       q.last_searched_at, q.created_at
FROM search_queries AS q;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.search_events AS
SELECT e.id, e.event_type, e.query, e.target_id, e.locale, e.platform, e.created_at
FROM search_events AS e;

-- Coupons and their use (discount amounts per order are on orders / combined_orders).
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.coupons AS
SELECT c.id, c.user_id, c.type, c.platform, c.code, c.discount, c.discount_type,
       c.max_users, c.start_date, c.end_date, c.status, c.created_at
FROM coupons AS c;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.coupon_usages AS
SELECT u.id, u.user_id, u.coupon_id, u.order_id, u.created_at
FROM coupon_usages AS u;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.reviews AS
SELECT r.id, r.product_id, r.user_id, r.rating, r.status, r.created_at
FROM reviews AS r;

-- Support tickets (subject and text are left out).
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.tickets AS
SELECT t.id, t.code, t.user_id, t.status, t.created_at, t.updated_at
FROM tickets AS t;

-- How fast sellers answer a new order (WhatsApp confirmation link).
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.seller_order_notifications AS
SELECT n.id, n.order_id, n.seller_id, n.shop_id, n.send_status, n.attempts,
       n.notified_at, n.wa_state, n.wa_delivered_at, n.wa_read_at, n.link_opened_at,
       n.reminder_count, n.last_reminder_at, n.escalated_at,
       n.response, n.response_source, n.responded_at, n.closed_at, n.closed_reason,
       n.response_seconds, n.response_business_seconds,
       n.order_hour, n.in_business_hours, n.payment_type, n.order_from, n.items_count,
       n.grand_total, n.city, n.created_at
FROM seller_order_notifications AS n;

-- Stock set to zero because an order found the product unavailable.
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.stock_zeroings AS
SELECT z.id, z.product_id, z.product_stock_id, z.variant, z.order_id, z.order_detail_id,
       z.order_code, z.reason, z.source, z.qty_before, z.order_qty, z.zeroed_by_id, z.created_at
FROM stock_zeroings AS z;

-- ---------------------------------------------------------------------
-- Marketing: WhatsApp campaigns and app/web push notifications (counts only;
-- the audience lists are left out).
-- ---------------------------------------------------------------------
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.whatsapp_campaigns AS
SELECT w.id, w.name, w.template_id, w.coupon_id, w.coupon_code, w.status,
       w.scheduled_at, w.started_at, w.finished_at,
       w.recipients_count, w.accepted_count, w.failed_count, w.delivered_count,
       w.read_count, w.clicked_count, w.created_at
FROM whatsapp_campaigns AS w;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.custom_notification_batches AS
SELECT b.id, b.notification_type_id, b.title, b.link, b.all_users, b.all_guests,
       b.send_type, b.status, b.scheduled_at, b.started_at, b.finished_at,
       b.recipients_count, b.sent_count, b.failed_count, b.created_at
FROM custom_notification_batches AS b;


-- =====================================================================
-- C. Features deployed after 2026-09-28. Stops here with "Table ... doesn't
--    exist" / "Unknown column" if they are not on the server yet; A and B are
--    already in place. Re-run the whole file after deploying them.
-- =====================================================================

GRANT SELECT ON order_ratings        TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON order_rating_scores  TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON traffic_visits       TO 'kapitano_reports_definer'@'localhost';
GRANT SELECT ON traffic_attributions TO 'kapitano_reports_definer'@'localhost';

-- One rating per checkout (comment left out).
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.order_ratings AS
SELECT r.id, r.order_id, r.combined_order_id, r.user_id, r.seller_id, r.product_id,
       r.status, r.rating, r.source,
       r.requested_at, r.notified_at, r.rated_at, r.dismissed_at, r.created_at
FROM order_ratings AS r;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.order_rating_scores AS
SELECT s.id, s.order_rating_id, s.criterion, s.score
FROM order_rating_scores AS s;

-- Visits per source (facebook, instagram, google, direct, other, app).
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.traffic_visits AS
SELECT v.id, v.source, v.referrer_host, v.utm_source, v.utm_medium, v.utm_campaign, v.created_at
FROM traffic_visits AS v;

-- Source credited to each new customer (subject_type 'user') and checkout
-- (subject_type 'checkout', subject_id = combined_orders.id).
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.traffic_attributions AS
SELECT a.id, a.subject_type, a.subject_id, a.source, a.visit_id, a.created_at
FROM traffic_attributions AS a;

-- users again, with the account deactivation date.
CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.users AS
SELECT u.id, u.user_type, u.created_at,
       u.country_id, u.state_id, u.city_id, u.state, u.city,
       u.banned, u.deactivated_at
FROM users AS u;
-- =====================================================================
-- D. Seller pricing (2026-10-06, migrations 2026_10_06_000001/2). Same
--    rule as C: stops with "Unknown column" until it is deployed.
--
--    What changed for the numbers:
--    - order_details.purchase_price = what the seller gets for the whole line
--      (BASE), frozen when the line is created. Commission of a line =
--      price - purchase_price. NULL on lines placed before 2026-10-06 (and on
--      in-house / partner-store products): those keep the old rule.
--    - shops.commission_percentage is now read with shops.pricing_mode:
--      'markup' = added on top of the seller's price (seller 100 -> sells 120),
--      'discount' = taken off it (seller 100 -> we pay 80), NULL = no margin.
--    - products.purchase_price is now filled for every seller product
--      (it was NULL for most of them), so cost sums go up.
-- =====================================================================

GRANT SELECT ON seller_category_commissions TO 'kapitano_reports_definer'@'localhost';

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.order_details AS
SELECT od.id, od.order_id, od.seller_id, od.product_id, od.variation,
       od.price, od.tax, od.shipping_cost, od.quantity,
       od.purchase_price,
       od.price - od.purchase_price AS commission,
       od.payment_status, od.delivery_status, od.shipping_type, od.pickup_point_id,
       od.earn_point, od.seller_unavailable,
       r.syp_rate,
       od.price * r.syp_rate AS price_syp,
       od.tax * r.syp_rate   AS tax_syp,
       od.purchase_price * r.syp_rate AS purchase_price_syp,
       (od.price - od.purchase_price) * r.syp_rate AS commission_syp,
       od.created_at, od.updated_at
FROM order_details AS od
LEFT JOIN kapitano_reports.order_rates AS r ON r.order_id = od.order_id;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.products AS
SELECT p.id, p.name, p.added_by, p.user_id, p.category_id, p.brand_id,
       p.exchange_type,
       p.unit_price, p.real_price, p.purchase_price,
       p.unit_price * IF(p.exchange_type = 'market', rt.market_multiplier, 1)     AS base_unit_price,
       p.purchase_price * IF(p.exchange_type = 'market', rt.market_multiplier, 1) AS base_purchase_price,
       p.seller_price, p.pricing_mode_override, p.pricing_percent_override,
       p.discount, p.discount_type, p.discount_start_date, p.discount_end_date,
       p.published, p.approved, p.current_stock, p.num_of_sale, p.rating,
       -- Today's Deal / Trending: home sections since 2026-10-25 (custom_home_sections.legacy_key), 0/1 as before
       EXISTS (SELECT 1 FROM custom_home_section_products AS hp JOIN custom_home_sections AS hs ON hs.id = hp.custom_home_section_id WHERE hp.product_id = p.id AND hs.legacy_key = 'featured')    AS featured,
       EXISTS (SELECT 1 FROM custom_home_section_products AS hp JOIN custom_home_sections AS hs ON hs.id = hp.custom_home_section_id WHERE hp.product_id = p.id AND hs.legacy_key = 'todays_deal') AS todays_deal,
       p.wholesale_product, p.digital, p.cash_on_delivery,
       p.created_at, p.updated_at
FROM products AS p
CROSS JOIN kapitano_reports.rates AS rt;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.product_stocks AS
SELECT s.id, s.product_id, s.variant, s.sku, s.price, s.real_price,
       s.seller_price, s.purchase_price, s.qty, s.created_at, s.updated_at
FROM product_stocks AS s;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.shops AS
SELECT s.id, s.user_id, s.name, s.rating, s.num_of_reviews, s.num_of_sale,
       s.verification_status, s.cash_on_delivery_status, s.admin_to_pay,
       s.commission_percentage, s.pricing_mode, s.shipping_cost, s.created_at
FROM shops AS s;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.seller_category_commissions AS
SELECT c.id, c.shop_id, c.category_id, c.percent, c.created_at, c.updated_at
FROM seller_category_commissions AS c;
-- =====================================================================
-- E. Seller entry type (2026-10-07, migration 2026_10_07_000001). Same
--    rule as C: stops with "Unknown column" until it is deployed.
--
--    shops.entry_type = who enters the seller's products:
--    'self' = the seller (ذاتي الإدخال), 'managed' = our staff, logged in as
--    the seller (غير ذاتي الإدخال), NULL = unclassified. Replaces the D view.
-- =====================================================================

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.shops AS
SELECT s.id, s.user_id, s.name, s.rating, s.num_of_reviews, s.num_of_sale,
       s.verification_status, s.cash_on_delivery_status, s.admin_to_pay,
       s.commission_percentage, s.pricing_mode, s.entry_type, s.shipping_cost, s.created_at
FROM shops AS s;
-- =====================================================================
-- F. Seller price currency (2026-10-25, migration 2026_10_25_000001). Same
--    rule as C: stops with "Unknown column" until it is deployed.
--
--    Each seller prices in ONE currency (App\Services\Currency\SellerCurrency):
--    shops.price_currency = products.price_currency of all their products,
--    NULL = the system default currency (US dollars) - every row until staff
--    switch a seller on the Seller Commission page.
--    - products.unit_price / real_price / purchase_price / seller_price and the
--      product_stocks prices are RAW in that currency. base_unit_price /
--      base_purchase_price turn them into BASE (USD) like
--      product_raw_to_base_multiplier(): a SYP price is divided by
--      syp_rate_now (a SYP product is never converted for display: it shows
--      the pounds the seller typed); another currency goes through its
--      currencies.exchange_rate.
--    - Orders do not change: order_details.price / tax / purchase_price and
--      orders.* stay BASE (USD). order_details.seller_currency / seller_amount
--      = what the seller gets for the line in their currency (NULL = the
--      purchase_price, in USD).
--    - shops.admin_to_pay (the seller's balance) is in shops.price_currency.
--    Replaces the D views of products, product_stocks and order_details and
--    the E view of shops.
-- =====================================================================

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.products AS
SELECT p.id, p.name, p.added_by, p.user_id, p.category_id, p.brand_id,
       p.exchange_type, p.price_currency,
       p.unit_price, p.real_price, p.purchase_price,
       p.unit_price * m.multiplier     AS base_unit_price,
       p.purchase_price * m.multiplier AS base_purchase_price,
       p.seller_price, p.pricing_mode_override, p.pricing_percent_override,
       p.discount, p.discount_type, p.discount_start_date, p.discount_end_date,
       p.published, p.approved, p.current_stock, p.num_of_sale, p.rating,
       -- Today's Deal / Trending: home sections since 2026-10-25 (custom_home_sections.legacy_key), 0/1 as before
       EXISTS (SELECT 1 FROM custom_home_section_products AS hp JOIN custom_home_sections AS hs ON hs.id = hp.custom_home_section_id WHERE hp.product_id = p.id AND hs.legacy_key = 'featured')    AS featured,
       EXISTS (SELECT 1 FROM custom_home_section_products AS hp JOIN custom_home_sections AS hs ON hs.id = hp.custom_home_section_id WHERE hp.product_id = p.id AND hs.legacy_key = 'todays_deal') AS todays_deal,
       p.wholesale_product, p.digital, p.cash_on_delivery,
       p.created_at, p.updated_at
FROM products AS p
JOIN (
    SELECT p2.id,
           CASE
               WHEN p2.price_currency IS NULL OR p2.price_currency = d.code
                   THEN IF(p2.exchange_type = 'market', rt.market_multiplier, 1)
               WHEN p2.price_currency = 'SYP'
                   THEN IF(rt.syp_rate_now > 0, 1 / rt.syp_rate_now, 1)
               WHEN c.exchange_rate > 0 AND d.exchange_rate > 0
                   THEN IF(p2.exchange_type = 'market', rt.market_multiplier, 1) * d.exchange_rate / c.exchange_rate
               ELSE IF(p2.exchange_type = 'market', rt.market_multiplier, 1)
           END AS multiplier
    FROM products AS p2
    CROSS JOIN kapitano_reports.rates AS rt
    LEFT JOIN currencies AS d
        ON d.id = (SELECT CAST(bs.value AS UNSIGNED) FROM business_settings AS bs
                   WHERE bs.type = 'system_default_currency' ORDER BY bs.id LIMIT 1)
    LEFT JOIN currencies AS c
        ON c.id = (SELECT c2.id FROM currencies AS c2 WHERE c2.code = p2.price_currency ORDER BY c2.id LIMIT 1)
) AS m ON m.id = p.id;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.product_stocks AS
SELECT s.id, s.product_id, p.price_currency, s.variant, s.sku, s.price, s.real_price,
       s.seller_price, s.purchase_price, s.qty, s.created_at, s.updated_at
FROM product_stocks AS s
LEFT JOIN products AS p ON p.id = s.product_id;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.shops AS
SELECT s.id, s.user_id, s.name, s.rating, s.num_of_reviews, s.num_of_sale,
       s.verification_status, s.cash_on_delivery_status, s.admin_to_pay, s.price_currency,
       s.commission_percentage, s.pricing_mode, s.entry_type, s.shipping_cost, s.created_at
FROM shops AS s;

CREATE OR REPLACE DEFINER = 'kapitano_reports_definer'@'localhost' SQL SECURITY DEFINER
VIEW kapitano_reports.order_details AS
SELECT od.id, od.order_id, od.seller_id, od.product_id, od.variation,
       od.price, od.tax, od.shipping_cost, od.quantity,
       od.purchase_price,
       od.price - od.purchase_price AS commission,
       od.seller_currency, od.seller_amount,
       od.payment_status, od.delivery_status, od.shipping_type, od.pickup_point_id,
       od.earn_point, od.seller_unavailable,
       r.syp_rate,
       od.price * r.syp_rate AS price_syp,
       od.tax * r.syp_rate   AS tax_syp,
       od.purchase_price * r.syp_rate AS purchase_price_syp,
       (od.price - od.purchase_price) * r.syp_rate AS commission_syp,
       od.created_at, od.updated_at
FROM order_details AS od
LEFT JOIN kapitano_reports.order_rates AS r ON r.order_id = od.order_id;