---

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


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

CREATE TABLE dbo.CarsharingClients33
(
    ClientID INT IDENTITY(1,1) NOT NULL,

    FullName NVARCHAR(150) NOT NULL,
    BirthDate DATE NOT NULL,

    Phone NVARCHAR(20) NOT NULL,
    Email NVARCHAR(100) NOT NULL,

    DriverLicenseNumber NVARCHAR(30) NOT NULL,
    DriverLicenseExpiry DATE NOT NULL,

    RegistrationDate DATETIME2 NOT NULL
        CONSTRAINT DF_CarsharingClients33_Date
        DEFAULT SYSDATETIME(),

    Status NVARCHAR(20) NOT NULL
        CONSTRAINT DF_CarsharingClients33_Status
        DEFAULT N'Активен',

    CONSTRAINT PK_CarsharingClients33
        PRIMARY KEY (ClientID),

    CONSTRAINT UQ_CarsharingClients33_Phone
        UNIQUE (Phone),

    CONSTRAINT UQ_CarsharingClients33_Email
        UNIQUE (Email),

    CONSTRAINT UQ_CarsharingClients33_License
        UNIQUE (DriverLicenseNumber),

    CONSTRAINT CK_CarsharingClients33_Status
        CHECK
        (
            Status IN
            (
                N'Активен',
                N'Заблокирован'
            )
        )
);
GO


/* =========================
   2. КАТЕГОРИИ АВТОМОБИЛЕЙ
   ========================= */

CREATE TABLE dbo.CarsharingCategories33
(
    CategoryID INT IDENTITY(1,1) NOT NULL,
    CategoryName NVARCHAR(50) NOT NULL,
    Description NVARCHAR(250) NULL,

    CONSTRAINT PK_CarsharingCategories33
        PRIMARY KEY (CategoryID),

    CONSTRAINT UQ_CarsharingCategories33_Name
        UNIQUE (CategoryName)
);
GO


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

CREATE TABLE dbo.CarsharingCars33
(
    CarID INT IDENTITY(1,1) NOT NULL,
    CategoryID INT NOT NULL,

    RegistrationNumber NVARCHAR(15) NOT NULL,
    Brand NVARCHAR(50) NOT NULL,
    Model NVARCHAR(50) NOT NULL,
    Color NVARCHAR(30) NULL,
    ProductionYear SMALLINT NOT NULL,

    FuelLevel DECIMAL(5,2) NOT NULL,
    MileageKm DECIMAL(10,2) NOT NULL,

    Latitude DECIMAL(9,6) NULL,
    Longitude DECIMAL(9,6) NULL,

    Status NVARCHAR(30) NOT NULL
        CONSTRAINT DF_CarsharingCars33_Status
        DEFAULT N'Свободен',

    CONSTRAINT PK_CarsharingCars33
        PRIMARY KEY (CarID),

    CONSTRAINT FK_CarsharingCars33_Category
        FOREIGN KEY (CategoryID)
        REFERENCES dbo.CarsharingCategories33(CategoryID),

    CONSTRAINT UQ_CarsharingCars33_RegNumber
        UNIQUE (RegistrationNumber),

    CONSTRAINT CK_CarsharingCars33_Year
        CHECK (ProductionYear BETWEEN 2000 AND 2100),

    CONSTRAINT CK_CarsharingCars33_Fuel
        CHECK (FuelLevel BETWEEN 0 AND 100),

    CONSTRAINT CK_CarsharingCars33_Mileage
        CHECK (MileageKm >= 0),

    CONSTRAINT CK_CarsharingCars33_Status
        CHECK
        (
            Status IN
            (
                N'Свободен',
                N'Забронирован',
                N'В поездке',
                N'Обслуживание',
                N'Недоступен'
            )
        )
);
GO


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

CREATE TABLE dbo.CarsharingTariffs33
(
    TariffID INT IDENTITY(1,1) NOT NULL,

    TariffName NVARCHAR(100) NOT NULL,

    PricePerMinute DECIMAL(10,2) NOT NULL,
    PricePerKm DECIMAL(10,2) NOT NULL,
    WaitingPricePerMinute DECIMAL(10,2) NOT NULL,

    Description NVARCHAR(300) NULL,

    CONSTRAINT PK_CarsharingTariffs33
        PRIMARY KEY (TariffID),

    CONSTRAINT UQ_CarsharingTariffs33_Name
        UNIQUE (TariffName),

    CONSTRAINT CK_CarsharingTariffs33_Prices
        CHECK
        (
            PricePerMinute >= 0
            AND PricePerKm >= 0
            AND WaitingPricePerMinute >= 0
        )
);
GO


/* =========================
   5. ЗОНЫ ПАРКОВКИ
   ========================= */

CREATE TABLE dbo.CarsharingZones33
(
    ZoneID INT IDENTITY(1,1) NOT NULL,

    ZoneName NVARCHAR(100) NOT NULL,
    Description NVARCHAR(300) NULL,

    Latitude DECIMAL(9,6) NULL,
    Longitude DECIMAL(9,6) NULL,

    ZoneType NVARCHAR(30) NOT NULL,

    CONSTRAINT PK_CarsharingZones33
        PRIMARY KEY (ZoneID),

    CONSTRAINT UQ_CarsharingZones33_Name
        UNIQUE (ZoneName),

    CONSTRAINT CK_CarsharingZones33_Type
        CHECK
        (
            ZoneType IN
            (
                N'Парковка',
                N'Зона старта',
                N'Зона завершения',
                N'Универсальная'
            )
        )
);
GO


/* =========================
   6. АРЕНДА / ПОЕЗДКА
   ========================= */

