PRINT 'BEGIN INSTALL/UPDATE LP_ASSIGN_PREMIUMS';
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

/***********************************************************************************************************************
Author:       Nick Reilingh, Fisher Center at Bard College
Description:  This procedure creates premium rows to be fulfilled.

Procedure:
  - Start by deriving premiums for all current memberships
  - Remove any premiums in the history table
  - Add everything that remains to the history table
  - Remove premiums that already exist on constituents
  - Add everything else to constituents.
***********************************************************************************************************************/
CREATE OR ALTER PROCEDURE [dbo].[LP_ASSIGN_PREMIUMS]
AS
SET NOCOUNT, XACT_ABORT ON;

WITH memb_org_prem_levels AS (
  SELECT
      p.premium_no,
      ml.memb_org_no,
      ml.[rank],
      p.value_amt
    FROM dbo.LTR_X_PREMIUM_LEVEL pl
      JOIN dbo.T_MEMB_LEVEL ml
        ON pl.memb_level = ml.memb_level
      JOIN dbo.T_PREMIUM p
        ON pl.premium_no = p.premium_no
)
SELECT
    m.campaign_no,
    m.customer_no,
    pl.premium_no
  INTO #new_premiums
  FROM dbo.TX_CUST_MEMBERSHIP m
    JOIN dbo.T_MEMB_LEVEL ml
      ON m.memb_org_no = ml.memb_org_no
        AND m.memb_level = ml.memb_level
    JOIN memb_org_prem_levels pl
      ON ml.memb_org_no = pl.memb_org_no
        AND pl.[rank] >= ml.[rank]
        AND (m.declined_ind <> 'Y' OR pl.value_amt = 0.00)
  WHERE m.cur_record = 'Y'
EXCEPT
SELECT
    campaign_no,
    customer_no,
    premium_no
  FROM dbo.LT_PREMIUM_HIST;

