Subversion Repositories SmartDukaan

-- Migration: Scheme Item Windowing (BRD Scheme-Item-Windowing)
-- Adds surrogate PK, audit columns, unique constraint, and safety triggers to fofo.scheme_item

-- Step 1: Add surrogate PK and audit columns
ALTER TABLE fofo.scheme_item
  DROP PRIMARY KEY,
  ADD COLUMN id BIGINT AUTO_INCREMENT PRIMARY KEY FIRST,
  ADD COLUMN updated_by INT NULL,
  ADD COLUMN updated_on TIMESTAMP NULL DEFAULT NULL;

-- Step 2: Backfill audit columns from existing data
UPDATE fofo.scheme_item SET updated_on = create_timestamp WHERE updated_on IS NULL;

-- Step 3: Add unique key on the 4-column business key
ALTER TABLE fofo.scheme_item
  ADD UNIQUE KEY uk_scheme_item_window (catalog_id, scheme_id, start_date, end_date);

-- Step 4: Add index for date-driven lookups
CREATE INDEX idx_scheme_item_date_lookup ON fofo.scheme_item (catalog_id, scheme_id, start_date, end_date);

-- Step 4b: Drop the legacy 2-column unique constraint that survived the
-- DROP PRIMARY KEY in step 1. Without this, the old uniqueness on
-- (scheme_id, catalog_id) still blocks multiple windows for the same
-- (scheme, catalog) pair — defeating the whole windowing feature.
ALTER TABLE fofo.scheme_item DROP INDEX scheme_catalog;

-- Step 5: Safety triggers (MySQL 5.7 — CHECK constraints are parsed but ignored)

DELIMITER $$

CREATE TRIGGER trg_scheme_item_bi BEFORE INSERT ON fofo.scheme_item
FOR EACH ROW
BEGIN
  DECLARE v_scheme_start DATETIME;
  DECLARE v_scheme_end   DATETIME;
  DECLARE v_overlap_cnt  INT;

  -- Validate start <= end
  IF NEW.start_date IS NOT NULL AND NEW.end_date IS NOT NULL AND NEW.start_date > NEW.end_date THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'scheme_item: start_date must be <= end_date';
  END IF;

  -- Validate containment within parent scheme window
  IF NEW.start_date IS NOT NULL AND NEW.end_date IS NOT NULL THEN
    SELECT start_date_time, end_date_time INTO v_scheme_start, v_scheme_end
      FROM catalog.scheme WHERE id = NEW.scheme_id;

    IF NEW.start_date < v_scheme_start OR NEW.end_date > v_scheme_end THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'scheme_item: window outside parent scheme window';
    END IF;

    -- Validate non-overlap within same (catalog_id, scheme_id)
    SELECT COUNT(*) INTO v_overlap_cnt FROM fofo.scheme_item
      WHERE catalog_id = NEW.catalog_id
        AND scheme_id  = NEW.scheme_id
        AND NEW.start_date < end_date
        AND NEW.end_date   > start_date;

    IF v_overlap_cnt > 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'scheme_item: window overlaps existing row';
    END IF;
  END IF;
END$$

CREATE TRIGGER trg_scheme_item_bu BEFORE UPDATE ON fofo.scheme_item
FOR EACH ROW
BEGIN
  DECLARE v_scheme_start DATETIME;
  DECLARE v_scheme_end   DATETIME;
  DECLARE v_overlap_cnt  INT;

  -- Validate start <= end
  IF NEW.start_date IS NOT NULL AND NEW.end_date IS NOT NULL AND NEW.start_date > NEW.end_date THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'scheme_item: start_date must be <= end_date';
  END IF;

  -- Validate containment within parent scheme window
  IF NEW.start_date IS NOT NULL AND NEW.end_date IS NOT NULL THEN
    SELECT start_date_time, end_date_time INTO v_scheme_start, v_scheme_end
      FROM catalog.scheme WHERE id = NEW.scheme_id;

    IF NEW.start_date < v_scheme_start OR NEW.end_date > v_scheme_end THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'scheme_item: window outside parent scheme window';
    END IF;

    -- Validate non-overlap within same (catalog_id, scheme_id), excluding self
    SELECT COUNT(*) INTO v_overlap_cnt FROM fofo.scheme_item
      WHERE catalog_id = NEW.catalog_id
        AND scheme_id  = NEW.scheme_id
        AND id        <> NEW.id
        AND NEW.start_date < end_date
        AND NEW.end_date   > start_date;

    IF v_overlap_cnt > 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'scheme_item: window overlaps existing row';
    END IF;
  END IF;
END$$

DELIMITER ;