SQL For Projeck.sql

create database SmartKharch;
use SmartKharch;

create table Users (
    user_id    int           identity(1,1) primary key,
    name       varchar(120)  not null,
    email      varchar(180)  not null unique,
    password_hash varchar(300) not null,
    currency   varchar(15)   not null default 'PKR',
    created_at datetime      not null default getdate()
);

create table Categories (
    category_id int          identity(1,1) primary key,
    name        varchar(130) not null,
    type        varchar(20)  not null check (type in ('income', 'expense'))
);

create table Transactions (
    transaction_id int           identity(1,1) primary key,
    user_id        int           not null,
    category_id    int           not null,
    amount         decimal(12,2) not null check (amount > 0),
    type           varchar(20)   not null check (type in ('income', 'expense')),
    date           date          not null,
    notes          varchar(400),
    foreign key (user_id)     references Users(user_id),
    foreign key (category_id) references Categories(category_id)
);

create table Budgets (
    budget_id    int           identity(1,1) primary key,
    user_id      int           not null,
    category_id  int           not null,
    month_year   varchar(10)   not null,   -- format: 2025-01
    limit_amount decimal(14,2) not null check (limit_amount > 0),
    foreign key (user_id)     references Users(user_id),
    foreign key (category_id) references Categories(category_id)
);

create table Alerts (
    alert_id     int           identity(1,1) primary key,
    user_id      int           not null,
    category_id  int           not null,
    alert_type   varchar(60)   not null,
    message      varchar(650)  not null,
    triggered_at datetime      not null default getdate(),
    is_read      bit           not null default 0,
    foreign key (user_id)     references Users(user_id),
    foreign key (category_id) references Categories(category_id)
);

create table Monthly_Summary (
    summary_id        int           identity(1,1) primary key,
    user_id           int           not null,
    month_year        varchar(10)   not null,
    total_income      decimal(12,2) not null default 0,
    total_expense     decimal(12,2) not null default 0,
    smartkharch_score int           not null default 0 check (smartkharch_score between 0 and 100),
    foreign key (user_id) references Users(user_id)
);

alter table Users
    add phone varchar(25);

alter table Transactions
    add is_recurring bit not null default 0;

insert into Users (name, email, password_hash, currency)
values
    ('Syed Tahir',  'tahir@gmail.com',  'hashed_pw_1', 'PKR'),
    ('Fatima Sabir','fatima@gmail.com', 'hashed_pw_2', 'PKR'),
    ('Ahmed Khan',  'ahmed@gmail.com',  'hashed_pw_3', 'PKR'),
    ('Eman Iqbal',  'eman@gmail.com',   'hashed_pw_4', 'PKR');

insert into Categories (name, type)
values
    ('Salary',        'income'),
    ('Freelance',     'income'),
    ('Food',          'expense'),
    ('Transport',     'expense'),
    ('Utilities',     'expense'),
    ('Entertainment', 'expense'),
    ('Health',        'expense'),
    ('Education',     'expense');

insert into Transactions (user_id, category_id, amount, type, date, notes)
values
    (1, 1, 50000.00, 'income',  '2025-01-01', 'January salary'),
    (1, 3,  8000.00, 'expense', '2025-01-05', 'Groceries'),
    (1, 4,  3000.00, 'expense', '2025-01-10', 'Fuel and rickshaw'),
    (1, 5,  5000.00, 'expense', '2025-01-15', 'Electricity bill'),
    (1, 6, 12000.00, 'expense', '2025-01-20', 'Dining out -- high this month'),
    (1, 3,  9500.00, 'expense', '2025-02-05', 'Groceries February'),
    (1, 4,  2800.00, 'expense', '2025-02-10', 'Transport'),
    (1, 6,  4000.00, 'expense', '2025-02-18', 'Movies and food'),
    (1, 3, 15000.00, 'expense', '2025-03-05', 'Groceries -- anomaly spike'),
    (1, 6,  3500.00, 'expense', '2025-03-15', 'Entertainment'),
    (1, 1, 50000.00, 'income',  '2025-02-01', 'February salary'),
    (1, 1, 50000.00, 'income',  '2025-03-01', 'March salary');

