/* Staff table */
CREATE TABLE Staff
(
StaffID smallint,
StaffName VarChar(70) NOT NULL,
StaffContactNo Char(8) NOT NULL,
StaffDateJoined smalldatetime NOT NULL,
StaffRole VarChar(50) NOT NULL,
CONSTRAINT PK_Staff PRIMARY KEY (StaffID)
);

/*Condo table*/
CREATE TABLE Condo
(
CondoID smallint,
CondoName varchar(50) NOT NULL,
CondoAddress varchar(150) NOT NULL,
CONSTRAINT PK_Condo PRIMARY KEY (CondoID)
);

/*Account table*/
CREATE TABLE Account
(
AccID Char(10),
AccName VarChar(70) NOT NULL,
AccAddress VarChar(150) NULL,
AccCtcNo Char(8) NULL,
AccEmail VarChar(50) NULL,
CondoID smallint NOT NULL,
ApprovedBy smallint NOT NULL,
CONSTRAINT PK_Acc PRIMARY KEY (AccID),
CONSTRAINT FK_Acc_Condo FOREIGN KEY (CondoID) REFERENCES Condo(CondoID),
CONSTRAINT FK_Acc_Staff FOREIGN KEY (ApprovedBy) REFERENCES Staff(StaffID)
);

/*CondoMgmt table*/
CREATE TABLE CondoMgmt
(
CondoMgmtID Char(10),
ContactPerson VarChar(70) NOT NULL,
CtcPersonMobile  Char(8) NOT NULL,
CONSTRAINT PK_CondoMgmt PRIMARY KEY (CondoMgmtID),
CONSTRAINT FK_CondoMgmt_AccID FOREIGN KEY (CondoMgmtID) REFERENCES Account(AccID)
);

/*Announcement table*/
CREATE TABLE Announcement
(
AnnID smallint,
AnnText VarChar(200) NOT NULL,
AnnStartDate smalldatetime NOT NULL,
AnnEndDate smalldatetime NULL,
CondoMgmtID Char(10) NOT NULL,
CONSTRAINT PK_Announcement PRIMARY KEY (AnnID),
CONSTRAINT FK_Announcement FOREIGN KEY (CondoMgmtID) REFERENCES CondoMgmt(CondoMgmtID)
);


/*Message table*/
CREATE TABLE Message
(
MsgID smallint,
MsgText VarChar(200) NOT NULL,
MsgType VarChar(50) NOT NULL,
PostedBy Char(10) NOT NULL,
ReplyTo smallint NULL,
CONSTRAINT PK_Message PRIMARY KEY (MsgID),
CONSTRAINT FK_Message_PostedBy FOREIGN KEY (PostedBy) REFERENCES Account(AccID),
CONSTRAINT FK_Message_ReplyTo FOREIGN KEY (ReplyTo) REFERENCES Message(MsgID),
CONSTRAINT CHK_Message CHECK (MsgType IN ('Chatterbox', 'Garage Sale', 'Find a Buddy'))
);

/*ContactCat table*/
CREATE TABLE ContactCat
(
CtcCatID smallint,
CtcCatDesc varchar(100) NULL,
CONSTRAINT PK_ContactCat PRIMARY KEY (CtcCatID)
);

/*Facility table*/
CREATE TABLE Facility
(
FacID smallint,
FacName varchar(50) NOT NULL,
Deposit smallmoney NULL,
CondoID smallint NOT NULL,
CONSTRAINT PK_Facility PRIMARY KEY (FacID),
CONSTRAINT FK_Facility_CondoID FOREIGN KEY (CondoID) REFERENCES Condo(CondoID)
);

/*FacTimeSlot table*/
CREATE TABLE FacTimeSlot
(
FacID smallint,
TimeSlotSN smallint,
SlotDesc VarChar(100) NOT NULL,
CONSTRAINT PK_FacTimeSlot PRIMARY KEY (FacID,TimeSlotSN),
CONSTRAINT FK_FacTimeSlot_FacID FOREIGN KEY (FacID) REFERENCES Facility(FacID)
);


/*FeedbkCat table*/
CREATE TABLE FeedbkCat
(
FbkCatID smallint,
FbkCatDesc varchar(100) NOT NULL,
CONSTRAINT PK_FeedbkCat PRIMARY KEY (FbkCatID)
);

