schema.sql

use Billing;

CREATE TABLE User_table(
    userid INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(30),
    user_password VARCHAR(60),
    email VARCHAR(40),
    phone VARCHAR(15),
    user_address VARCHAR(60),
    user_type VARCHAR(10),

	CONSTRAINT unique_username UNIQUE (username),
	CONSTRAINT unique_email UNIQUE (email),
	CONSTRAINT unique_phone UNIQUE (phone)
);

create table customer(
	customer_id int primary key,
	gender char(1),
	salary int,

	constraint customer_fk foreign key (customer_id)
	references User_table(userid)
);

create table company(
	company_id int primary key,
	com_type varchar(15),
	registration_number varchar(15),

	constraint company_fk foreign key(company_id)
	references User_table(userid)

);
create table bill(
    bill_id INT IDENTITY(1,1) PRIMARY KEY,
    bill_amount DECIMAL(10,2),
    bill_status VARCHAR(10) DEFAULT 'unpaid',
    created_at DATETIME DEFAULT GETDATE(),
    paid_at DATETIME NULL,
    customer_id int,
    company_id int,

    constraint bill_customer_fk foreign key (customer_id)
    references customer(customer_id),

    constraint bill_company_fk foreign key (company_id)
    references company(company_id)
);


-- Drop all data + reset IDs
DELETE FROM bill;
DELETE FROM customer;
DELETE FROM company;
DELETE FROM User_table;
DBCC CHECKIDENT ('User_table', RESEED, 0);
DBCC CHECKIDENT ('bill', RESEED, 0);
GO