-- ============================================================================
-- Delete unused discount codes older than 2023-01-01 or having invalid dates.
-- ============================================================================
DELETE  FROM discount_code_filters
 WHERE  discount_code_id IN (
            SELECT  discountid
              FROM  discount_codes
             WHERE  (SELECT COUNT(*) FROM order_rows s WHERE s.sub_type = 'discount_code' AND s.data ->> 'code' = code) = 0
               AND  ("end" < '2023-01-01' OR  start > "end")
        );

DELETE  FROM discount_codes
 WHERE  discountid IN (
            SELECT  discountid
              FROM  discount_codes dc
             WHERE  (SELECT COUNT(*) FROM order_rows s WHERE s.sub_type = 'discount_code' AND s.data ->> 'code' = code) = 0
               AND  ("end" < '2023-01-01' OR start > "end")
        );

-- ============================================================================
-- Delete discounts with codes occuring more than once.
-- Keep the latest inserted.
-- ============================================================================
CREATE INDEX IF NOT EXISTS ix_discount_codes_discountid_lower_code ON discount_codes (discountid, LOWER(code));

DELETE  FROM discount_code_filters
 WHERE  NOT discount_code_id IN (
            SELECT  discountid
              FROM  discount_codes dc
             WHERE  discountid = (
                        SELECT  MAX(discountid)
                          FROM  discount_codes dcs
                         WHERE  LOWER(dc.code) = LOWER(dcs.code)
                    )
        );

DELETE  FROM discount_codes
 WHERE  NOT discountid IN (
            SELECT  discountid
              FROM  discount_codes dc
             WHERE  discountid = (
                        SELECT  MAX(discountid)
                          FROM  discount_codes dcs
                         WHERE  LOWER(dc.code) = LOWER(dcs.code)
                    )
        );

DROP INDEX IF EXISTS ix_discount_codes_discountid_lower_code;

-- ============================================================================
-- Make existing codes lower cased.
-- ============================================================================

-- Keep existing updated timestamps
DROP TRIGGER IF EXISTS updated_timestamp ON public.discount_codes;

UPDATE  discount_codes
   SET  code = LOWER(code);

CREATE TRIGGER updated_timestamp
    BEFORE UPDATE
    ON public.discount_codes
    FOR EACH ROW
    EXECUTE FUNCTION public.updated_timestamp();

-- ============================================================================
-- Make code unique.
-- ============================================================================
CREATE UNIQUE INDEX IF NOT EXISTS uix_discount_codes_lower_code ON discount_codes (LOWER(code));

-- ============================================================================
-- Ensure start date is before end date.
-- ============================================================================
ALTER TABLE discount_codes
  ADD CONSTRAINT date_range_check
CHECK (start <= "end");