CREATE TABLE Patients
(
  ID          INT IDENTITY (1, 1) NOT NULL,
  Surname     NVARCHAR(50)        NOT NULL,
  BirthDate   DATE                NOT NULL,
  PhoneNumber NVARCHAR(15),
  PRIMARY KEY (ID)
);


INSERT INTO Patients VALUES ('Замотаев', '1981-12-09', '+78923432');
INSERT INTO Patients VALUES ('Кошкин', '1998-11-07', '+7854432');
INSERT INTO Patients VALUES ('Собачкин', '2001-12-09', '+7812432');
INSERT INTO Patients VALUES ('Дроид', '1961-12-09', '+777732');
INSERT INTO Patients VALUES ('БибиВосемь', '2014-10-03', '+8888');
INSERT INTO Patients VALUES ('КаДевять', '1968-12-09', '+9999');

CREATE TABLE Price
(
  ServiceID INT IDENTITY (1, 1) NOT NULL,
  Name      NVARCHAR(50)        NOT NULL,
  DoctorID  INT                 NOT NULL,
  cost      INT                 NOT NULL,
  PRIMARY KEY (ServiceID)
);

INSERT INTO Price VALUES ('Консультация', 1, 1500);
INSERT INTO Price VALUES ('Консультация', 5, 120000);
INSERT INTO Price VALUES ('Первичный прием', 2, 1100);
INSERT INTO Price VALUES ('Вторичный прием', 3, 5000);
INSERT INTO Price VALUES ('Осмотр', 4, 4500);

CREATE TABLE Payments
(
  PaymentID   INT IDENTITY (1, 1) NOT NULL,
  PatientID   INT                 NOT NULL,
  PriceID     INT                 NOT NULL,
  PaymentDate DATETIME,
  PRIMARY KEY (PaymentID)
);

INSERT INTO Payments VALUES (8, 5, '18-06-12 10:40:09 PM');
INSERT INTO Payments VALUES (8, 1, '18-06-12 10:41:09 PM');
INSERT INTO Payments VALUES (8, 2, '18-06-12 10:42:09 PM');
INSERT INTO Payments VALUES (8, 3, '18-06-12 10:43:09 PM');
INSERT INTO Payments VALUES (8, 4, '18-06-12 10:44:09 PM');
INSERT INTO Payments VALUES (7, 3, '18-06-12 10:44:09 PM');


CREATE TABLE Visits
(
  VisitID      INT IDENTITY (1, 1) NOT NULL,
  PatientID    INT                 NOT NULL,
  DoctorID     INT                 NOT NULL,
  VisitingTime DATETIME            NOT NULL,
  Durability   TIME                NOT NULL,
  PRIMARY KEY (VisitID)
);
INSERT INTO Visits VALUES (8, 6, '18-06-12 10:50:00 PM', '00:40')
INSERT INTO Visits VALUES (8, 1, '18-06-17 10:50:00 PM', '00:40')
INSERT INTO Visits VALUES (1, 2, '18-06-17 10:50:00 PM', '00:40')



CREATE TABLE Departments
(
  ID          INT IDENTITY (1, 1) NOT NULL,
  OpeningTime TIME                NOT NULL,
  ClosingTime TIME                NOT NULL,
  PRIMARY KEY (ID)
);

INSERT INTO Departments VALUES ('08:00', '17:00');
INSERT INTO Departments VALUES ('08:00', '20:00');
INSERT INTO Departments VALUES ('08:00', '19:00');

CREATE TABLE Doctors
(
  ID            INT IDENTITY (1, 1) NOT NULL,
  DepatramentID INT                 NOT NULL,
  Surname       NVARCHAR(50)        NOT NULL,
  Specialty     NVARCHAR(50)        NOT NULL,
  PhoneNumber   NVARCHAR(15),
  PRIMARY KEY (ID)
);

