<?php

namespace App\Repositories\Admin\Member;

use Illuminate\Support\Facades\DB;

final class MemberMarketingBatchRepository
{
    public function upsertDaily(string $asOfDate): int
    {
        $from = $asOfDate.' 00:00:00';
        $to   = $asOfDate.' 23:59:59';

        $sql = <<<SQL
INSERT INTO mem_marketing_purchase_daily
  (ymd, mem_no,
   paid_count, paid_amount, paid_first_at, paid_last_at,
   cancel_count, cancel_amount)
SELECT
  ? AS ymd,
  x.mem_no,
  SUM(x.paid_count)      AS paid_count,
  SUM(x.paid_amount)     AS paid_amount,
  MIN(x.paid_first_at)   AS paid_first_at,
  MAX(x.paid_last_at)    AS paid_last_at,
  SUM(x.cancel_count)    AS cancel_count,
  SUM(x.cancel_amount)   AS cancel_amount
FROM (
  SELECT
    po.mem_no,
    COUNT(*)              AS paid_count,
    SUM(po.pay_money)     AS paid_amount,
    MIN(po.dt_stat_pay_p) AS paid_first_at,
    MAX(po.dt_stat_pay_p) AS paid_last_at,
    0 AS cancel_count,
    0 AS cancel_amount
  FROM pin_order po
  WHERE po.mem_no IS NOT NULL
    AND po.stat_pay = 'p'
    AND po.dt_stat_pay_p BETWEEN ? AND ?
  GROUP BY po.mem_no

  UNION ALL

  SELECT
    po.mem_no,
    0 AS paid_count,
    0 AS paid_amount,
    NULL AS paid_first_at,
    NULL AS paid_last_at,
    COUNT(*)            AS cancel_count,
    SUM(po.pay_money)   AS cancel_amount
  FROM pin_order po
  WHERE po.mem_no IS NOT NULL
    AND po.stat_pay = 'c'
    AND po.dt_stat_cancel BETWEEN ? AND ?
  GROUP BY po.mem_no
) x
GROUP BY x.mem_no
ON DUPLICATE KEY UPDATE
  paid_count    = VALUES(paid_count),
  paid_amount   = VALUES(paid_amount),
  paid_first_at = VALUES(paid_first_at),
  paid_last_at  = VALUES(paid_last_at),
  cancel_count  = VALUES(cancel_count),
  cancel_amount = VALUES(cancel_amount);
SQL;

        // rowcount는 드라이버/설정에 따라 정확하지 않을 수 있어 “실제 건수”는 별도 count로 잡는다.
        DB::statement($sql, [$asOfDate, $from, $to, $from, $to]);

        return (int) DB::table('mem_marketing_purchase_daily')->where('ymd', $asOfDate)->count();
    }

    public function rebuildPurchaseTotal(string $asOfDate): int
    {
        DB::statement('DROP TABLE IF EXISTS mem_marketing_purchase_total_tmp');
        DB::statement('CREATE TABLE mem_marketing_purchase_total_tmp LIKE mem_marketing_purchase_total');

        $sql = <<<SQL
INSERT INTO mem_marketing_purchase_total_tmp
  (mem_no, first_purchase_at, last_purchase_at,
   paid_count_total, paid_amount_total,
   cancel_count_total, cancel_amount_total,
   as_of_date)
SELECT
  d.mem_no,
  MIN(d.paid_first_at) AS first_purchase_at,
  MAX(d.paid_last_at)  AS last_purchase_at,
  SUM(d.paid_count)    AS paid_count_total,
  SUM(d.paid_amount)   AS paid_amount_total,
  SUM(d.cancel_count)  AS cancel_count_total,
  SUM(d.cancel_amount) AS cancel_amount_total,
  ? AS as_of_date
FROM mem_marketing_purchase_daily d
GROUP BY d.mem_no;
SQL;

        DB::statement($sql, [$asOfDate]);

        // 스왑
        DB::statement('DROP TABLE IF EXISTS mem_marketing_purchase_total_old');
        DB::statement('RENAME TABLE mem_marketing_purchase_total TO mem_marketing_purchase_total_old, mem_marketing_purchase_total_tmp TO mem_marketing_purchase_total');
        DB::statement('DROP TABLE mem_marketing_purchase_total_old');

        return (int) DB::table('mem_marketing_purchase_total')->count();
    }

