-- Create the Superannuation database
IF DB_ID('Superannuation') IS NOT NULL
    DROP DATABASE Superannuation; -- Drop the database if it already exists
CREATE DATABASE Superannuation;
GO

USE Superannuation;

-- Create the "Customer Data" table
CREATE TABLE [Customer Data] (
    CustomerId INT PRIMARY KEY IDENTITY(1,1), -- Primary key with auto-increment
    Residency NVARCHAR(50),
    MedicalCondition NVARCHAR(100),
    MaritalStatus NVARCHAR(50),
    DOB DATE,
    Gender NVARCHAR(10),
    AccountBalance DECIMAL(18, 2),
    RetirementStatus NVARCHAR(50),
    EmploymentStatus NVARCHAR(50),
    RetirementSavings DECIMAL(18, 2),
    HaveDiscussedInvestmentBefore BIT, -- BIT for Yes/No or True/False
    Dependents INT,
    RecentFinancialEvent NVARCHAR(255),
    ReasonForLeaving NVARCHAR(100), -- Additional column for ReasonForLeaving
    DepartingAustralia BIT, -- Additional column for Departing Australia
    FirstHomeSuperSaverScheme BIT, -- Additional column for First Home Super Saver Scheme
    CustomerFirstName NVARCHAR(50), -- Column for Customer First Name
    CustomerLastName NVARCHAR(50), -- Column for Customer Last Name
    Email NVARCHAR(100) -- Column for Email
);

-- Insert the first dataset
INSERT INTO [Customer Data] (
    Residency,
    MedicalCondition,
    MaritalStatus,
    DOB,
    Gender,
    AccountBalance,
    RetirementStatus,
    EmploymentStatus,
    RetirementSavings,
    HaveDiscussedInvestmentBefore,
    Dependents,
    RecentFinancialEvent,
    ReasonForLeaving,
    DepartingAustralia,
    FirstHomeSuperSaverScheme,
    CustomerFirstName,
    CustomerLastName,
    Email
)
VALUES (
    'Citizen', -- Residency
    'Terminal Medical Condition', -- MedicalCondition
    'Married', -- MaritalStatus
    '1956-01-05', -- DOB (formatted as YYYY-MM-DD)
    'Male', -- Gender
    120000.00, -- AccountBalance
    'Approaching Retirement', -- RetirementStatus
    'Employed', -- EmploymentStatus
    100000.00, -- RetirementSavings
    0, -- HaveDiscussedInvestmentBefore (0 = false, 1 = true)
    3, -- Dependents
    'Inheritance', -- RecentFinancialEvent
    'Retirement Withdrawal', -- ReasonForLeaving
    1, -- DepartingAustralia (1 = true)
    0, -- FirstHomeSuperSaverScheme (0 = false)
    'John', -- CustomerFirstName
    'Smith', -- CustomerLastName
    'arash.aghlara@flexrule.com' -- Email
);

-- Insert the second dataset
INSERT INTO [Customer Data] (
    Residency,
    MedicalCondition,
    MaritalStatus,
    DOB,
    Gender,
    AccountBalance,
    RetirementStatus,
    EmploymentStatus,
    RetirementSavings,
    HaveDiscussedInvestmentBefore,
    Dependents,
    RecentFinancialEvent,
    ReasonForLeaving,
    DepartingAustralia,
    FirstHomeSuperSaverScheme,
    CustomerFirstName,
    CustomerLastName,
    Email
)
VALUES (
    NULL, -- Residency (set to NULL as per the data)
    NULL, -- MedicalCondition (set to NULL as per the data)
    NULL, -- MaritalStatus (set to NULL as per the data)
    '1955-01-01', -- DOB (formatted as YYYY-MM-DD)
    NULL, -- Gender (set to NULL as per the data)
    150000.00, -- AccountBalance
    'Retired', -- RetirementStatus
    'Part Time', -- EmploymentStatus
    0.00, -- RetirementSavings
    0, -- HaveDiscussedInvestmentBefore (0 = false, 1 = true)
    1, -- Dependents
    'Bonus', -- RecentFinancialEvent
    'Retirement Withdrawal', -- ReasonForLeaving
    0, -- DepartingAustralia (0 = false)
    0, -- FirstHomeSuperSaverScheme (0 = false)
    'Emma', -- CustomerFirstName
    'Johnson', -- CustomerLastName
    'arash.aghlara@flexrule.com' -- Email
);

