---

USE user284_db;
GO

SET NOCOUNT ON;
GO

/* ===========================================================================
   ШАГ 1. Удаление связей (Foreign Keys) для таблиц из твоего файла
=========================================================================== */
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql
    + N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
    + N'.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
    + N' DROP CONSTRAINT ' + QUOTENAME(fk.name) + N';' + CHAR(10)
FROM sys.foreign_keys AS fk
WHERE OBJECT_NAME(fk.parent_object_id) IN (
    N'AirportCities44', N'AirportTerminals44', N'AirportGates44', N'AirportCarriers44',
    N'AirportFlights44', N'AirportPassengers44', N'AirportBookings44',
    N'TaxiCustomers44', N'TaxiDrivers44', N'TaxiVehicleTypes44', N'TaxiVehicles44',
    N'TaxiShifts44', N'TaxiRides44', N'TaxiRidePayments44',
    N'GalleryAuthors44', N'GalleryStyles44', N'GalleryRooms44', N'GalleryShows44',
    N'GalleryExhibits44', N'GalleryGuests44', N'GalleryEntries44'
    )
   OR OBJECT_NAME(fk.referenced_object_id) IN (
    N'AirportCities44', N'AirportTerminals44', N'AirportGates44', N'AirportCarriers44',
    N'AirportFlights44', N'AirportPassengers44', N'AirportBookings44',
    N'TaxiCustomers44', N'TaxiDrivers44', N'TaxiVehicleTypes44', N'TaxiVehicles44',
    N'TaxiShifts44', N'TaxiRides44', N'TaxiRidePayments44',
    N'GalleryAuthors44', N'GalleryStyles44', N'GalleryRooms44', N'GalleryShows44',
    N'GalleryExhibits44', N'GalleryGuests44', N'GalleryEntries44'
    );

IF LEN(@sql) > 0 EXEC sp_executesql @sql;
GO

/* ===========================================================================
   ШАГ 2. Удаление таблиц (всех 21 из твоего файла)
=========================================================================== */
-- Галерея
IF OBJECT_ID(N'GalleryEntries44', N'U') IS NOT NULL DROP TABLE GalleryEntries44;
IF OBJECT_ID(N'GalleryGuests44', N'U') IS NOT NULL DROP TABLE GalleryGuests44;
IF OBJECT_ID(N'GalleryExhibits44', N'U') IS NOT NULL DROP TABLE GalleryExhibits44;
IF OBJECT_ID(N'GalleryShows44', N'U') IS NOT NULL DROP TABLE GalleryShows44;
IF OBJECT_ID(N'GalleryRooms44', N'U') IS NOT NULL DROP TABLE GalleryRooms44;
IF OBJECT_ID(N'GalleryStyles44', N'U') IS NOT NULL DROP TABLE GalleryStyles44;
IF OBJECT_ID(N'GalleryAuthors44', N'U') IS NOT NULL DROP TABLE GalleryAuthors44;

-- Такси (из файла друга - удаляем, чтобы не мешало, твое останется)
IF OBJECT_ID(N'TaxiRidePayments44', N'U') IS NOT NULL DROP TABLE TaxiRidePayments44;
IF OBJECT_ID(N'TaxiRides44', N'U') IS NOT NULL DROP TABLE TaxiRides44;
IF OBJECT_ID(N'TaxiShifts44', N'U') IS NOT NULL DROP TABLE TaxiShifts44;
IF OBJECT_ID(N'TaxiVehicles44', N'U') IS NOT NULL DROP TABLE TaxiVehicles44;
IF OBJECT_ID(N'TaxiVehicleTypes44', N'U') IS NOT NULL DROP TABLE TaxiVehicleTypes44;
IF OBJECT_ID(N'TaxiDrivers44', N'U') IS NOT NULL DROP TABLE TaxiDrivers44;
IF OBJECT_ID(N'TaxiCustomers44', N'U') IS NOT NULL DROP TABLE TaxiCustomers44;