insert into Transactions (user_id, category_id, amount, type, date, notes)
values
    (2, 1, 45000.00, 'income',  '2025-01-01', 'Salary'),
    (2, 3,  6000.00, 'expense', '2025-01-07', 'Food'),
    (2, 8,  8000.00, 'expense', '2025-01-12', 'Course fee'),
    (2, 7,  3000.00, 'expense', '2025-01-22', 'Doctor visit');

insert into Budgets (user_id, category_id, month_year, limit_amount)
values
    (1, 3, '2025-01', 10000.00),
    (1, 4, '2025-01',  4000.00),
    (1, 5, '2025-01',  6000.00),
    (1, 6, '2025-01',  5000.00),
    (1, 3, '2025-02', 10000.00),
    (1, 6, '2025-02',  5000.00),
    (1, 3, '2025-03', 10000.00);

insert into Alerts (user_id, category_id, alert_type, message)
values
    (1, 6, 'budget_breach',  'entertainment spending exceeded budget limit in january 2025'),
    (1, 3, 'anomaly_spike',  'food spending in march 2025 is 87% higher than your 2-month average'),
    (2, 8, 'budget_breach',  'education spending has no budget set -- consider adding one');

insert into Monthly_Summary (user_id, month_year, total_income, total_expense, smartkharch_score)
values
    (1, '2025-01', 50000.00, 28000.00, 72),
    (1, '2025-02', 50000.00, 16300.00, 88),
    (1, '2025-03', 50000.00, 18500.00, 65),
    (2, '2025-01', 45000.00, 17000.00, 80);

update Users
set phone = '0300-1234567'
where user_id = 1;

update Alerts
set is_read = 1
where alert_id = 1;

update Budgets
set limit_amount = 12000.00
where user_id = 1 and category_id = 3 and month_year = '2025-03';

update Transactions
set amount = 8500.00, notes = 'Groceries (corrected amount)'
where transaction_id = 2;

delete from Alerts
where alert_id = 3;

delete from Alerts
where is_read = 0 and triggered_at < '2025-01-01';

select * from Users;

select t.transaction_id, c.name as category, t.amount, t.date, t.notes
from Transactions t
join Categories c on t.category_id = c.category_id
where t.user_id = 1 and t.type = 'expense'
order by t.date;

select a.alert_id, c.name as category, a.alert_type, a.message, a.triggered_at
from Alerts a
join Categories c on a.category_id = c.category_id
where a.user_id = 1 and a.is_read = 0;

select month_year, total_income, total_expense,
       (total_income - total_expense) as savings,
       smartkharch_score
from Monthly_Summary
where user_id = 1
order by month_year;

select count(user_id) as total_registered_users
from Users;

select user_id, count(transaction_id) as total_transactions
from Transactions
group by user_id;

select type, sum(amount) as total_amount
from Transactions
where user_id = 1
group by type;

select c.name as category, avg(t.amount) as avg_spending
from Transactions t
join Categories c on t.category_id = c.category_id
where t.type = 'expense'
group by c.name
having avg(t.amount) > 5000;

select c.name as category,
       count(t.transaction_id)  as total_transactions,
       sum(t.amount)            as total_spent,
       avg(t.amount)            as avg_per_transaction,
       min(t.amount)            as min_amount,
       max(t.amount)            as max_amount
from Transactions t
join Categories c on t.category_id = c.category_id
where t.user_id = 1 and t.type = 'expense'
group by c.name
order by total_spent desc;

select format(date, 'yyyy-MM') as month,
       sum(amount)             as total_expense
from Transactions
where user_id = 1 and type = 'expense'
group by format(date, 'yyyy-MM')
order by month;

