| 37163 |
amit |
1 |
-- PJP agenda instances (Jul 2026).
|
|
|
2 |
-- Lifecycle-tracked visit agendas: AUTO instances opened/closed by nightly cron
|
|
|
3 |
-- (23:45, after the 23:30 activation-type cron), MANUAL instances by the visiting
|
|
|
4 |
-- team. Config: fofo_id = 0 row = global default (NOT NULL — MySQL unique keys
|
|
|
5 |
-- ignore NULLs, so 0 keeps (agenda_type, fofo_id) enforceable).
|
|
|
6 |
|
|
|
7 |
CREATE TABLE `user`.`agenda_rule_config` (
|
|
|
8 |
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
|
9 |
`agenda_type` VARCHAR(32) NOT NULL,
|
|
|
10 |
`fofo_id` INT NOT NULL DEFAULT 0,
|
|
|
11 |
`params` VARCHAR(500) NOT NULL,
|
|
|
12 |
`updated_by` VARCHAR(64) NULL,
|
|
|
13 |
`updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
14 |
UNIQUE KEY `uq_type_fofo` (`agenda_type`, `fofo_id`)
|
|
|
15 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
16 |
|
|
|
17 |
-- open_marker: 1 while OPEN, NULL when CLOSED — the unique key then enforces at
|
|
|
18 |
-- most one OPEN instance per (fofo_id, agenda_type) while allowing any number of
|
|
|
19 |
-- CLOSED history rows.
|
|
|
20 |
CREATE TABLE `user`.`agenda_instance` (
|
|
|
21 |
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
|
22 |
`fofo_id` INT NOT NULL,
|
|
|
23 |
`agenda_type` VARCHAR(32) NOT NULL,
|
|
|
24 |
`source` VARCHAR(8) NOT NULL,
|
|
|
25 |
`status` VARCHAR(8) NOT NULL DEFAULT 'OPEN',
|
|
|
26 |
`opened_on` DATETIME NOT NULL,
|
|
|
27 |
`opened_by` INT NULL,
|
|
|
28 |
`open_metric` VARCHAR(255) NULL,
|
|
|
29 |
`closed_on` DATETIME NULL,
|
|
|
30 |
`closed_by` INT NULL,
|
|
|
31 |
`close_metric` VARCHAR(255) NULL,
|
|
|
32 |
`close_remark` VARCHAR(500) NULL,
|
|
|
33 |
`open_marker` TINYINT AS (IF(`status` = 'OPEN', 1, NULL)) STORED,
|
|
|
34 |
UNIQUE KEY `uq_one_open` (`fofo_id`, `agenda_type`, `open_marker`),
|
|
|
35 |
KEY `idx_fofo_status` (`fofo_id`, `status`),
|
|
|
36 |
KEY `idx_type_status` (`agenda_type`, `status`)
|
|
|
37 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
38 |
|
|
|
39 |
CREATE TABLE `user`.`agenda_visit_log` (
|
|
|
40 |
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
|
41 |
`agenda_instance_id` INT NOT NULL,
|
|
|
42 |
`location_tracking_id` INT NULL,
|
|
|
43 |
`visitor_user_id` INT NOT NULL,
|
|
|
44 |
`visit_date` DATE NOT NULL,
|
|
|
45 |
`discussion` TEXT NULL,
|
|
|
46 |
`created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
47 |
KEY `idx_instance` (`agenda_instance_id`),
|
|
|
48 |
KEY `idx_visit_date` (`visit_date`)
|
|
|
49 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
50 |
|
|
|
51 |
INSERT INTO `user`.`agenda_rule_config` (`agenda_type`,`fofo_id`,`params`,`updated_by`) VALUES
|
|
|
52 |
('LOW_INVESTMENT', 0, '{"shortPct":50}', 'seed'),
|
|
|
53 |
('LOW_SALES', 0, '{"flagPct":40,"clearPct":75,"baselineDays":90,"windowDays":30}', 'seed'),
|
|
|
54 |
('LOW_PURCHASE', 0, '{"gapDays":15}', 'seed'),
|
|
|
55 |
('CREDIT_DUES', 0, '{"loanAgeDays":20}', 'seed');
|
|
|
56 |
-- REVIVAL: no params row - mirrors fofo_store.activation_type (Rs 99,999/60d cron).
|