-- Аэропорт
IF OBJECT_ID(N'AirportBookings44', N'U') IS NOT NULL DROP TABLE AirportBookings44;
IF OBJECT_ID(N'AirportPassengers44', N'U') IS NOT NULL DROP TABLE AirportPassengers44;
IF OBJECT_ID(N'AirportFlights44', N'U') IS NOT NULL DROP TABLE AirportFlights44;
IF OBJECT_ID(N'AirportCarriers44', N'U') IS NOT NULL DROP TABLE AirportCarriers44;
IF OBJECT_ID(N'AirportGates44', N'U') IS NOT NULL DROP TABLE AirportGates44;
IF OBJECT_ID(N'AirportTerminals44', N'U') IS NOT NULL DROP TABLE AirportTerminals44;
IF OBJECT_ID(N'AirportCities44', N'U') IS NOT NULL DROP TABLE AirportCities44;
GO


/* ===========================================================================
   ШАГ 3. Создание НОВОГО Аэропорта (7 таблиц)
=========================================================================== */
CREATE TABLE AirportCities44 (
    CityId        INT IDENTITY(1,1) NOT NULL,
    CityName      NVARCHAR(100)     NOT NULL,
    Country       NVARCHAR(100)     NOT NULL,
    CONSTRAINT PKAirportCities44 PRIMARY KEY (CityId),
    CONSTRAINT UQAirportCities44 UNIQUE (CityName, Country)
);

CREATE TABLE AirportTerminals44 (
    TerminalId         INT IDENTITY(1,1) NOT NULL,
    TerminalName       NVARCHAR(50)      NOT NULL,
    PassengerCapacity  INT               NOT NULL,
    OpenedOn           DATE              NULL,
    CONSTRAINT PKAirportTerminals44 PRIMARY KEY (TerminalId),
    CONSTRAINT UQAirportTerminalsName44 UNIQUE (TerminalName),
    CONSTRAINT CKAirportTerminalsCapacity44 CHECK (PassengerCapacity > 0)
);

CREATE TABLE AirportGates44 (
    GateId           INT IDENTITY(1,1) NOT NULL,
    TerminalId       INT               NOT NULL,
    GateCode         NVARCHAR(10)      NOT NULL,
    IsInternational  BIT               NOT NULL CONSTRAINT DFAirportGatesIntl44 DEFAULT 0,
    CONSTRAINT PKAirportGates44 PRIMARY KEY (GateId),
    CONSTRAINT UQAirportGates44 UNIQUE (TerminalId, GateCode),
    CONSTRAINT FKAirportGatesTerminals44 FOREIGN KEY (TerminalId)
        REFERENCES AirportTerminals44 (TerminalId)
);

CREATE TABLE AirportCarriers44 (
    CarrierId     INT IDENTITY(1,1) NOT NULL,
    CarrierName   NVARCHAR(100)     NOT NULL,
    CallSign      NVARCHAR(10)      NOT NULL,
    FoundedYear   SMALLINT          NULL,
    CONSTRAINT PKAirportCarriers44 PRIMARY KEY (CarrierId),
    CONSTRAINT UQAirportCarriersName44 UNIQUE (CarrierName),
    CONSTRAINT UQAirportCarriersCallSign44 UNIQUE (CallSign)
);

CREATE TABLE AirportFlights44 (
    FlightId           INT IDENTITY(1,1) NOT NULL,
    CarrierId          INT               NOT NULL,
    GateId             INT               NOT NULL,
    DestinationCityId  INT               NOT NULL,
    FlightCode         NVARCHAR(10)      NOT NULL,
    AircraftType       NVARCHAR(50)      NOT NULL,
    DepartureTime      DATETIME2         NOT NULL,
    DurationMinutes    INT               NOT NULL,
    Status             NVARCHAR(15)      NOT NULL CONSTRAINT DFAirportFlightsStatus44 DEFAULT 'OnTime',
    CONSTRAINT PKAirportFlights44 PRIMARY KEY (FlightId),
    CONSTRAINT UQAirportFlights44 UNIQUE (FlightCode, DepartureTime),
    CONSTRAINT CKAirportFlightsStatus44 CHECK (Status IN ('OnTime','Delayed','Boarding','Departed','Cancelled')),
    CONSTRAINT CKAirportFlightsDuration44 CHECK (DurationMinutes > 0),
    CONSTRAINT FKAirportFlightsCarriers44 FOREIGN KEY (CarrierId)
        REFERENCES AirportCarriers44 (CarrierId),
    CONSTRAINT FKAirportFlightsGates44 FOREIGN KEY (GateId)
        REFERENCES AirportGates44 (GateId),
    CONSTRAINT FKAirportFlightsCities44 FOREIGN KEY (DestinationCityId)
        REFERENCES AirportCities44 (CityId)
);

