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 ;