CampsiteTracking

CREATE TABLE CAMPSITES(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(255) NOT NULL
);

CREATE TABLE RESERVATION(
ID INT NOT NULL IDENTITY(1,1)  PRIMARY KEY,
ID_CAMP INT NOT NULL,
VISITOR_NUMBER INT,
STATUS INT, // 0: Active; 1: Cancelled
CONSTRAINT FK_CAMP FOREIGN KEY (ID_CAMP) REFERENCES CAMPSITES(ID)
);

CREATE TABLE RESERVATION_DATES(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ID_RSV INT NOT NULL,
R_DATE  DATE,
CONSTRAINT FK_CAMP FOREIGN KEY (ID_RSV) REFERENCES CAMPSITES(RESERVATION)
);


CREATE TABLE CAMPSITE_AVAILABLE(
ID INT IDENTITY(1,1)  PRIMARY KEY,
ID_CAMP INT,
AVAIL_DATE DATE,
STATUS INT // 0: Available; 1 : Not Available
);



// I did two different 
ALTER TABLE AVAIL_RESERVATION ADD CONSTRAINT FK_AVAIL_CAMP FOREIGN KEY (ID_CAMP) REFERENCES CAMPSITES(ID);

INSERT INTO CAMPSITES(ID,NAME) VALUES(1,"CAMPSITE 001");
INSERT INTO CAMPSITES(ID,NAME) VALUES(2,"CAMPSITE 002");
INSERT INTO CAMPSITES(ID,NAME) VALUES(3,"CAMPSITE 003");

INSERT INTO RESERVATION( ID_CAMP, VISITOR_NUMBER,STATUS )VALUES(1,3,0);
INSERT INTO RESERVATION( ID_CAMP, VISITOR_NUMBER )VALUES(1,7,0);
INSERT INTO RESERVATION( ID_CAMP, VISITOR_NUMBER )VALUES(1,5,0);
INSERT INTO RESERVATION( ID_CAMP, VISITOR_NUMBER )VALUES(2,9,0);
INSERT INTO RESERVATION( ID_CAMP, VISITOR_NUMBER )VALUES(3,5,0);


INSERT INTO RESERVATION_DATES( ID_RSV, R_DATE )VALUES(1,'2021-04-05');
INSERT INTO RESERVATION_DATES( ID_RSV, R_DATE )VALUES(1,'2021-04-06');
INSERT INTO RESERVATION_DATES( ID_RSV, R_DATE )VALUES(2,'2021-05-01');
INSERT INTO RESERVATION_DATES( ID_RSV, R_DATE )VALUES(2,'2021-05-02');
INSERT INTO RESERVATION_DATES( ID_RSV, R_DATE )VALUES(2,'2021-05-03');
INSERT INTO RESERVATION_DATES( ID_RSV, R_DATE )VALUES(3,'2021-07-06');


INSERT INTO CAMPSITE_AVAILABLE( ID_CAMP, AVAIL_DATE,STATUS)VALUES(1,'2021-01-01', 0);
// ...
INSERT INTO CAMPSITE_AVAILABLE( ID_CAMP, AVAIL_DATE,STATUS)VALUES(1,'2021-12-31', 0);


INSERT INTO CAMPSITE_AVAILABLE( ID_CAMP, AVAIL_DATE,STATUS)VALUES(2,'2021-01-01', 0);
// ...
INSERT INTO CAMPSITE_AVAILABLE( ID_CAMP, AVAIL_DATE,STATUS)VALUES(2,'2021-12-31', 0);


INSERT INTO CAMPSITE_AVAILABLE( ID_CAMP, AVAIL_DATE,STATUS)VALUES(3,'2021-01-01', 0);
// ...
INSERT INTO CAMPSITE_AVAILABLE( ID_CAMP, AVAIL_DATE,STATUS)VALUES(3,'2021-12-31', 0);