CREATE TABLE AirportPassengers44 (
    PassengerId     INT IDENTITY(1,1) NOT NULL,
    FullName        NVARCHAR(200)     NOT NULL,
    BirthDate       DATE              NOT NULL,
    PassportNumber  NVARCHAR(20)      NOT NULL,
    Phone           NVARCHAR(20)      NULL,
    CONSTRAINT PKAirportPassengers44 PRIMARY KEY (PassengerId),
    CONSTRAINT UQAirportPassengersPassport44 UNIQUE (PassportNumber)
);

CREATE TABLE AirportBookings44 (
    BookingId     INT IDENTITY(1,1) NOT NULL,
    FlightId      INT               NOT NULL,
    PassengerId   INT               NOT NULL,
    SeatNumber    NVARCHAR(4)       NOT NULL,
    FareClass     NVARCHAR(10)      NOT NULL,
    Price         DECIMAL(10,2)     NOT NULL,
    CheckedBags   TINYINT           NOT NULL CONSTRAINT DFAirportBookingsBags44 DEFAULT 0,
    BookedAt      DATETIME2         NOT NULL CONSTRAINT DFAirportBookingsBookedAt44 DEFAULT SYSDATETIME(),
    CONSTRAINT PKAirportBookings44 PRIMARY KEY (BookingId),
    CONSTRAINT UQAirportBookingsSeat44 UNIQUE (FlightId, SeatNumber),
    CONSTRAINT UQAirportBookingsPassenger44 UNIQUE (FlightId, PassengerId),
    CONSTRAINT CKAirportBookingsClass44 CHECK (FareClass IN ('Economy','Business','First')),
    CONSTRAINT CKAirportBookingsPrice44 CHECK (Price >= 0),
    CONSTRAINT FKAirportBookingsFlights44 FOREIGN KEY (FlightId)
        REFERENCES AirportFlights44 (FlightId),
    CONSTRAINT FKAirportBookingsPassengers44 FOREIGN KEY (PassengerId)
        REFERENCES AirportPassengers44 (PassengerId)
);
GO


/* ===========================================================================
   ШАГ 4. Создание НОВОЙ Галереи (7 таблиц)
=========================================================================== */
CREATE TABLE GalleryAuthors44 (
    AuthorId      INT IDENTITY(1,1) NOT NULL,
    FullName      NVARCHAR(200)     NOT NULL,
    Nationality   NVARCHAR(100)     NOT NULL,
    BirthYear     SMALLINT          NULL,
    DeathYear     SMALLINT          NULL,
    CONSTRAINT PKGalleryAuthors44 PRIMARY KEY (AuthorId),
    CONSTRAINT CKGalleryAuthorsYears44 CHECK (BirthYear IS NULL OR DeathYear IS NULL OR DeathYear >= BirthYear)
);

CREATE TABLE GalleryStyles44 (
    StyleId       INT IDENTITY(1,1) NOT NULL,
    StyleName     NVARCHAR(100)     NOT NULL,
    Era           NVARCHAR(50)      NULL,
    CONSTRAINT PKGalleryStyles44 PRIMARY KEY (StyleId),
    CONSTRAINT UQGalleryStylesName44 UNIQUE (StyleName)
);