/* Feedback table */
CREATE TABLE Feedback
(
FbkID smallint,
FbkDesc VarChar(100) NOT NULL,
FbkDateTime smalldatetime NOT NULL,
FbkStatus VarChar(20) NOT NULL,
ByAccID Char(10) NOT NULL,
FbkCatID smallint NOT NULL,
CondoMgmtID Char(10) NOT NULL,
CONSTRAINT PK_Feedback PRIMARY KEY(FbkID),
CONSTRAINT FK_Feedback_ByAccID FOREIGN KEY(ByAccID) REFERENCES Account(AccID),
CONSTRAINT FK_Feedback_FbkCatID FOREIGN KEY(FbkCatID) REFERENCES FeedbkCat(FbkCatID),
CONSTRAINT FK_Feedback_FbkCatID FOREIGN KEY(CondoMgmtID) REFERENCES CondoMgmt(CondoMgmtID)
);

/*ItemCategory table*/
CREATE TABLE ItemCategory
(
ItemCatID smallint,
ItemCatDesc varchar(100) NOT NULL,
CONSTRAINT PK_ItemCategory PRIMARY KEY (ItemCatID)
);

/*ItemRelated table*/
CREATE TABLE ItemRelated
(
ItemID smallint,
ItemDesc VarChar(100) NULL,
ItemPrice smallmoney NULL,
ItemStatus VarChar(9) NOT NULL,
SaleOrRent Char(4) NOT NULL,
ItemCatID smallint NOT NULL,
CONSTRAINT PK_ItemRelated PRIMARY KEY (ItemID),
CONSTRAINT FK_ItemRelated_ItemID FOREIGN KEY (ItemID) REFERENCES Message(MsgID),
CONSTRAINT CHK_ItemRelated CHECK (ItemStatus IN ('Available', 'Sold', 'Reserved') AND SaleOrRent IN ('Rent','Sale')),
CONSTRAINT FK_ItemRelated_ItemCatID FOREIGN KEY (ItemCatID) REFERENCES ItemCategory(ItemCatID)
);

/*ItemPhoto table*/
CREATE TABLE ItemPhoto
(
ItemID smallint,
Photo varchar(100) NOT NULL,
CONSTRAINT PK_ItemPhoto PRIMARY KEY (ItemID,Photo),
CONSTRAINT FK_ItemPhoto_ItemID FOREIGN KEY (ItemID) REFERENCES ItemRelated(ItemID)
);

/*BookSlot table*/
CREATE TABLE BookSlot
(
FacID smallint,
TimeSlotSN smallint NOT NULL,
SlotDate smalldatetime NOT NULL DEFAULT(GETDATE()),
SlotStatus VarChar(15) NOT NULL,
CONSTRAINT PK_BookSlot PRIMARY KEY(FacID,TimeSlotSN,SlotDate),
CONSTRAINT FK_BookSlot FOREIGN KEY(FacID, TimeSlotSN) REFERENCES FacTimeSlot(FacID, TimeSlotSN),
CONSTRAINT CHK_BookSlot CHECK (SlotStatus in ('Booked', 'Available', 'Maintenance'))
);

/*Likes table*/
CREATE TABLE Likes
(
AccID Char(10),
MessageID smallint,
CONSTRAINT PK_Likes PRIMARY KEY (AccID, MessageID),
CONSTRAINT FK_Likes_AccID FOREIGN KEY (AccID) REFERENCES ACCOUNT(AccID),
CONSTRAINT FK_Likes_MessageID FOREIGN KEY (MessageID) REFERENCES Message(MsgID)
);

/*Owner table*/
CREATE TABLE Owner
(
OwnerID Char(10),
OwnStartDate smalldatetime NOT NULL,
CheckedBy Char(10) NOT NULL,
CONSTRAINT PK_Owner PRIMARY KEY (OwnerID),
CONSTRAINT FK_Owner_OwnerID FOREIGN KEY (OwnerID) REFERENCES Account(AccID),
CONSTRAINT FK_Owner_CheckedBy FOREIGN KEY (CheckedBy) REFERENCES CondoMgmt(CondoMgmtID)
);

/*TempVehLabel table*/
CREATE TABLE TempVehLabel
(
VehLblAppID smallint,
TempStartDate smalldatetime NOT NULL,
TempExpiryDate smalldatetime NOT NULL,
CONSTRAINT PK_TempVehLabel PRIMARY KEY (VehLblAppID)
);

/*Tenant table*/
CREATE TABLE Tenant
(
TenantID Char(10),
ContractStartDate smalldatetime NOT NULL,
ContractEndDate smalldatetime NULL,
VerifiedBy Char(10) NOT NULL,
CONSTRAINT PK_Tenant PRIMARY KEY (TenantID),
CONSTRAINT FK_Tenant_TenantID FOREIGN KEY (TenantID) REFERENCES Account(AccID),
CONSTRAINT FK_Tenant_VerifiedBy FOREIGN KEY (VerifiedBy) REFERENCES CondoMgmt(CondoMgmtID)
);

