---

/* =========================================================
   ПРЕДМЕТНАЯ ОБЛАСТЬ: ТАКСИ
   ГРУППА ИС1-33
   ========================================================= */


/* =========================
   1. КЛИЕНТЫ
   ========================= */

CREATE TABLE dbo.TaxiClients33
(
    ClientID INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(150) NOT NULL,
    Phone NVARCHAR(20) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
    RegistrationDate DATETIME2 NOT NULL
        CONSTRAINT DF_TaxiClients33_RegistrationDate
        DEFAULT SYSDATETIME(),

    CONSTRAINT PK_TaxiClients33
        PRIMARY KEY (ClientID),

    CONSTRAINT UQ_TaxiClients33_Phone
        UNIQUE (Phone),

    CONSTRAINT UQ_TaxiClients33_Email
        UNIQUE (Email)
);
GO


/* =========================
   2. ВОДИТЕЛИ
   ========================= */

CREATE TABLE dbo.TaxiDrivers33
(
    DriverID INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(150) NOT NULL,
    Phone NVARCHAR(20) NOT NULL,
    LicenseNumber NVARCHAR(30) NOT NULL,
    HireDate DATE NOT NULL,
    Rating DECIMAL(3,2) NOT NULL
        CONSTRAINT DF_TaxiDrivers33_Rating DEFAULT 5.00,
    Status NVARCHAR(20) NOT NULL
        CONSTRAINT DF_TaxiDrivers33_Status DEFAULT N'Свободен',

    CONSTRAINT PK_TaxiDrivers33
        PRIMARY KEY (DriverID),

    CONSTRAINT UQ_TaxiDrivers33_Phone
        UNIQUE (Phone),

    CONSTRAINT UQ_TaxiDrivers33_License
        UNIQUE (LicenseNumber),

    CONSTRAINT CK_TaxiDrivers33_Rating
        CHECK (Rating BETWEEN 0 AND 5),

    CONSTRAINT CK_TaxiDrivers33_Status
        CHECK
        (
            Status IN
            (
                N'Свободен',
                N'На заказе',
                N'Не работает'
            )
        )
);
GO


/* =========================
   3. АВТОМОБИЛИ ТАКСИ
   ========================= */

CREATE TABLE dbo.TaxiCars33
(
    CarID INT IDENTITY(1,1) NOT NULL,
    RegistrationNumber NVARCHAR(15) NOT NULL,
    Brand NVARCHAR(50) NOT NULL,
    Model NVARCHAR(50) NOT NULL,
    Color NVARCHAR(30) NOT NULL,
    ProductionYear SMALLINT NOT NULL,
    CarClass NVARCHAR(30) NOT NULL,
    Status NVARCHAR(20) NOT NULL
        CONSTRAINT DF_TaxiCars33_Status DEFAULT N'Свободен',

    CONSTRAINT PK_TaxiCars33
        PRIMARY KEY (CarID),

    CONSTRAINT UQ_TaxiCars33_RegNumber
        UNIQUE (RegistrationNumber),

    CONSTRAINT CK_TaxiCars33_Year
        CHECK (ProductionYear BETWEEN 1990 AND 2100),

    CONSTRAINT CK_TaxiCars33_Class
        CHECK
        (
            CarClass IN
            (
                N'Эконом',
                N'Комфорт',
                N'Бизнес'
            )
        ),

    CONSTRAINT CK_TaxiCars33_Status
        CHECK
        (
            Status IN
            (
                N'Свободен',
                N'На заказе',
                N'Ремонт'
            )
        )
);
GO


/* =========================
   4. ТАРИФЫ
   ========================= */

CREATE TABLE dbo.TaxiTariffs33
(
    TariffID INT IDENTITY(1,1) NOT NULL,
    TariffName NVARCHAR(50) NOT NULL,
    BasePrice DECIMAL(10,2) NOT NULL,
    PricePerKm DECIMAL(10,2) NOT NULL,
    PricePerMinute DECIMAL(10,2) NOT NULL,

    CONSTRAINT PK_TaxiTariffs33
        PRIMARY KEY (TariffID),

    CONSTRAINT UQ_TaxiTariffs33_Name
        UNIQUE (TariffName),

    CONSTRAINT CK_TaxiTariffs33_Prices
        CHECK
        (
            BasePrice >= 0
            AND PricePerKm >= 0
            AND PricePerMinute >= 0
        )
);
GO


/* =========================
   5. ЗАКАЗЫ / ПОЕЗДКИ
   ========================= */