    public function rebuildStats(string $asOfDate): int
    {
        DB::statement('DROP TABLE IF EXISTS mem_marketing_stats_tmp');
        DB::statement('CREATE TABLE mem_marketing_stats_tmp LIKE mem_marketing_stats');

        $sql = <<<SQL
INSERT INTO mem_marketing_stats_tmp (
  mem_no,
  stat_3, stat_3_at,
  reg_at, last_login_at, has_login,
  days_since_reg, days_since_login,
  has_email, has_phone, optin_email, optin_sms, optin_push,
  pv_sns, country_code,
  first_purchase_at, last_purchase_at,
  purchase_count_total, purchase_amount_total,
  purchase_count_30d, purchase_amount_30d,
  purchase_count_90d, purchase_amount_90d,
  as_of_date
)
SELECT
  mi.mem_no,
  CAST(mi.stat_3 AS UNSIGNED) AS stat_3,
  NULLIF(mi.dt_stat_3, '0000-00-00 00:00:00') AS stat_3_at,
  NULLIF(mi.dt_reg,   '0000-00-00 00:00:00') AS reg_at,
  NULLIF(mi.dt_login, '0000-00-00 00:00:00') AS last_login_at,
  CASE WHEN NULLIF(mi.dt_login, '0000-00-00 00:00:00') IS NULL THEN 0 ELSE 1 END AS has_login,
  CASE
    WHEN NULLIF(mi.dt_reg, '0000-00-00 00:00:00') IS NULL THEN 0
    ELSE GREATEST(DATEDIFF(?, DATE(NULLIF(mi.dt_reg, '0000-00-00 00:00:00'))), 0)
  END AS days_since_reg,
  CASE
    WHEN NULLIF(mi.dt_login, '0000-00-00 00:00:00') IS NULL THEN NULL
    ELSE GREATEST(DATEDIFF(?, DATE(NULLIF(mi.dt_login, '0000-00-00 00:00:00'))), 0)
  END AS days_since_login,
  CASE WHEN mi.email IS NULL OR mi.email='' THEN 0 ELSE 1 END AS has_email,
  CASE WHEN mi.cell_phone IS NULL OR mi.cell_phone='' THEN 0 ELSE 1 END AS has_phone,
  CASE WHEN mi.rcv_email='y' THEN 1 ELSE 0 END AS optin_email,
  CASE WHEN mi.rcv_sms  ='y' THEN 1 ELSE 0 END AS optin_sms,
  CASE WHEN mi.rcv_push ='y' THEN 1 ELSE 0 END AS optin_push,
  mi.pv_sns,
  mi.country_code,
  pt.first_purchase_at,
  pt.last_purchase_at,
  COALESCE(pt.paid_count_total, 0)  AS purchase_count_total,
  COALESCE(pt.paid_amount_total, 0) AS purchase_amount_total,
  COALESCE(w30.c30, 0) AS purchase_count_30d,
  COALESCE(w30.a30, 0) AS purchase_amount_30d,
  COALESCE(w90.c90, 0) AS purchase_count_90d,
  COALESCE(w90.a90, 0) AS purchase_amount_90d,
  ? AS as_of_date
FROM mem_info mi
LEFT JOIN mem_marketing_purchase_total pt
  ON pt.mem_no = mi.mem_no
LEFT JOIN (
  SELECT mem_no, SUM(paid_count) c30, SUM(paid_amount) a30
  FROM mem_marketing_purchase_daily
  WHERE ymd BETWEEN DATE_SUB(?, INTERVAL 29 DAY) AND ?
  GROUP BY mem_no
) w30 ON w30.mem_no = mi.mem_no
LEFT JOIN (
  SELECT mem_no, SUM(paid_count) c90, SUM(paid_amount) a90
  FROM mem_marketing_purchase_daily
  WHERE ymd BETWEEN DATE_SUB(?, INTERVAL 89 DAY) AND ?
  GROUP BY mem_no
) w90 ON w90.mem_no = mi.mem_no;
SQL;

        // placeholders: DATEDIFF(?,...), DATEDIFF(?,...), as_of_date, w30 range(?,?), w90 range(?,?)
        DB::statement($sql, [
            $asOfDate, $asOfDate,
            $asOfDate,
            $asOfDate, $asOfDate,
            $asOfDate, $asOfDate,
        ]);

        // 스왑
        DB::statement('DROP TABLE IF EXISTS mem_marketing_stats_old');
        DB::statement('RENAME TABLE mem_marketing_stats TO mem_marketing_stats_old, mem_marketing_stats_tmp TO mem_marketing_stats');
        DB::statement('DROP TABLE mem_marketing_stats_old');

        return (int) DB::table('mem_marketing_stats')->count();
    }

    public function upsertBatchRunStart(string $asOfDate): void
    {
        DB::statement(
            "INSERT INTO marketing_batch_runs (as_of_date, status, daily_rows, total_rows, stats_rows, error_message, started_at, finished_at)
             VALUES (?, 'running', 0, 0, 0, NULL, NOW(), NULL)
             ON DUPLICATE KEY UPDATE status='running', error_message=NULL, started_at=VALUES(started_at), finished_at=NULL",
            [$asOfDate]
        );
    }

    public function markBatchRunSuccess(string $asOfDate, int $dailyRows, int $totalRows, int $statsRows): void
    {
        DB::statement(
            "UPDATE marketing_batch_runs
             SET status='success', daily_rows=?, total_rows=?, stats_rows=?, finished_at=NOW()
             WHERE as_of_date=?",
            [$dailyRows, $totalRows, $statsRows, $asOfDate]
        );
    }

    public function markBatchRunFailed(string $asOfDate, string $err): void
    {
        DB::statement(
            "UPDATE marketing_batch_runs
             SET status='failed', error_message=?, finished_at=NOW()
             WHERE as_of_date=?",
            [$err, $asOfDate]
        );
    }
}
