fluent-cart / database / Migrations / RetentionSnapshotsMigrator.php

RetentionSnapshotsMigrator.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.6.4, at database/Migrations/RetentionSnapshotsMigrator.php

48 lines 2.1 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\Database\Migrations;
4
5	class RetentionSnapshotsMigrator extends Migrator
6	{
7	public static string $tableName = "fct_retention_snapshots";
8
9	public static function getSqlSchema(): string
10	{
11	$indexPrefix = static::getDbPrefix() . 'fct_rs_';
12
13	return "`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
14	`cohort` VARCHAR(7) NOT NULL COMMENT 'YYYY-MM format, when customer first subscribed',
15	`period` VARCHAR(7) NOT NULL COMMENT 'YYYY-MM format, the month being measured',
16	`product_id` BIGINT(20) UNSIGNED NULL COMMENT 'NULL means all products combined',
17
18	-- Cohort baseline (at cohort month)
19	`cohort_customers` INT UNSIGNED NOT NULL DEFAULT 0,
20	`cohort_mrr` BIGINT UNSIGNED NOT NULL DEFAULT 0,
21
22	-- Retention at this period
23	`retained_customers` INT UNSIGNED NOT NULL DEFAULT 0,
24	`retained_mrr` BIGINT UNSIGNED NOT NULL DEFAULT 0,
25
26	-- Movement tracking
27	`new_customers` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Recyclers who came back this period',
28	`churned_customers` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Left this period',
29
30	-- Pre-calculated rates (for fast queries)
31	`retention_rate_customers` DECIMAL(5,2) NULL,
32	`retention_rate_mrr` DECIMAL(5,2) NULL,
33
34	-- Period offset from cohort (Month 1, Month 2, etc)
35	`period_offset` INT UNSIGNED NOT NULL DEFAULT 0,
36
37	-- Timestamps
38	`created_at` DATETIME NULL,
39	`updated_at` DATETIME NULL,
40
41	UNIQUE INDEX `{$indexPrefix}cohort_period_product_idx` (`cohort`, `period`, `product_id`),
42	INDEX `{$indexPrefix}cohort_idx` (`cohort`),
43	INDEX `{$indexPrefix}period_idx` (`period`),
44	INDEX `{$indexPrefix}product_idx` (`product_id`),
45	INDEX `{$indexPrefix}offset_idx` (`period_offset`)";
46	}
47	}
48