CREATE TABLE dbo.TaxiOrders33
(
    OrderID INT IDENTITY(1,1) NOT NULL,

    ClientID INT NOT NULL,
    DriverID INT NULL,
    CarID INT NULL,
    TariffID INT NOT NULL,

    OrderDate DATETIME2 NOT NULL
        CONSTRAINT DF_TaxiOrders33_OrderDate
        DEFAULT SYSDATETIME(),

    PickupAddress NVARCHAR(250) NOT NULL,
    DestinationAddress NVARCHAR(250) NOT NULL,

    StartTime DATETIME2 NULL,
    EndTime DATETIME2 NULL,

    DistanceKm DECIMAL(8,2) NULL,
    DurationMinutes INT NULL,

    TotalAmount DECIMAL(10,2) NULL,

    Status NVARCHAR(30) NOT NULL
        CONSTRAINT DF_TaxiOrders33_Status
        DEFAULT N'Создан',

    CONSTRAINT PK_TaxiOrders33
        PRIMARY KEY (OrderID),

    CONSTRAINT FK_TaxiOrders33_Client
        FOREIGN KEY (ClientID)
        REFERENCES dbo.TaxiClients33(ClientID),

    CONSTRAINT FK_TaxiOrders33_Driver
        FOREIGN KEY (DriverID)
        REFERENCES dbo.TaxiDrivers33(DriverID),

    CONSTRAINT FK_TaxiOrders33_Car
        FOREIGN KEY (CarID)
        REFERENCES dbo.TaxiCars33(CarID),

    CONSTRAINT FK_TaxiOrders33_Tariff
        FOREIGN KEY (TariffID)
        REFERENCES dbo.TaxiTariffs33(TariffID),

    CONSTRAINT CK_TaxiOrders33_Status
        CHECK
        (
            Status IN
            (
                N'Создан',
                N'Принят',
                N'В пути',
                N'Завершён',
                N'Отменён'
            )
        ),

    CONSTRAINT CK_TaxiOrders33_Distance
        CHECK (DistanceKm IS NULL OR DistanceKm >= 0),

    CONSTRAINT CK_TaxiOrders33_Duration
        CHECK (DurationMinutes IS NULL OR DurationMinutes >= 0),

    CONSTRAINT CK_TaxiOrders33_Amount
        CHECK (TotalAmount IS NULL OR TotalAmount >= 0),

    CONSTRAINT CK_TaxiOrders33_Time
        CHECK
        (
            EndTime IS NULL
            OR StartTime IS NULL
            OR EndTime >= StartTime
        )
);
GO


/* =========================
   6. ПЛАТЕЖИ
   ========================= */

CREATE TABLE dbo.TaxiPayments33
(
    PaymentID INT IDENTITY(1,1) NOT NULL,
    OrderID INT NOT NULL,

    Amount DECIMAL(10,2) NOT NULL,
    PaymentDate DATETIME2 NOT NULL
        CONSTRAINT DF_TaxiPayments33_Date
        DEFAULT SYSDATETIME(),

    PaymentMethod NVARCHAR(30) NOT NULL,
    Status NVARCHAR(20) NOT NULL,

    CONSTRAINT PK_TaxiPayments33
        PRIMARY KEY (PaymentID),

    CONSTRAINT FK_TaxiPayments33_Order
        FOREIGN KEY (OrderID)
        REFERENCES dbo.TaxiOrders33(OrderID),

    CONSTRAINT CK_TaxiPayments33_Amount
        CHECK (Amount > 0),

    CONSTRAINT CK_TaxiPayments33_Method
        CHECK
        (
            PaymentMethod IN
            (
                N'Наличные',
                N'Карта',
                N'Онлайн'
            )
        ),

    CONSTRAINT CK_TaxiPayments33_Status
        CHECK
        (
            Status IN
            (
                N'Ожидает',
                N'Оплачен',
                N'Возврат'
            )
        )
);
GO


/* =========================
   7. ОТЗЫВЫ
   ========================= */

CREATE TABLE dbo.TaxiReviews33
(
    ReviewID INT IDENTITY(1,1) NOT NULL,
    OrderID INT NOT NULL,
    Rating TINYINT NOT NULL,
    Comment NVARCHAR(500) NULL,
    ReviewDate DATETIME2 NOT NULL
        CONSTRAINT DF_TaxiReviews33_Date
        DEFAULT SYSDATETIME(),

    CONSTRAINT PK_TaxiReviews33
        PRIMARY KEY (ReviewID),

    CONSTRAINT UQ_TaxiReviews33_Order
        UNIQUE (OrderID),

    CONSTRAINT FK_TaxiReviews33_Order
        FOREIGN KEY (OrderID)
        REFERENCES dbo.TaxiOrders33(OrderID),

    CONSTRAINT CK_TaxiReviews33_Rating
        CHECK (Rating BETWEEN 1 AND 5)
);
GO


/* Индексы */

CREATE INDEX IX_TaxiOrders33_Client
ON dbo.TaxiOrders33(ClientID);
GO

CREATE INDEX IX_TaxiOrders33_Driver
ON dbo.TaxiOrders33(DriverID);
GO

CREATE INDEX IX_TaxiOrders33_Status
ON dbo.TaxiOrders33(Status);
GO