Subversion Repositories SmartDukaan

-- PJP agenda instances (Jul 2026).
-- Lifecycle-tracked visit agendas: AUTO instances opened/closed by nightly cron
-- (23:45, after the 23:30 activation-type cron), MANUAL instances by the visiting
-- team. Config: fofo_id = 0 row = global default (NOT NULL — MySQL unique keys
-- ignore NULLs, so 0 keeps (agenda_type, fofo_id) enforceable).

CREATE TABLE `user`.`agenda_rule_config` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `agenda_type` VARCHAR(32) NOT NULL,
  `fofo_id` INT NOT NULL DEFAULT 0,
  `params` VARCHAR(500) NOT NULL,
  `updated_by` VARCHAR(64) NULL,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_type_fofo` (`agenda_type`, `fofo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- open_marker: 1 while OPEN, NULL when CLOSED — the unique key then enforces at
-- most one OPEN instance per (fofo_id, agenda_type) while allowing any number of
-- CLOSED history rows.
CREATE TABLE `user`.`agenda_instance` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `fofo_id` INT NOT NULL,
  `agenda_type` VARCHAR(32) NOT NULL,
  `source` VARCHAR(8) NOT NULL,
  `status` VARCHAR(8) NOT NULL DEFAULT 'OPEN',
  `opened_on` DATETIME NOT NULL,
  `opened_by` INT NULL,
  `open_metric` VARCHAR(255) NULL,
  `closed_on` DATETIME NULL,
  `closed_by` INT NULL,
  `close_metric` VARCHAR(255) NULL,
  `close_remark` VARCHAR(500) NULL,
  `open_marker` TINYINT AS (IF(`status` = 'OPEN', 1, NULL)) STORED,
  UNIQUE KEY `uq_one_open` (`fofo_id`, `agenda_type`, `open_marker`),
  KEY `idx_fofo_status` (`fofo_id`, `status`),
  KEY `idx_type_status` (`agenda_type`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user`.`agenda_visit_log` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `agenda_instance_id` INT NOT NULL,
  `location_tracking_id` INT NULL,
  `visitor_user_id` INT NOT NULL,
  `visit_date` DATE NOT NULL,
  `discussion` TEXT NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_instance` (`agenda_instance_id`),
  KEY `idx_visit_date` (`visit_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user`.`agenda_rule_config` (`agenda_type`,`fofo_id`,`params`,`updated_by`) VALUES
 ('LOW_INVESTMENT', 0, '{"shortPct":50}', 'seed'),
 ('LOW_SALES',      0, '{"flagPct":40,"clearPct":75,"baselineDays":90,"windowDays":30}', 'seed'),
 ('LOW_PURCHASE',   0, '{"gapDays":15}', 'seed'),
 ('CREDIT_DUES',    0, '{"loanAgeDays":20}', 'seed');
-- REVIVAL: no params row - mirrors fofo_store.activation_type (Rs 99,999/60d cron).