CREATE TABLE IF NOT EXISTS WebsiteAcquisitionEventReceipt (
  EventKey CHAR(64) NOT NULL,
  WebsiteID INT NOT NULL,
  EventType ENUM('REGISTER','FIRST_DEPOSIT') NOT NULL,
  ExternalEventID VARCHAR(191) NULL,
  FirstReceivedAtUtc DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (EventKey),
  KEY idx_waer_website_type (WebsiteID, EventType, FirstReceivedAtUtc),
  CONSTRAINT fk_waer_website FOREIGN KEY (WebsiteID) REFERENCES Website(ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS WebsiteAcquisitionEventRaw (
  ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  EventKey CHAR(64) NOT NULL,
  WebsiteID INT NOT NULL,
  CM VARCHAR(100) NOT NULL,
  EventType ENUM('REGISTER','FIRST_DEPOSIT') NOT NULL,
  ExternalEventID VARCHAR(191) NULL,
  UserExternalID VARCHAR(191) NOT NULL,
  Amount DECIMAL(20,8) NULL,
  MerchantID VARCHAR(191) NULL,
  SourceCreatedAtRaw VARCHAR(100) NOT NULL,
  SourceCreatedAtUtc DATETIME(6) NOT NULL,
  SourceCreatedAtLocal DATETIME(6) NOT NULL,
  SourceTimezone VARCHAR(100) NOT NULL,
  LocalDate DATE NOT NULL,
  RawPayload JSON NOT NULL,
  ReceivedAtUtc DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (ID),
  UNIQUE KEY uq_waeraw_event (EventKey),
  KEY idx_waeraw_daily (WebsiteID, LocalDate, EventType),
  CONSTRAINT fk_waeraw_website FOREIGN KEY (WebsiteID) REFERENCES Website(ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS WebsiteAcquisitionDailySummary (
  ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  WebsiteID INT NOT NULL,
  SummaryDate DATE NOT NULL,
  Currency VARCHAR(20) NOT NULL,
  SourceTimezone VARCHAR(100) NOT NULL,
  NewCount INT UNSIGNED NOT NULL DEFAULT 0,
  FTDCount INT UNSIGNED NOT NULL DEFAULT 0,
  FTDAmount DECIMAL(20,8) NOT NULL DEFAULT 0,
  FirstSummarizedAtUtc DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  LastSummarizedAtUtc DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (ID),
  UNIQUE KEY uq_wads_website_date (WebsiteID, SummaryDate),
  KEY idx_wads_date (SummaryDate),
  CONSTRAINT fk_wads_website FOREIGN KEY (WebsiteID) REFERENCES Website(ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP PROCEDURE IF EXISTS SummarizeWebsiteAcquisitionDay;
DELIMITER $$
CREATE PROCEDURE SummarizeWebsiteAcquisitionDay(IN p_summary_date DATE, IN p_website_id INT)
BEGIN
  DECLARE v_rows INT DEFAULT 0;
  START TRANSACTION;

  SELECT COUNT(*) INTO v_rows
    FROM WebsiteAcquisitionEventRaw
   WHERE LocalDate = p_summary_date
     AND (p_website_id IS NULL OR p_website_id = 0 OR WebsiteID = p_website_id)
   FOR UPDATE;

  IF v_rows > 0 THEN
    INSERT INTO WebsiteAcquisitionDailySummary
      (WebsiteID, SummaryDate, Currency, SourceTimezone, NewCount, FTDCount, FTDAmount)
    SELECT raw.WebsiteID, raw.LocalDate, COALESCE(wg.Currency, 'MYR'), raw.SourceTimezone,
           COUNT(DISTINCT CASE WHEN raw.EventType='REGISTER' THEN raw.UserExternalID END),
           COUNT(DISTINCT CASE WHEN raw.EventType='FIRST_DEPOSIT' THEN raw.UserExternalID END),
           COALESCE(SUM(CASE WHEN raw.EventType='FIRST_DEPOSIT' THEN raw.Amount ELSE 0 END), 0)
      FROM WebsiteAcquisitionEventRaw raw
      INNER JOIN Website w ON w.ID=raw.WebsiteID
      LEFT JOIN WebsiteGroup wg ON wg.ID=w.GroupID
     WHERE raw.LocalDate = p_summary_date
       AND (p_website_id IS NULL OR p_website_id = 0 OR raw.WebsiteID = p_website_id)
     GROUP BY raw.WebsiteID, raw.LocalDate, COALESCE(wg.Currency, 'MYR'), raw.SourceTimezone
    ON DUPLICATE KEY UPDATE
      NewCount = NewCount + VALUES(NewCount),
      FTDCount = FTDCount + VALUES(FTDCount),
      FTDAmount = FTDAmount + VALUES(FTDAmount),
      LastSummarizedAtUtc = CURRENT_TIMESTAMP(6);

    DELETE FROM WebsiteAcquisitionEventRaw
     WHERE LocalDate = p_summary_date
       AND (p_website_id IS NULL OR p_website_id = 0 OR WebsiteID = p_website_id);
  END IF;

  COMMIT;
  SELECT v_rows AS RawRowsProcessed;
END$$
DELIMITER ;

-- Examples:
-- CALL SummarizeWebsiteAcquisitionDay('2026-07-22', NULL); -- all websites
-- CALL SummarizeWebsiteAcquisitionDay('2026-07-22', 1);    -- one WebsiteID

