---

CREATE DATABASE Почта;
GO
USE Почта;
GO

CREATE TABLE Почтовые_отделения
(
    Код_почтового_отделения int IDENTITY(1,1) PRIMARY KEY,
    Адрес                   nvarchar(200) NOT NULL,
    Город                   nvarchar(100) NOT NULL,
    Район                   nvarchar(100) NOT NULL,
    Индекс                  nvarchar(10)  NOT NULL,
    Телефон                 nvarchar(20)  NULL
);
GO

CREATE TABLE Прием_корреспонденции
(
    Код_приема               int IDENTITY(1,1) PRIMARY KEY,
    Код_почтового_отделения  int           NOT NULL,
    Трек_номер                nvarchar(20)  NOT NULL,
    Дата_и_время_приема       datetime      NOT NULL,
    Объявленная_ценность      decimal(10,2) NULL,
    Итоговая_стоимость_отправки decimal(10,2) NULL,
    Оплачено                 bit           NOT NULL DEFAULT 0,
    Дата_и_время_отправки     datetime      NULL,

    CONSTRAINT FK_Прием_Отделение FOREIGN KEY (Код_почтового_отделения)
        REFERENCES Почтовые_отделения (Код_почтового_отделения)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
GO

CREATE TABLE Списание_корреспонденции
(
    Код_списания                int IDENTITY(1,1) PRIMARY KEY,
    Трек_номер                   nvarchar(20) NOT NULL,
    Корреспонденция_утилизирована bit         NOT NULL DEFAULT 0
);
GO

ALTER TABLE Списание_корреспонденции
ADD CONSTRAINT FK_Списание_Трек FOREIGN KEY (Трек_номер)
    REFERENCES Прием_корреспонденции (Трек_номер);
GO

INSERT INTO Почтовые_отделения (Адрес, Город, Район, Индекс, Телефон) VALUES
('ул. Ленина, 1',   'Москва', 'Центральный', '101000', '+7 495 200-00-01'),
('ул. Мира, 15',    'Москва', 'Северный',    '101001', '+7 495 200-00-02'),
('пр-т Гагарина, 8','Москва', 'Южный',       '101002', '+7 495 200-00-03');
GO

INSERT INTO Прием_корреспонденции (Код_почтового_отделения, Трек_номер, Дата_и_время_приема, Объявленная_ценность, Итоговая_стоимость_отправки, Оплачено, Дата_и_время_отправки) VALUES
(1, 'TN-0001', '2026-01-05 09:00', 500,  120, 1, NULL),
(1, 'TN-0001', '2026-01-12 10:15', 500,  120, 1, NULL),
(1, 'TN-0001', '2026-01-20 11:30', 500,  120, 1, NULL),
(1, 'TN-0002', '2026-01-06 08:20', 1000, 200, 1, '2026-01-07 09:00'),
(2, 'TN-0003', '2026-01-07 12:00', 300,  90,  1, '2026-01-08 10:00'),
(2, 'TN-0004', '2026-01-08 14:10', 700,  150, 1, NULL),
(2, 'TN-0004', '2026-01-15 09:45', 700,  150, 1, NULL),
(3, 'TN-0005', '2026-01-09 16:30', 450,  110, 1, '2026-01-10 08:00'),
(3, 'TN-0006', '2026-01-10 10:00', 600,  130, 1, NULL),
(3, 'TN-0007', '2026-01-11 11:20', 250,  80,  1, '2026-01-12 09:30');
GO

INSERT INTO Списание_корреспонденции (Трек_номер, Корреспонденция_утилизирована)
VALUES ('TN-0001', 1);
GO

CREATE VIEW vw_Отчет_Списание AS
SELECT
    po.Код_почтового_отделения,
    po.Адрес                                           AS Адрес_отделения,
    pk.Трек_номер,
    pk.Дата_и_время_приема,
    pk.Дата_и_время_отправки,
    (SELECT COUNT(*)
     FROM Прием_корреспонденции pk2
     WHERE pk2.Код_почтового_отделения = pk.Код_почтового_отделения
       AND pk2.Трек_номер = pk.Трек_номер
       AND pk2.Дата_и_время_отправки IS NULL)          AS Количество_приемов_без_отправки,
    ISNULL(sk.Корреспонденция_утилизирована, 0)         AS Утилизирована
FROM Прием_корреспонденции pk
JOIN Почтовые_отделения po ON po.Код_почтового_отделения = pk.Код_почтового_отделения
LEFT JOIN Списание_корреспонденции sk ON sk.Трек_номер = pk.Трек_номер;
GO