-- Create and use database
DROP DATABASE IF EXISTS u156403995_pocketwoke;
CREATE DATABASE u156403995_pocketwoke;
USE u156403995_pocketwoke;

-- Core configuration table for global assumptions
CREATE TABLE assumptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    house_appreciation_rate DECIMAL(4,2),
    vanguard_return_rate DECIMAL(4,2),
    monthly_investment DECIMAL(10,2),
    additional_investment DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Store personal financial situation
CREATE TABLE household_finances (
    id INT PRIMARY KEY AUTO_INCREMENT,
    applicant1_salary DECIMAL(10,2),
    applicant2_salary DECIMAL(10,2),
    monthly_income DECIMAL(10,2),
    monthly_outgoings DECIMAL(10,2),
    current_savings DECIMAL(10,2),
    total_annual_salaries DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Store different mortgage scenarios
CREATE TABLE scenarios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    property_price DECIMAL(10,2),
    ownership_percentage DECIMAL(5,2),
    interest_rate DECIMAL(4,2),
    mortgage_term_years INT,
    monthly_mortgage DECIMAL(10,2),
    lq_rent DECIMAL(10,2),
    service_charge DECIMAL(10,2),
    total_monthly_payment DECIMAL(10,2),
    remaining_savings DECIMAL(10,2),
    interest_only_monthly DECIMAL(10,2),
    assumption_id INT,
    FOREIGN KEY (assumption_id) REFERENCES assumptions(id)
);

-- Store financial projections for each scenario
CREATE TABLE scenario_projections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scenario_id INT,
    current_full_price DECIMAL(12,2),
    future_full_price DECIMAL(12,2),
    actual_shares_price DECIMAL(12,2),
    future_shares_price DECIMAL(12,2),
    difference_to_full_house DECIMAL(12,2),
    vanguard_monthly_savings DECIMAL(10,2),
    vanguard_savings_period DECIMAL(12,2),
    vanguard_additional_savings DECIMAL(12,2),
    total_vanguard_cash DECIMAL(12,2),
    interest_only_savings DECIMAL(12,2),
    rent_money_lq DECIMAL(12,2),
    interest_paid DECIMAL(12,2),
    total_burned_money DECIMAL(12,2),
    total_wealth DECIMAL(12,2),
    cash_remaining_full_house DECIMAL(12,2),
    FOREIGN KEY (scenario_id) REFERENCES scenarios(id)
);

-- Store transaction costs for different actions
CREATE TABLE transaction_costs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    valuation_fees DECIMAL(10,2),
    legal_fees DECIMAL(10,2),
    housing_association_fees DECIMAL(10,2),
    land_registry_fee DECIMAL(10,2),
    new_mortgage_moving_fees DECIMAL(10,2),
    new_mortgage_legal_fees DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Store bank lending criteria
CREATE TABLE lending_criteria (
    id INT PRIMARY KEY AUTO_INCREMENT,
    min_deposit_full_ownership DECIMAL(4,2),
    min_deposit_shared_ownership DECIMAL(4,2),
    affordability_multiplier DECIMAL(4,2),
    max_affordable_amount DECIMAL(12,2),
    max_affordable_with_outgoings DECIMAL(12,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Populate initial data
-- Assumptions
INSERT INTO assumptions (house_appreciation_rate, vanguard_return_rate, monthly_investment, additional_investment)
VALUES (4.00, 5.00, 1500.00, 500.00);

-- Household Finances
INSERT INTO household_finances (
    applicant1_salary, 
    applicant2_salary, 
    monthly_income, 
    monthly_outgoings, 
    current_savings,
    total_annual_salaries
) VALUES (
    46000.00,
    73000.00,
    6392.00,
    800.00,
    25000.00,
    119000.00
);

-- Transaction Costs
INSERT INTO transaction_costs (
    valuation_fees,
    legal_fees,
    housing_association_fees,
    land_registry_fee,
    new_mortgage_moving_fees,
    new_mortgage_legal_fees
) VALUES (
    400.00,
    2000.00,
    500.00,
    500.00,
    1000.00,
    5100.00
);

-- Lending Criteria
INSERT INTO lending_criteria (
    min_deposit_full_ownership,
    min_deposit_shared_ownership,
    affordability_multiplier,
    max_affordable_amount,
    max_affordable_with_outgoings
) VALUES (
    10.00,
    15.00,
    4.90,
    583100.00,
    536060.00
);

-- Scenarios
INSERT INTO scenarios (
    name,
    property_price,
    ownership_percentage,
    interest_rate,
    mortgage_term_years,
    monthly_mortgage,
    lq_rent,
    service_charge,
    total_monthly_payment,
    remaining_savings,
    interest_only_monthly,
    assumption_id
) VALUES 
-- Current state
('Current', 525000.00, 25.00, 1.80, 25, 478.00, 1031.00, 212.00, 1864.00, 25000.00, 169.00, 1),
-- Staircase 50%
('Staircase 50%', 550000.00, 50.00, 5.50, 20, 1053.00, 688.00, 212.00, 2095.00, 21600.00, 383.00, 1),
-- Staircase 75%
('Staircase 75%', 550000.00, 75.00, 5.50, 20, 1999.00, 344.00, 212.00, 2698.00, 21600.00, 1013.00, 1),
-- Full Purchase
('New Purchase', 525000.00, 100.00, 5.50, 20, 3240.00, 0.00, 0.00, 3383.00, 5150.00, 2159.00, 1);

-- Scenario Projections (example for current state)
INSERT INTO scenario_projections (
    scenario_id,
    current_full_price,
    future_full_price,
    actual_shares_price,
    future_shares_price,
    difference_to_full_house,
    vanguard_monthly_savings,
    vanguard_savings_period,
    vanguard_additional_savings,
    total_vanguard_cash,
    interest_only_savings,
    rent_money_lq,
    interest_paid,
    total_burned_money,
    total_wealth,
    cash_remaining_full_house
) VALUES 
-- Current state
(1, 550000.00, 1205118.00, 137500.00, 301279.00, -903838.00, 1500.00, 616551.00, 205517.00, 822067.00, 127177.00, -372900.00, -27302.00, -400202.00, 1123347.00, 219508.00),
-- Staircase 50%
(2, 550000.00, 1205118.00, 275000.00, 602559.00, -602559.00, 1269.00, 521526.00, 205517.00, 727043.00, 275404.00, -215880.00, -54381.00, -270261.00, 1329602.00, 727043.00),
-- Staircase 75%
(3, 550000.00, 1205118.00, 412500.00, 903838.00, -301279.00, 667.00, 274044.00, 205517.00, 479561.00, 405141.00, -133380.00, -143884.00, -277264.00, 1383400.00, 1082120.00),
-- Full Purchase
(4, 525000.00, 1150340.00, 525000.00, 1150340.00, 0.00, -19.00, -7809.00, 205517.00, 197708.00, 444450.00, 0.00, -306616.00, -306616.00, 1348048.00, 1348048.00);