CREATE TABLE dbo.CarsharingRentals33
(
    RentalID INT IDENTITY(1,1) NOT NULL,

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

    StartZoneID INT NULL,
    EndZoneID INT NULL,

    ReservationTime DATETIME2 NOT NULL
        CONSTRAINT DF_CarsharingRentals33_Reservation
        DEFAULT SYSDATETIME(),

    StartTime DATETIME2 NULL,
    EndTime DATETIME2 NULL,

    StartMileageKm DECIMAL(10,2) NULL,
    EndMileageKm DECIMAL(10,2) NULL,

    TotalMinutes INT NULL,
    TotalAmount DECIMAL(10,2) NULL,

    Status NVARCHAR(30) NOT NULL
        CONSTRAINT DF_CarsharingRentals33_Status
        DEFAULT N'Забронирован',

    CONSTRAINT PK_CarsharingRentals33
        PRIMARY KEY (RentalID),

    CONSTRAINT FK_CarsharingRentals33_Client
        FOREIGN KEY (ClientID)
        REFERENCES dbo.CarsharingClients33(ClientID),

    CONSTRAINT FK_CarsharingRentals33_Car
        FOREIGN KEY (CarID)
        REFERENCES dbo.CarsharingCars33(CarID),

    CONSTRAINT FK_CarsharingRentals33_Tariff
        FOREIGN KEY (TariffID)
        REFERENCES dbo.CarsharingTariffs33(TariffID),

    CONSTRAINT FK_CarsharingRentals33_StartZone
        FOREIGN KEY (StartZoneID)
        REFERENCES dbo.CarsharingZones33(ZoneID),

    CONSTRAINT FK_CarsharingRentals33_EndZone
        FOREIGN KEY (EndZoneID)
        REFERENCES dbo.CarsharingZones33(ZoneID),

    CONSTRAINT CK_CarsharingRentals33_Status
        CHECK
        (
            Status IN
            (
                N'Забронирован',
                N'В поездке',
                N'Завершён',
                N'Отменён'
            )
        ),

    CONSTRAINT CK_CarsharingRentals33_Time
        CHECK
        (
            EndTime IS NULL
            OR StartTime IS NULL
            OR EndTime >= StartTime
        ),

    CONSTRAINT CK_CarsharingRentals33_Mileage
        CHECK
        (
            EndMileageKm IS NULL
            OR StartMileageKm IS NULL
            OR EndMileageKm >= StartMileageKm
        ),

    CONSTRAINT CK_CarsharingRentals33_Total
        CHECK
        (
            TotalAmount IS NULL
            OR TotalAmount >= 0
        )
);
GO


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

CREATE TABLE dbo.CarsharingPayments33
(
    PaymentID INT IDENTITY(1,1) NOT NULL,
    RentalID INT NOT NULL,

    Amount DECIMAL(10,2) NOT NULL,

    PaymentDate DATETIME2 NOT NULL
        CONSTRAINT DF_CarsharingPayments33_Date
        DEFAULT SYSDATETIME(),

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

    CONSTRAINT PK_CarsharingPayments33
        PRIMARY KEY (PaymentID),

    CONSTRAINT FK_CarsharingPayments33_Rental
        FOREIGN KEY (RentalID)
        REFERENCES dbo.CarsharingRentals33(RentalID),

    CONSTRAINT CK_CarsharingPayments33_Amount
        CHECK (Amount > 0),

    CONSTRAINT CK_CarsharingPayments33_Method
        CHECK
        (
            PaymentMethod IN
            (
                N'Карта',
                N'Онлайн'
            )
        ),

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


/* =========================
   8. ПОВРЕЖДЕНИЯ АВТОМОБИЛЯ
   ========================= */

CREATE TABLE dbo.CarsharingDamages33
(
    DamageID INT IDENTITY(1,1) NOT NULL,

    CarID INT NOT NULL,
    RentalID INT NULL,

    Description NVARCHAR(500) NOT NULL,

    ReportDate DATETIME2 NOT NULL
        CONSTRAINT DF_CarsharingDamages33_Date
        DEFAULT SYSDATETIME(),

    RepairCost DECIMAL(10,2) NULL,

    Status NVARCHAR(30) NOT NULL
        CONSTRAINT DF_CarsharingDamages33_Status
        DEFAULT N'Обнаружено',

    CONSTRAINT PK_CarsharingDamages33
        PRIMARY KEY (DamageID),

    CONSTRAINT FK_CarsharingDamages33_Car
        FOREIGN KEY (CarID)
        REFERENCES dbo.CarsharingCars33(CarID),

    CONSTRAINT FK_CarsharingDamages33_Rental
        FOREIGN KEY (RentalID)
        REFERENCES dbo.CarsharingRentals33(RentalID),

    CONSTRAINT CK_CarsharingDamages33_Cost
        CHECK
        (
            RepairCost IS NULL
            OR RepairCost >= 0
        ),

    CONSTRAINT CK_CarsharingDamages33_Status
        CHECK
        (
            Status IN
            (
                N'Обнаружено',
                N'На ремонте',
                N'Устранено'
            )
        )
);
GO


/* Индексы */

CREATE INDEX IX_CarsharingRentals33_Client
ON dbo.CarsharingRentals33(ClientID);
GO

CREATE INDEX IX_CarsharingRentals33_Car
ON dbo.CarsharingRentals33(CarID);
GO

CREATE INDEX IX_CarsharingRentals33_Status
ON dbo.CarsharingRentals33(Status);
GO

CREATE INDEX IX_CarsharingCars33_Status
ON dbo.CarsharingCars33(Status);
GO