CREATE TABLE GalleryRooms44 (
    RoomId        INT IDENTITY(1,1) NOT NULL,
    RoomName      NVARCHAR(100)     NOT NULL,
    Floor         TINYINT           NOT NULL,
    Area          DECIMAL(8,2)      NOT NULL,
    CONSTRAINT PKGalleryRooms44 PRIMARY KEY (RoomId),
    CONSTRAINT UQGalleryRoomsName44 UNIQUE (RoomName),
    CONSTRAINT CKGalleryRoomsArea44 CHECK (Area > 0)
);

CREATE TABLE GalleryShows44 (
    ShowId        INT IDENTITY(1,1) NOT NULL,
    RoomId        INT               NOT NULL,
    ShowTitle     NVARCHAR(200)     NOT NULL,
    StartDate     DATE              NOT NULL,
    EndDate       DATE              NOT NULL,
    CONSTRAINT PKGalleryShows44 PRIMARY KEY (ShowId),
    CONSTRAINT CKGalleryShowsDates44 CHECK (EndDate >= StartDate),
    CONSTRAINT FKGalleryShowsRooms44 FOREIGN KEY (RoomId)
        REFERENCES GalleryRooms44 (RoomId)
);

CREATE TABLE GalleryExhibits44 (
    ExhibitId       INT IDENTITY(1,1) NOT NULL,
    AuthorId        INT               NOT NULL,
    StyleId         INT               NOT NULL,
    ShowId          INT               NULL,
    Title           NVARCHAR(200)     NOT NULL,
    CreationYear    SMALLINT          NULL,
    Medium          NVARCHAR(100)     NOT NULL,
    EstimatedValue  DECIMAL(14,2)     NULL,
    Status          NVARCHAR(15)      NOT NULL CONSTRAINT DFGalleryExhibitsStatus44 DEFAULT 'Stored',
    CONSTRAINT PKGalleryExhibits44 PRIMARY KEY (ExhibitId),
    CONSTRAINT CKGalleryExhibitsStatus44 CHECK (Status IN ('Stored','Displayed','Loaned','Restoring')),
    CONSTRAINT CKGalleryExhibitsValue44 CHECK (EstimatedValue IS NULL OR EstimatedValue >= 0),
    CONSTRAINT FKGalleryExhibitsAuthors44 FOREIGN KEY (AuthorId)
        REFERENCES GalleryAuthors44 (AuthorId),
    CONSTRAINT FKGalleryExhibitsStyles44 FOREIGN KEY (StyleId)
        REFERENCES GalleryStyles44 (StyleId),
    CONSTRAINT FKGalleryExhibitsShows44 FOREIGN KEY (ShowId)
        REFERENCES GalleryShows44 (ShowId)
);

CREATE TABLE GalleryGuests44 (
    GuestId       INT IDENTITY(1,1) NOT NULL,
    FullName      NVARCHAR(200)     NOT NULL,
    Email         NVARCHAR(150)     NOT NULL,
    Phone         NVARCHAR(20)      NULL,
    CONSTRAINT PKGalleryGuests44 PRIMARY KEY (GuestId),
    CONSTRAINT UQGalleryGuestsEmail44 UNIQUE (Email)
);

CREATE TABLE GalleryEntries44 (
    EntryId       INT IDENTITY(1,1) NOT NULL,
    GuestId       INT               NOT NULL,
    ShowId        INT               NOT NULL,
    EntryDate     DATE              NOT NULL,
    TicketPrice   DECIMAL(8,2)      NOT NULL,
    CONSTRAINT PKGalleryEntries44 PRIMARY KEY (EntryId),
    CONSTRAINT CKGalleryEntriesPrice44 CHECK (TicketPrice >= 0),
    CONSTRAINT FKGalleryEntriesGuests44 FOREIGN KEY (GuestId)
        REFERENCES GalleryGuests44 (GuestId),
    CONSTRAINT FKGalleryEntriesShows44 FOREIGN KEY (ShowId)
        REFERENCES GalleryShows44 (ShowId)
);
GO

PRINT 'Готово: старые таблицы из скрипта удалены. Созданы новые Аэропорт и Галерея.';
GO