select u.name, count(a.alert_id) as unread_alerts
from Users u
left join Alerts a on u.user_id = a.user_id and a.is_read = 0
group by u.name;

select u.name, avg(ms.smartkharch_score) as avg_score
from Monthly_Summary ms
join Users u on ms.user_id = u.user_id
group by u.name;

select top 1 u.name, c.name as category, t.amount, t.date
from Transactions t
join Users u       on t.user_id = u.user_id
join Categories c  on t.category_id = c.category_id
where t.type = 'expense'
order by t.amount desc;


create function fn_GetMonthlySpending
(
    @user_id   int,
    @month_year varchar(10)   -- format: 2025-01
)
returns decimal(12,2)
as
begin
    declare @total decimal(12,2);

    select @total = sum(amount)
    from Transactions
    where user_id = @user_id
      and type = 'expense'
      and format(date, 'yyyy-MM') = @month_year;

    return isnull(@total, 0);
end;

select dbo.fn_GetMonthlySpending(1, '2025-01') as Jan_Spending;


-- udf 2: calculate smartkharch score for a user in a month
-- score = 100 - penalty for each category that exceeded budget
-- penalty = 10 points per breach (min score = 0)
create function fn_CalcSmartKharchScore
(
    @user_id    int,
    @month_year varchar(10)
)
returns int
as
begin
    declare @breaches int;
    declare @score    int;

    select @breaches = count(*)
    from Budgets b
    where b.user_id = @user_id
      and b.month_year = @month_year
      and b.limit_amount < (
            select isnull(sum(t.amount), 0)
            from Transactions t
            where t.user_id = b.user_id
              and t.category_id = b.category_id
              and t.type = 'expense'
              and format(t.date, 'yyyy-MM') = @month_year
      );

    set @score = 100 - (@breaches * 10);

    return case when @score < 0 then 0 else @score end;
end;

select dbo.fn_CalcSmartKharchScore(1, '2025-01') as Score_Jan;

create function fn_IsAnomaly
(
    @user_id     int,
    @category_id int,
    @month_year  varchar(10)
)
returns bit
as
begin
    declare @current_spend decimal(12,2);
    declare @avg_prev      decimal(12,2);

    select @current_spend = isnull(sum(amount), 0)
    from Transactions
    where user_id = @user_id
      and category_id = @category_id
      and type = 'expense'
      and format(date, 'yyyy-MM') = @month_year;

    -- average of previous 2 months
    select @avg_prev = isnull(avg(monthly_total), 0)
    from (
        select format(date, 'yyyy-MM') as mo, sum(amount) as monthly_total
        from Transactions
        where user_id = @user_id
          and category_id = @category_id
          and type = 'expense'
          and format(date, 'yyyy-MM') < @month_year
        group by format(date, 'yyyy-MM')
    ) as prev_months;

    if @avg_prev > 0 and @current_spend > (@avg_prev * 1.5)
        return 1;

    return 0;
end;

select dbo.fn_IsAnomaly(1, 3, '2025-03') as Is_Food_Anomaly;

create function fn_GetRemainingBudget
(
    @user_id     int,
    @category_id int,
    @month_year  varchar(10)
)
returns decimal(12,2)
as
begin
    declare @limit   decimal(12,2);
    declare @spent   decimal(12,2);

    select @limit = isnull(limit_amount, 0)
    from Budgets
    where user_id = @user_id
      and category_id = @category_id
      and month_year = @month_year;

    select @spent = isnull(sum(amount), 0)
    from Transactions
    where user_id = @user_id
      and category_id = @category_id
      and type = 'expense'
      and format(date, 'yyyy-MM') = @month_year;

    return @limit - @spent;
end;

select dbo.fn_GetRemainingBudget(1, 3, '2025-01') as Remaining_Food_Budget;