-- Insert the third dataset
INSERT INTO [Customer Data] (
    Residency,
    MedicalCondition,
    MaritalStatus,
    DOB,
    Gender,
    AccountBalance,
    RetirementStatus,
    EmploymentStatus,
    RetirementSavings,
    HaveDiscussedInvestmentBefore,
    Dependents,
    RecentFinancialEvent,
    ReasonForLeaving,
    DepartingAustralia,
    FirstHomeSuperSaverScheme,
    CustomerFirstName,
    CustomerLastName,
    Email
)
VALUES (
    'Citizen', -- Residency
    NULL, -- MedicalCondition (set to NULL as per the data)
    'De Facto', -- MaritalStatus
    '1965-01-01', -- DOB (formatted as YYYY-MM-DD)
    'Female', -- Gender
    75000.00, -- AccountBalance
    'Approaching Retirement', -- RetirementStatus
    'Employed', -- EmploymentStatus
    0.00, -- RetirementSavings
    0, -- HaveDiscussedInvestmentBefore (0 = false, 1 = true)
    3, -- Dependents
    'Bonus', -- RecentFinancialEvent
    'Retirement Withdrawal', -- ReasonForLeaving
    0, -- DepartingAustralia (0 = false)
    1, -- FirstHomeSuperSaverScheme (1 = true)
    'Michael', -- CustomerFirstName
    'Williams', -- CustomerLastName
    'arash.aghlara@flexrule.com' -- Email
);

-- Insert the fourth dataset
INSERT INTO [Customer Data] (
    Residency,
    MedicalCondition,
    MaritalStatus,
    DOB,
    Gender,
    AccountBalance,
    RetirementStatus,
    EmploymentStatus,
    RetirementSavings,
    HaveDiscussedInvestmentBefore,
    Dependents,
    RecentFinancialEvent,
    ReasonForLeaving,
    DepartingAustralia,
    FirstHomeSuperSaverScheme,
    CustomerFirstName,
    CustomerLastName,
    Email
)
VALUES (
    'Citizen', -- Residency
    'Permanent Incapacity', -- MedicalCondition
    'Married', -- MaritalStatus
    '1975-01-01', -- DOB (formatted as YYYY-MM-DD)
    'Male', -- Gender
    175000.00, -- AccountBalance
    'Not Yet Retired', -- RetirementStatus
    'Employed', -- EmploymentStatus
    0.00, -- RetirementSavings
    0, -- HaveDiscussedInvestmentBefore (0 = false, 1 = true)
    3, -- Dependents
    'Bonus', -- RecentFinancialEvent
    'Wanting to Consolidate accounts', -- ReasonForLeaving
    0, -- DepartingAustralia (0 = false)
    1, -- FirstHomeSuperSaverScheme (1 = true)
    'Sophia', -- CustomerFirstName
    'Brown', -- CustomerLastName
    'arash.aghlara@flexrule.com' -- Email
);

-- Insert the fifth dataset (new row)
INSERT INTO [Customer Data] (
    Residency,
    MedicalCondition,
    MaritalStatus,
    DOB,
    Gender,
    AccountBalance,
    RetirementStatus,
    EmploymentStatus,
    RetirementSavings,
    HaveDiscussedInvestmentBefore,
    Dependents,
    RecentFinancialEvent,
    ReasonForLeaving,
    DepartingAustralia,
    FirstHomeSuperSaverScheme,
    CustomerFirstName,
    CustomerLastName,
    Email
)
VALUES (
    NULL, -- Residency
    NULL, -- MedicalCondition
    'Single', -- MaritalStatus
    '1997-01-02', -- DOB (formatted as YYYY-MM-DD)
    'Male', -- Gender
    10000.00, -- AccountBalance
    'Not Yet Retired', -- RetirementStatus
    'Unemployed', -- EmploymentStatus
    0.00, -- RetirementSavings
    0, -- HaveDiscussedInvestmentBefore (0 = false, 1 = true)
    0, -- Dependents
    'Pay Rise', -- RecentFinancialEvent
    NULL, -- ReasonForLeaving (not provided)
    0, -- DepartingAustralia (0 = false)
    0, -- FirstHomeSuperSaverScheme (0 = false)
    'Karl', -- CustomerFirstName
    'Smith', -- CustomerLastName
    'arash.aghlara@flexrule.com' -- Email
);