CREATE TABLE CustomerCategory (
                CustomerCategoryCode VARCHAR(100) NOT NULL,
                CustomerGroupDesc VARCHAR(100),
                CONSTRAINT CustomerCategory_pk PRIMARY KEY (CustomerCategoryCode)
);


CREATE TABLE Customer (
                CustomerId INTEGER NOT NULL,
                CustomerName VARCHAR(100),
                Address VARCHAR(100),
                City VARCHAR(100),
                State VARCHAR(100),
                IdentificationType VARCHAR(100),
                Gender CHAR(1) NOT NULL,
                DOB DATE NOT NULL,
                CustomerEmail VARCHAR(100),
                CustomerPhone VARCHAR(100),
                AnnualIncome INTEGER,
                CreditScore INTEGER NOT NULL,
                CustomerCategoryCode VARCHAR(100),
                CONSTRAINT Customer_pk PRIMARY KEY (CustomerId)
);


CREATE TABLE ProductGroup (
                ProductGroupCode VARCHAR(100) NOT NULL,
                ProductGroupDesc VARCHAR(500),
                CONSTRAINT ProductGroup_pk PRIMARY KEY (ProductGroupCode)
);


CREATE TABLE RegionCode (
                RegionCode VARCHAR(100) NOT NULL,
                RegionDesc VARCHAR(100),
                CONSTRAINT RegionCode_pk PRIMARY KEY (RegionCode)
);



-- Inserts for region code
------------------------------------------------

INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('1', 'North America');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('2', 'South America');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('3', 'Europe');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('4', 'Asia');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('5', 'Africa');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('6', 'Australia');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('7', 'Antarctica');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('8', 'Middle East');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('9', 'Caribbean');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('10', 'Central America');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('11', 'Southeast Asia');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('12', 'Eastern Europe');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('13', 'Western Europe');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('14', 'Oceania');
INSERT INTO RegionCode (RegionCode, RegionDesc) VALUES ('15', 'Northern Africa');



CREATE TABLE Zone (
                ZoneCode VARCHAR(100) NOT NULL,
                RegionCode VARCHAR(100),
                ZoneDesc VARCHAR(100),
                CONSTRAINT Zone_pk PRIMARY KEY (ZoneCode)
);


CREATE TABLE AccountType (
                AccountType VARCHAR(10) NOT NULL,
                AccountTypeDesc VARCHAR(100),
                CONSTRAINT AccountType_pk PRIMARY KEY (AccountType)
);


CREATE TABLE TransactionGrouping (
                TransactionGroupCode VARCHAR(100) NOT NULL,
                TransactionGroupDesc VARCHAR(100),
                CONSTRAINT TransactionGrouping_pk PRIMARY KEY (TransactionGroupCode)
);


CREATE TABLE TransactionType (
                TransactionType VARCHAR(100) NOT NULL,
                TransactionGroupCode VARCHAR(100) NOT NULL,
                TransactionTypeDesc VARCHAR(100),
                BankChargeInPct INTEGER NOT NULL,
                CONSTRAINT TransactionType_pk PRIMARY KEY (TransactionType)
);


CREATE TABLE Branch (
                Branch_Id INTEGER NOT NULL,
                IFSCCode VARCHAR(30) NOT NULL,
                BranchName VARCHAR(100),
                Address VARCHAR(100),
                City VARCHAR(100),
                State VARCHAR(100),
                ZoneCode VARCHAR(100),
                CONSTRAINT Branch_pk PRIMARY KEY (Branch_Id)
);


CREATE TABLE AccountCampaign (
                campaignId INTEGER NOT NULL,
                CampaignStartDate DATE NOT NULL,
                campaignEndDate DATE NOT NULL,
                CampaignCost INTEGER NOT NULL,
                Branch_Id INTEGER NOT NULL,
                CONSTRAINT AccountCampaign_pk PRIMARY KEY (campaignId)
);


CREATE TABLE Product (
                ProductId INTEGER NOT NULL,
                ProductCode VARCHAR(100),
                ProductDesc VARCHAR(100),
                ProductGroupCode VARCHAR(100),
                CONSTRAINT Product_pk PRIMARY KEY (ProductId)
);


CREATE TABLE Account (
                AccountId INTEGER NOT NULL,
                Branch_Id INTEGER NOT NULL,
                CustomerId INTEGER,
                AccountType VARCHAR(10),
                ProductId INTEGER,
                AccountOpenDate DATE,
                AccountWithdrawlLimit INTEGER NOT NULL,
                AccountCashLimit INTEGER NOT NULL,
                AccountClosureDate DATE,
                AccountStatus CHAR(1),
                CONSTRAINT Account_pk PRIMARY KEY (AccountId)
);


CREATE TABLE AccountWrittenOff (
                AccountId INTEGER NOT NULL,
                WriteOffDate DATE NOT NULL,
                CONSTRAINT AccountWrittenOff_pk PRIMARY KEY (AccountId)
);


CREATE TABLE AccountTransaction (
                TransactionId INTEGER NOT NULL,
                AccountId INTEGER,
                TransactionType VARCHAR(100),
                TransactionDate DATE NOT NULL,
                DebitCreditFlag CHAR(1),
                TransactionAmount INTEGER,
                TransactionStatus CHAR(1)
);