/* UsefulContact Table */
CREATE TABLE UsefulContact
(
UsefulCtcID smallint,
UsefulCtcName VarChar(50) NOT NULL,
UsefulCtcDesc VarChar(100) NULL,
UsefulCtcPhone Char(8) NOT NULL,
CtcCatID smallint NOT NULL,
CONSTRAINT PK_UsefulContact PRIMARY KEY (UsefulCtcID),
CONSTRAINT FK_UsefulContact_CtcCatID FOREIGN KEY(CtcCatID) REFERENCES ContactCat(CtcCatID)
);

/*CondoUsefulContact table*/
CREATE TABLE CondoUsefulContact
(
CondoID smallint,
UsefulCtcID smallint NOT NULL,
CONSTRAINT PK_CondoUsefulContact PRIMARY KEY (CondoID,UsefulCtcID),
CONSTRAINT FK_CondoUsefulContact_CondoID FOREIGN KEY (CondoID) REFERENCES Condo(CondoID),
CONSTRAINT FK_CondoUsefulContact_UsefulCtcID FOREIGN KEY (UsefulCtcID) REFERENCES UsefulContact(UsefulCtcID)
);

/*Vehicle table*/
CREATE TABLE Vehicle
(
VehicleNo varchar(10),
IUNo char(10) NOT NULL,
Ownership varchar(50) NOT NULL,
Make varchar(50) NOT NULL,
Model varchar(50) NOT NULL,
CONSTRAINT PK_Vehicle PRIMARY KEY (VehicleNo)
);

/*Booking table*/
CREATE TABLE Booking
(
BookingID smallint,
BookingDate smalldatetime NOT NULL,
BookingStatus VarChar(20) NOT NULL,
AccID Char(10) NOT NULL,
FacID smallint NOT NULL,
TimeSlotSN smallint NOT NULL,
SlotDate smalldatetime NOT NULL,
CONSTRAINT PK_Booking PRIMARY KEY(BookingID),
CONSTRAINT FK_Booking_FacIDTimeSlotSNSlotDate FOREIGN KEY (FacID,TimeSlotSN,SlotDate) REFERENCES BookSlot(FacID,TimeSlotSN,SlotDate),
CONSTRAINT FK_Booking_AccID FOREIGN KEY (AccID) REFERENCES Account(AccID)
);

/*VehicleLabel table*/
CREATE TABLE VehicleLabel
(
VehLblAppID smallint,
VehLblStatus Varchar(10) NOT NULL,
VehLblNum Varchar(10) NULL,
VehicleNo Varchar(10) NOT NULL,
AppliedBy Char(10) NOT NULL,
IssuedBy Char(10) NOT NULL,
CONSTRAINT PK_VehicleLabel PRIMARY KEY(VehLblAppID),
CONSTRAINT FK_VehicleLabel_VehicleNo FOREIGN KEY(VehicleNo) REFERENCES Vehicle(VehicleNo),
CONSTRAINT FK_VehicleLabel_AppliedBy FOREIGN KEY(AppliedBy) REFERENCES Account(AccID),
CONSTRAINT FK_VehicleLabel_IssuedBy FOREIGN KEY(IssuedBy) REFERENCES CondoMgmt(CondoMgmtID),
CONSTRAINT CHK_VehicleLabel CHECK (VehLblStatus in ('Pending', 'Approved', 'Rejected'))
);



/*Show the content in all tables*/

DECLARE @table varchar(20); /*Declare a variable It will be used to store the name of the current table in the loop*/

/*Declare the cursor CurrTable, get and assign all the table names*/
/* Since we plan to iterate linearly, FAST_FORWARD is used. It makes the cursor read-only and only move forward. Also, it is better for performance */
DECLARE CurrTable CURSOR FAST_FORWARD FOR SELECT name FROM SYSOBJECTS WHERE xtype = 'U'

/*Activate the cursor and populate it with the data*/
OPEN CurrTable

FETCH NEXT FROM CurrTable INTO @table /* Set table to the first item in the cursor*/

/* Start iterating through the cursor */

WHILE @@FETCH_STATUS = 0 /* if the status of the previous fetch was successful */
BEGIN
    /* Show the content of the table with a select statement*/
    /* EXEC statement executes the statement. It's used for integrating the table variable into it*/
    EXEC('SELECT * FROM ' + @table)

    /* Set table to the next item in the cursor*/
    FETCH NEXT FROM CurrTable INTO @table
END

/* Deactivate the cursor and free it */
CLOSE CurrTable

/* Remove the cursor reference, it is no longer needed */
DEALLOCATE CurrTable