BEGIN TRY

  BEGIN TRAN;

  INSERT dbo.LT_PREMIUM_HIST
        (campaign_no, customer_no, premium_no)
  SELECT campaign_no, customer_no, premium_no
    FROM #new_premiums;

  DELETE n
    FROM #new_premiums n
      JOIN dbo.T_CUST_PREM p
        ON n.campaign_no = p.campaign_no
          AND n.customer_no = p.customer_no
          AND n.premium_no = p.premium;

  DECLARE @insert_count int = (SELECT COUNT(*) FROM #new_premiums);

  IF @insert_count > 0
  BEGIN;

    DECLARE @next_id int;
    EXEC dbo.AP_GET_NEXTID_function @type = 'CP', @nextid = @next_id OUTPUT, @increment = @insert_count;

    WITH inserting AS (
      SELECT
          cust_prem_no = @next_id + ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1,
          i.customer_no,
          i.campaign_no,
          premium = i.premium_no
        FROM #new_premiums i
    )
    INSERT dbo.T_CUST_PREM
          (cust_prem_no, customer_no, campaign_no, premium)
    SELECT cust_prem_no, customer_no, campaign_no, premium
      FROM inserting;

  END;

  COMMIT TRAN;

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  THROW;
END CATCH;

GO

GRANT EXECUTE ON [dbo].[LP_ASSIGN_PREMIUMS] TO [ImpUsers], [tessitura_app];
GO
/* END INSTALL/UPDATE LP_ASSIGN_PREMIUMS */

PRINT 'BEGIN INSTALL LTR_X_PREMIUM_LEVEL';
GO

IF OBJECT_ID(N'[dbo].[LTR_X_PREMIUM_LEVEL]', N'U') IS NOT NULL
BEGIN;
  DROP TABLE [dbo].[LTR_X_PREMIUM_LEVEL];
END;
GO

/****** Object:  Table [dbo].[LTR_X_PREMIUM_LEVEL]   ******/
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO

CREATE TABLE [dbo].[LTR_X_PREMIUM_LEVEL] (
    [premium_no] int NOT NULL,
    [memb_level] varchar(3) NOT NULL,
    [created_by] varchar(8) NULL CONSTRAINT [DF_LTR_X_PREMIUM_LEVEL_created_by] DEFAULT (dbo.FS_USERNAME()),
    [create_dt] datetime NULL CONSTRAINT [DF_LTR_X_PREMIUM_LEVEL_create_dt] DEFAULT (GETDATE()),
    [create_loc] varchar(16) NULL CONSTRAINT [DF_LTR_X_PREMIUM_LEVEL_create_loc] DEFAULT (dbo.FS_LOCATION()),
    [last_updated_by] varchar(8) NULL CONSTRAINT [DF_LTR_X_PREMIUM_LEVEL_last_updated_by] DEFAULT (dbo.FS_USERNAME()),
    [last_update_dt] datetime NOT NULL CONSTRAINT [DF_LTR_X_PREMIUM_LEVEL_last_update_dt] DEFAULT (GETDATE()),
  CONSTRAINT [PK_LTR_X_PREMIUM_LEVEL] PRIMARY KEY CLUSTERED (
    [premium_no] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
);
GO

CREATE TRIGGER [dbo].[LTR_X_PREMIUM_LEVEL_TU]
  ON [dbo].[LTR_X_PREMIUM_LEVEL]
  FOR UPDATE
AS
BEGIN;
SET NOCOUNT ON;
IF dbo.FS_GET_PARAM_FROM_APPNAME('LockTrigger') = 'Y'
  RETURN;
UPDATE  a
  SET   last_updated_by = [dbo].FS_USERNAME(), last_update_dt = GETDATE()
  FROM  [dbo].[LTR_X_PREMIUM_LEVEL] a JOIN inserted b ON a.premium_no = b.premium_no;
END;
GO

EXEC dbo.UP_POPULATE_REFERENCE_METADATA 'LTR_X_PREMIUM_LEVEL'; -- Use below if desired.
UPDATE dbo.TR_REFERENCE_TABLE
  SET help_text = 'This table matches premiums to the minimum level at which they apply. This assumes that all levels are consistent across membership organizations.'
  WHERE table_name = 'LTR_X_PREMIUM_LEVEL';
WITH metadata AS (
  SELECT rt.id, v.*
    FROM (VALUES
      ('premium_no', 'editable', 'N', 'T_PREMIUM', 'premium_no', 'description', NULL),
      ('memb_level', 'editable', 'N', 'LVS_MEMB_LEVEL_UNIQ', 'memb_level', 'memb_level', NULL)
    ) v (column_name, column_type, checkbox, dddw_table, dddw_value, dddw_description, dddw_where)
      JOIN dbo.TR_REFERENCE_TABLE rt ON rt.table_name = 'LTR_X_PREMIUM_LEVEL'
)
MERGE dbo.TR_REFERENCE_COLUMN c
  USING metadata m
    ON c.reference_table_id = m.id AND c.column_name = m.column_name
  WHEN MATCHED THEN UPDATE
    SET c.column_type = m.column_type,
        c.checkbox = m.checkbox,
        c.dddw_table = m.dddw_table,
        c.dddw_value = m.dddw_value,
        c.dddw_description = m.dddw_description,
        c.dddw_where = m.dddw_where;
GO

GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[LTR_X_PREMIUM_LEVEL] TO [ImpUsers], [tessitura_app];
GO

/* END INSTALL LTR_X_PREMIUM_LEVEL */

PRINT 'BEGIN INSTALL LT_PREMIUM_HIST';
GO

IF OBJECT_ID(N'[dbo].[LT_PREMIUM_HIST]', N'U') IS NOT NULL
BEGIN;
  DROP TABLE [dbo].[LT_PREMIUM_HIST];
END;
GO

/****** Object:  Table [dbo].[LT_PREMIUM_HIST]   ******/
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO

CREATE TABLE [dbo].[LT_PREMIUM_HIST] (
    [campaign_no] int NOT NULL,
    [customer_no] int NOT NULL,
    [premium_no] int NOT NULL,
    [create_dt] datetime NULL CONSTRAINT [DF_LT_PREMIUM_HIST_create_dt] DEFAULT (GETDATE()),
  CONSTRAINT [PK_LT_PREMIUM_HIST] PRIMARY KEY CLUSTERED (
    campaign_no ASC, customer_no ASC, premium_no ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
);
GO

GRANT SELECT, INSERT ON [dbo].[LT_PREMIUM_HIST] TO [ImpUsers], [tessitura_app];
GO

/* END INSTALL LT_PREMIUM_HIST */

PRINT 'BEGIN INSTALL/UPDATE LVS_MEMB_LEVEL_UNIQ';
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

/***********************************************************************************************************************
Author:       Nick Reilingh, Fisher Center at Bard College
Description:  This view dedupes membership level codes.

***********************************************************************************************************************/
CREATE OR ALTER VIEW [dbo].[LVS_MEMB_LEVEL_UNIQ]
AS

SELECT DISTINCT
    memb_level
  FROM dbo.VS_MEMB_LEVEL;

GO

GRANT SELECT ON [dbo].[LVS_MEMB_LEVEL_UNIQ] TO [ImpUsers], [tessitura_app];
GO
/* END INSTALL/UPDATE LVS_MEMB_LEVEL_UNIQ */