Create Table PremiumDuePdf (
  PremiumDuePdfInternalNo int not null Primary Key,
  Date DateTime not null,
  AgencyInternalNo int not null foreign key references Agency(AgencyInternalNo),
  EmailMessageId int NULL
);
Create Unique Index AK1PremiumDuePdf On PremiumDuePdf (AgencyInternalNo, Date);
CREATE INDEX IX_PremiumDuePdf_EmailMessageId ON PremiumDuePdf(EmailMessageId);
CREATE TABLE PremiumDuePdfDetail
(
    PremiumDuePdfDetailInternalNo INT NOT NULL PRIMARY KEY,
    PremiumDuePdfInternalNo INT NOT NULL,
    PolicyNo VARCHAR(20) NOT NULL,
    CommencementDate DATETIME NULL,
    HolderName VARCHAR(50) NULL,
    PlanNo VARCHAR(5) NULL,
    Term SMALLINT NULL,
    Mode CHAR(1) NULL,
    FUP DATETIME NULL,
    Premium MONEY NULL,

    CONSTRAINT FK_PremiumDuePdfDetail_PremiumDuePdf
        FOREIGN KEY (PremiumDuePdfInternalNo)
        REFERENCES PremiumDuePdf(PremiumDuePdfInternalNo) ON DELETE CASCADE,

    CONSTRAINT CK_PremiumDuePdfDetail_Mode
        CHECK (Mode IN ('P', 'S', 'M', 'Q', 'H', 'Y', 'E'))
);

Insert into IdGenerator values ('PremiumDuePdf', 1)
Insert into IdGenerator values ('PremiumDuePdfDetail', 1)

Update InsurancePlan Set WithProfit = 0, NearBirthDay = 0, MinVestingAge = 50 Where PlanInternalNo = 97;
insert into Mode (ModeInternalNo, PlanInternalNo, Mode) Values (971, 97, 'P')
Insert into AnnuityOption Values (97, 149, 'Return of Purchase Price', 1)

Alter Table Commission Add EmailMessageId int null
CREATE INDEX IX_Commission_EmailMessageId ON Commission(EmailMessageId);

Update InsurancePlan Set ExpiryDate = 'Jan 12, 2026' where PlanInternalNo = 768
