/*
 * Migrate Gift Cards - Part 1.
 *
 * Migrating gift cards is a two step process. Run this script BEFORE
 * running XMS migrations (or exporting). This simply copies the existing
 * data to temporary tables.
 * 
 */

 /*
 * Create temporary tables to hold existing gift cards.
 */

DROP TABLE IF EXISTS OL_GiftCard_TMP;
DROP TABLE IF EXISTS OL_GiftCardTrans_TMP;

CREATE TABLE OL_GiftCard_TMP (
	Id int NOT NULL,
	GiftCardProgramId int NOT NULL,
	Code varchar(8000) NOT NULL,
	CustomerId int NULL,
	PosId varchar(255) NULL,
	Type int NOT NULL,
	Status int NOT NULL,
	Balance decimal(38, 6) NOT NULL,
	PendingAmount decimal(38, 6) NOT NULL,
	QrImageId varchar(50) NULL,
	Pin varchar(8000) NULL,
	ExpiresOn datetime NULL,
	LastUsedOn datetime NULL,
	CreatedOn datetime NOT NULL,
	UpdatedOn datetime NOT NULL,
 CONSTRAINT PK_OL_GiftCard_TMP PRIMARY KEY CLUSTERED 
 (
	Id ASC
 )
)

CREATE TABLE dbo.OL_GiftCardTrans_TMP(
	Id int NOT NULL,
	GiftCardId int NOT NULL,
	Type int NOT NULL,
	Status int NOT NULL,
	AuthCode varchar(50) NULL,
	Amount decimal(38, 6) NOT NULL,
	Date datetime NOT NULL,
	ReportDay bigint NOT NULL,
	PosId varchar(255) NULL,
	SourceCode varchar(8000) NULL,
	SourcePin varchar(8000) NULL,
	RegisterId varchar(8000) NULL,
	RefNo varchar(8000) NULL,
	CreatedOn datetime NOT NULL,
	UpdatedOn datetime NOT NULL,
 CONSTRAINT PK_OL_GiftCardTrans_TMP PRIMARY KEY CLUSTERED 
 (
	Id ASC
 )
)
GO

/*
 * Copy existing gift cards to temporary tables.
 * Ignore records that represent incomplete cards or transactions.
 */

INSERT INTO OL_GiftCard_TMP
SELECT * FROM OL_GiftCard
WHERE Status IN (2,3) -- Active & Terminated cards only
GO

INSERT INTO OL_GiftCardTrans_TMP
SELECT t.* FROM OL_GiftCardTrans t
INNER JOIN OL_GiftCard g ON g.Id = t.GiftCardId
WHERE g.Status IN (2,3) -- Active & Terminated cards only
AND t.Status IN (1,3,4) -- Completed, Authorized, Cancelled
;
GO

/*
 * Migrate Gift Cards - Part 2.
 *
 * Migrating gift cards is a two step process. Run this script AFTER
 * running XMS migrations - you may need to import the data into
 * temporary tables.
 *
 * This script copies the legacy gift card data into the new XMS table
 * structures, as of Oct 2020.
 *
 * IMPORTANT: this script includes SQL to create claim codes to represent
 * old-style PIN values on gift cards. However, the semantics of PIN versus 
 * claim codes is different. ONLY create claim codes if PIN values are unique
 * amongst other claim codes, AND unique across other gift card codes (e.g.
 * physical).
 *
 * If the PIN values cannot satisfy the above requirements, make sure that you
 * comment out the claim-code SQL sections below.
 *
 */

/*
 * Copy gift card rows.
 */

SET IDENTITY_INSERT OL_GiftCard ON
GO

INSERT INTO OL_GiftCard
(
  Id,
  CustomerId,
  Status,
  Balance,
  LastUsedOn,
  CreatedOn,
  UpdatedOn
)
SELECT
  Id,
  CustomerId,
  CASE
    WHEN Status = 3 THEN 1  -- Terminated
	ELSE 0 -- Active
  END AS Status,
  Balance,
  LastUsedOn,
  CreatedOn,
  UpdatedOn
FROM
  OL_GiftCard_TMP
GO

SET IDENTITY_INSERT OL_GiftCard OFF
GO
DBCC CHECKIDENT(OL_GiftCard, RESEED)
GO

/*
 * Copy gift card trans rows.
 */

SET IDENTITY_INSERT OL_GiftCardTrans ON
GO

INSERT INTO OL_GiftCardTrans
(
 Id,
 GiftCardId,
 Type,
 Status,
 AuthCode,
 Amount,
 Date,
 ReportDay,
 SourceId,
 RefNo,
 CreatedOn,
 UpdatedOn
)
SELECT
  Id,
  GiftCardId,
  Type,
  CASE
    WHEN Status = 1 THEN 0  -- Completed
    WHEN Status = 3 THEN 1  -- Authorized
	ELSE 2 -- Cancelled
  END AS Status,
  AuthCode,
  Amount,
  Date,
  ReportDay,
  RegisterId,
  RefNo,
  CreatedOn,
  UpdatedOn
FROM OL_GiftCardTrans_TMP
GO

SET IDENTITY_INSERT OL_GiftCardTrans OFF
GO
DBCC CHECKIDENT(OL_GiftCardTrans, RESEED)
GO

/*
 * Create gift card code rows - standard codes.
 */

INSERT INTO OL_GiftCardCode
(
  GiftCardId,
  CodeType,
  Code,
  ExpiresOn,
  CreatedOn,
  UpdatedOn
)
SELECT
  Id,
  CASE
    WHEN Type = 0 THEN 1  -- Personal
    WHEN Type = 1 THEN 2  -- Shared
	ELSE 0 -- Physical
  END AS CodeType,
  Code,
  ExpiresOn,
  CreatedOn,
  UpdatedOn
FROM
  OL_GiftCard_TMP
GO

/*
 * Create gift card code rows - PIN values as claim codes.
 * You may want to comment this out, if PIN values are not
 * unique across all gift card codes.
 */

INSERT INTO OL_GiftCardCode
(
  GiftCardId,
  CodeType,
  Code,
  ExpiresOn,
  CreatedOn,
  UpdatedOn
)
SELECT
  Id,
  4,
  Pin,
  ExpiresOn,
  CreatedOn,
  UpdatedOn
FROM
  OL_GiftCard_TMP
WHERE
  Pin IS NOT NULL
GO

/*
 * Drop temp gift cards tables.
 */

DROP TABLE OL_GiftCard_TMP;
DROP TABLE OL_GiftCardTrans_TMP;