INSERT INTO Doctors VALUES (1, 'Иванова', 'Окулист', '88888');
INSERT INTO Doctors VALUES (1, 'Петрова', 'Окулист', '89023');
INSERT INTO Doctors VALUES (2, 'Лифанов', 'Психотерапевт', '31227');
INSERT INTO Doctors VALUES (3, 'Немцев', 'Стоматолог', '333');
INSERT INTO Doctors VALUES (3, 'Чудакова', 'Хирург', '2394023');
INSERT INTO Doctors VALUES (2, 'Замотаева', 'Окулист', '443572');
INSERT INTO Doctors VALUES (1, 'Уволенный', 'Окулист', '423572');



--------------------------------1-------------------------------------

ALTER FUNCTION GetTimetable (@y INT, @m INT, @d INT, @dID INT)
  RETURNS TABLE
  RETURN SELECT
           v.VisitingTime,
           p.Surname
         FROM Doctors d
           JOIN Visits v ON d.ID = v.DoctorID
           JOIN Patients p ON p.ID = v.PatientID
         WHERE MONTH(v.VisitingTime) = @m AND YEAR(v.VisitingTime) = @y AND DAY(v.VisitingTime) = @d
               AND v.DoctorID = @dID
         GROUP BY v.VisitingTime, p.Surname

SELECT *
FROM GetTimetable(2012, 6, 18, 5)

-------------------------------2--------------------------------------
SELECT
  d.Specialty,
  d.Surname,
  COUNT(d.Surname)
FROM
  Doctors d
  JOIN Visits v ON v.DoctorID = d.ID
GROUP BY d.Specialty, d.Surname

------------------------------3----------------------------------------


SELECT d.Specialty, sum(pr.cost)
FROM Doctors d
  JOIN Price pr ON d.ID = pr.DoctorID
  JOIN Payments p on p.PriceID=pr.ServiceID
GROUP BY d.Specialty
ORDER BY sum(pr.cost) DESC


-------------------------------4--------------------------------------
CREATE TRIGGER Add_visits
  ON Visits
  INSTEAD OF INSERT AS
  BEGIN
   IF EXISTS(SELECT *
             FROM inserted i
               JOIN Visits v ON v.DoctorID = i.DoctorID
             WHERE i.VisitingTime BETWEEN v.VisitingTime
             AND dateadd(MINUTE, DATEPART(MINUTE, v.Durability), dateadd(HOUR, DATEPART(HOUR, v.Durability),
                                                                         v.VisitingTime)))
    BEGIN
      ROLLBACK;
      THROW 50001, 'Cannot delete Sotr', 1;
    END
  ELSE
    BEGIN
      INSERT INTO Visits (PatientID, DoctorID, VisitingTime, Durability)
        SELECT
          l.PatientID,
          l.DoctorID,
          l.VisitingTime,
          l.Durability
        FROM inserted l
    END

END

DROP TRIGGER Add_visits
INSERT INTO Visits (PatientID, DoctorID, VisitingTime, Durability) VALUES (2, 1, '18-06-12 9:40:00 PM', '00:10')
INSERT INTO Visits (PatientID, DoctorID, VisitingTime, Durability) VALUES (2, 2, '18-06-12 9:40:00 PM', '00:10')
INSERT INTO Visits (PatientID, DoctorID, VisitingTime, Durability) VALUES (2, 5, '18-06-12 11:40:00 PM', '00:10')

--------------------------------5-----------------------------------------
ALTER TABLE Doctors
    ADD Архив INT


CREATE TRIGGER DelDoctor
  On Doctors
  INSTEAD OF DELETE AS
  BEGIN
    if exists(SELECT * FROM deleted d
    JOIN Visits v on v.DoctorID=d.ID
    WHERE (SELECT  MAX(vt.VisitingTime) FROM Visits vt
          WHERE v.DoctorID=d.ID) > GETDATE())
      BEGIN
      ROLLBACK;
      THROW 50001, 'Cannot delete doctor', 1;
    END
    ELSE
    BEGIN
      UPDATE Doctors
      SET Doctors.Архив=1 WHERE Doctors.ID in (SELECT ID FROM deleted)
    END
  END

DROP TRIGGER DelDoctor
DELETE FROM Doctors WHERE ID=2