USE db_loc_exp2;
GO
PRINT 'CREATE SPS 01 START'

---------------------------------------------------------------


---------------------------------------------------------------
-- Create an sp that creates generic expense table

DROP PROCEDURE IF EXISTS sp_expense_01_create_tables
GO

CREATE PROCEDURE sp_expense_01_create_tables 

AS

--BEGIN

PRINT 'SP EXPENSE - CREATE TABLES - START: '

DROP TABLE IF EXISTS stage01_amex;

CREATE TABLE stage01_amex
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'AMEX',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
); 

DROP TABLE IF EXISTS stage02_amex_clean;

CREATE TABLE stage02_amex_clean
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'AMEX',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
); 

DROP TABLE IF EXISTS stage01_lloyds;

CREATE TABLE stage01_lloyds
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'LYDS',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
); 

DROP TABLE IF EXISTS stage02_lloyds_clean;

CREATE TABLE stage02_lloyds_clean
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'AMEX',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
); 

DROP TABLE IF EXISTS stage01_mast;

CREATE TABLE stage01_mast
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'MAST',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
);

DROP TABLE IF EXISTS stage02_mast_clean;

CREATE TABLE stage02_mast_clean
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'MAST',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
); 

DROP TABLE IF EXISTS stage01_wells;

CREATE TABLE stage01_wells
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'WELLS',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
); 

DROP TABLE IF EXISTS stage02_wells_clean;

CREATE TABLE stage02_wells_clean
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'MAST',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
);
DROP TABLE IF EXISTS stage02_master01_combine;

CREATE TABLE stage02_master01_combine
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'AMEX',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
); 

DROP TABLE IF EXISTS stage02_master02_category;

CREATE TABLE stage02_master02_category
(
exp_id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
exp_date DATE,
exp_desc NVARCHAR(200),
exp_amount MONEY,
exp_source NVARCHAR(10) DEFAULT 'AMEX',
exp_notes NVARCHAR(500),
exp_cat NVARCHAR(50) DEFAULT 'NONE',
exp_cat_id INT DEFAULT 1
); 

PRINT 'SP EXPENSE - CREATE TABLE - DONE '
GO

--END
-------------------------------------------------------
---------------------------------------------------------------
-- Create an sp that inserts a new category

DROP PROCEDURE IF EXISTS sp_exp_cat_insert_new_category ;
GO

CREATE PROCEDURE sp_exp_cat_insert_new_category 
@exp_cat_new_key nvarchar(50), 
@exp_cat_new_value nvarchar(50)

AS

DECLARE @next_id INT

SET @next_id = (SELECT MAX(exp_cat_id) FROM  dim_categories) + 1

PRINT @next_id

INSERT INTO dim_categories (exp_cat_id, exp_cat_key, exp_cat_value)
VALUES (@next_id, 'TEST01','TEST VAL 01');

GO