CREATE PROCEDURE ADD_RESERVATION(@V_ID_CAMP INT, @V_START_DATE DATE, @V_END_START DATE, @V_VISITORS INT)
AS
  //Assuming we have a procedure to check availabily
  DECLARE @AVAIL BOOLEAN=FALSE
  DECLARE @RSV_ID INT
  
  
  @AVAIL=CHECK_AVAIL(@V_ID_CAMP, @V_START_DATE , @V_END_START )
  
  IF( @AVAIL) 
   BEGIN
     INSERT INTO RESERVATION( ID_CAMP,START_DATE,END_DATE, VISITOR_NUMBER,STATUS ) 
     VALUES( @V_ID_CAMP, @V_START_DATE, @V_END_START, @V_VISITORS, '0' )
     SELECT @RSV_ID=SCOPE_IDENTITY()
     
     INSERT INTO 
     
     //Using Sample as recursive to get all the dates
     INSERT INTO RESERVATION_DATE ( ID_RSV, R_DATE )
  WITH sample AS (
      SELECT @V_START_DATE AS dt
      UNION ALL
      SELECT DATEADD(dd, 1, dt)
        FROM sample s
       WHERE DATEADD(dd, 1, dt) <= @end_date)
SELECT @RSV_ID,s.dt
  FROM sample s
     
   
     UPDATE CAMPSITE_AVAILABLE
     SET STATUS=1
     WHERE ID_CAMP=@V_ID_CAMP
     AND AVAIL_DATE>=@V_START_DATE
     AND AVAIL_DATE<=@V_END_START
   END
  
END;

CREATE PROCEDURE USP_CANCEL_RESERVATION(@V_ID_CAMP INT, @V_START_DATE DATE, @V_END_START DATE)
AS
BEGIN
  UPDATE RESERVATION 
  SET STATUS=1
  WHERE ID_CAMP=@V_ID_CAMP
  AND START_DATE=@V_START_DATE
  AND END_DATE=@V_END_START;

  UPDATE CAMPSITE_AVAILABLE
  SET STATUS=0
  WHERE ID_CAMP=@V_ID_CAMP
  AND AVAIL_DATE>=@V_START_DATE
  AND AVAIL_DATE<=@V_END_START;
     
  
END;

CREATE VIEW AVAILABLE_CAMPSITE_DATES
AS
  SELECT a.ID_CAMP,c.NAME, a.AVAIL_DATE
  FROM CAMPSITE_AVAILABLE a
  JOIN CAMPSITES c
  ON c.ID=a.ID_CAMP
  WHERE STATUS=0;
  
GO



// RETURN  VARCHAR 
CREATE FUNCTION UFN_POPULAR_WEEKDAY
AS
RETURN VARCHAR

DECLARE @POPULAR_DAY VARCHAR






  SELECT @MON_COUNT= COUNT(*)   FROM RESERVATION  WHERE DATEPART(WEEKDAY,
  
 SELECT  @POPULAR_DAY=TOP 1 WDAY
 FROM (SELECT  CASE DATEPART(WEEKDAY,R_DATE)  
              WHEN 1 THEN 'SUNDAY' 
              WHEN 2 THEN 'MONDAY' 
              WHEN 3 THEN 'TUESDAY' 
              WHEN 4 THEN 'WEDNESDAY' 
              WHEN 5 THEN 'THURSDAY' 
              WHEN 6 THEN 'FRIDAY' 
              WHEN 7 THEN 'SATURDAY' 
              END AS WDAY
              , SUM(*) AS CNT
   FROM RESERVATION
   GROUP BY CASE DATEPART(WEEKDAY,R_DATE)  
              WHEN 1 THEN 'SUNDAY' 
              WHEN 2 THEN 'MONDAY' 
              WHEN 3 THEN 'TUESDAY' 
              WHEN 4 THEN 'WEDNESDAY' 
              WHEN 5 THEN 'THURSDAY' 
              WHEN 6 THEN 'FRIDAY' 
              WHEN 7 THEN 'SATURDAY' 
              END AS WDAY
              
              )  AS POPULAR_DAY
      ORDER BY CNT DESC
      
              
   RETURN  @POPULAR_DAY
  
  END;


GO