create function dbo.fn_GetCompleteTransactionHistory 
(
    @UserId int
)
returns table
as
return
(
    select 
        t.transaction_id,
        c.name as category_name,
        t.amount,
        t.type,
        t.date,
        t.notes
    from 
        Transactions t
    join 
        Categories c on t.category_id = c.category_id
    where 
        t.user_id = @UserId
);

select * from dbo.fn_GetCompleteTransactionHistory(1)
order by date desc;

create view vw_UserMonthlyDashboard as
select 
    u.user_id,
    u.name,
    format(t.date, 'yyyy-MM') as month_year,
    sum(case when t.type = 'income' then t.amount else 0 end) as total_income,
    sum(case when t.type = 'expense' then t.amount else 0 end) as total_expense
from Users u
left join Transactions t on u.user_id = t.user_id
group by u.user_id, u.name, format(t.date, 'yyyy-MM');

create procedure sp_AddTransaction
    @p_user_id int,
    @p_category_id int,
    @p_amount decimal(12,2),
    @p_type varchar(20),
    @p_date date,
    @p_notes varchar(400)
as
begin
    begin try
        insert into Transactions (user_id, category_id, amount, type, date, notes)
        values (@p_user_id, @p_category_id, @p_amount, @p_type, @p_date, @p_notes);
        
        print 'transaction added successfully.';
    end try
    begin catch
        print 'error occurred while adding the transaction.';
        print error_message();
    end catch
end;

create procedure sp_AddTransaction
    @p_user_id int,
    @p_category_id int,
    @p_amount decimal(12,2),
    @p_type varchar(20),
    @p_date date,
    @p_notes varchar(400)
as
begin
    begin try
        insert into Transactions (user_id, category_id, amount, type, date, notes)
        values (@p_user_id, @p_category_id, @p_amount, @p_type, @p_date, @p_notes);
        
        print 'transaction added successfully.';
    end try
    begin catch
        -- return the error so flask can catch it
        declare @ErrorMessage nvarchar(4000) = error_message();
        declare @ErrorSeverity int = error_severity();
        declare @ErrorState int = error_state();

        raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
    end catch
end;

create trigger trg_CheckBudgetBreach
on Transactions
after insert
as
begin
    declare @user_id int, @category_id int, @amount decimal(12,2), @type varchar(20), @tx_date date;
    declare @month_year varchar(10);
    declare @budget_limit decimal(12,2), @total_spent decimal(12,2);

    select @user_id = user_id, @category_id = category_id, @amount = amount, @type = type, @tx_date = date
    from inserted;

    set @month_year = format(@tx_date, 'yyyy-MM');
    if @type = 'expense'
    begin
        select @budget_limit = limit_amount
        from Budgets
        where user_id = @user_id and category_id = @category_id and month_year = @month_year;

        if @budget_limit is not null
        begin
            select @total_spent = sum(amount)
            from Transactions
            where user_id = @user_id and category_id = @category_id 
              and type = 'expense' and format(date, 'yyyy-MM') = @month_year;

            if @total_spent > @budget_limit
            begin
                insert into Alerts (user_id, category_id, alert_type, message)
                values (@user_id, @category_id, 'budget_breach', 
                        'automatic alert: you have exceeded your budget limit for this category!');
            end
        end
    end
end;

alter procedure sp_AddTransaction
    @p_user_id int,
    @p_category_id int,
    @p_amount decimal(12,2),
    @p_type varchar(20),
    @p_date date,
    @p_notes varchar(400),
    @p_is_recurring bit = 0 -- naya parameter (default 0/false)
as
begin
    begin try
        insert into Transactions (user_id, category_id, amount, type, date, notes, is_recurring)
        values (@p_user_id, @p_category_id, @p_amount, @p_type, @p_date, @p_notes, @p_is_recurring);
    end try
    begin catch
        declare @ErrorMessage nvarchar(4000) = error_message();
        declare @ErrorSeverity int = error_severity();
        declare @ErrorState int = error_state();
        raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
    end catch
end;