-- ****************** SqlDBM: Microsoft SQL Server ******************
-- ******************************************************************

-- ************************************** [Ticket]

CREATE TABLE [Ticket]
(
 [TicketId]     int IDENTITY (1, 1) NOT NULL ,
 [DiscountId]   int NOT NULL ,
 [PriceId]      int NOT NULL ,
 [AreaId]       int NOT NULL ,
 [ValidityDate] timestamp NOT NULL ,
 CONSTRAINT PK_Ticket PRIMARY KEY NONCLUSTERED ([TicketId]),
 );
GO






-- ************************************** [ControlSchedule]

CREATE TABLE [ControlSchedule]
(
 [ControlScheduleId] int IDENTITY (1, 1) NOT NULL ,
 [RegionId]          int NOT NULL ,
 [MeanOfTransportId] int NOT NULL ,
 [Month]             int NOT NULL ,
 CONSTRAINT PK_ControlSchedule PRIMARY KEY NONCLUSTERED ([ControlScheduleId]),

);
GO

-- ************************************** [Company]

CREATE TABLE [Company]
(
 [CompanyId]      int IDENTITY (1, 1) NOT NULL ,
 [CompanyName]    varchar(45) NOT NULL ,
 [CityId]         int NOT NULL ,
 [StreetId]       int NOT NULL ,
 [BuildingNumber] int NOT NULL ,
 [PostalCode]     varchar(7) NOT NULL ,
 [PhoneNumber]    int NOT NULL ,
 [EmailAddress]   varchar(30) NOT NULL ,
 CONSTRAINT PK_CompanyId PRIMARY KEY NONCLUSTERED ([CompanyId]),

);
GO



EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'NIP / REGON

', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Company', @level2type=N'COLUMN', @level2name=N'CompanyId';
GO


-- ************************************** [Person]
CREATE TABLE [Person]
(
 [PersonId]       int IDENTITY (1, 1) NOT NULL ,
 [Name]           varchar(30) NOT NULL ,
 [Surname]        varchar(30) NOT NULL ,
 [PESEL]          varchar(11) NULL ,
 [TicketId]       int NULL ,
 [DocumentTypeId] int NOT NULL ,
 [DocumentSeries] int NOT NULL ,
 [FathersName]    varchar(30) NULL ,
 [CityId]         int NOT NULL ,
 [StreetId]       int NOT NULL ,
 [BuildingNumber] int NOT NULL ,
 [FlatNumber]     int NULL ,
 [NationalityId]  int NULL ,
 [BirthCountryId] int NULL ,
 [BirthCityId]    int NULL ,
 [BirthDate]      date NULL ,
 CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED ([PersonId]),
 CONSTRAINT FK_Person_Ticket FOREIGN KEY (PersonId)
    REFERENCES Ticket (TicketId)
);
GO




-- ************************************** [Accounts]

CREATE TABLE [Accounts]
(
 [AccountsId]      int IDENTITY (1, 1) NOT NULL ,
 [CompanyId]       int NOT NULL ,
 [Revenue]         float NOT NULL ,
 [OperationalCost] float NOT NULL ,
 [Month]           int NOT NULL ,
 [Year]            int NOT NULL ,
 CONSTRAINT PK_Accounts PRIMARY KEY NONCLUSTERED (AccountsId),
 CONSTRAINT FK_Accounts_Company FOREIGN KEY (AccountsId)
    REFERENCES [Company] ([CompanyId])
);
GO


-- ************************************** [TicketCollector]

CREATE TABLE [TicketCollector]
(
 [TicketCollectorId]          int IDENTITY (1, 1) NOT NULL ,
 [CompanyId]                  int NOT NULL ,
 [ControlScheduleId]          int NULL ,
 [Name]                       varchar(30) NOT NULL ,
 [Surname]                    varchar(30) NOT NULL ,
 [MedicalExamValidity]        date NOT NULL ,
 [Age]                        int NOT NULL ,
 [LicenseNumber]              int NOT NULL ,
 [TrainingValidity]           date NOT NULL ,
 [JobStatusId]                int NOT NULL ,
 [BankAccountNumber]          varchar(26) NOT NULL ,
 [ColleagueTicketCollectorId] int NULL ,
  CONSTRAINT PK_TicketCollector PRIMARY KEY NONCLUSTERED ([TicketCollectorId]),
 CONSTRAINT FK_TicketCollector_Company FOREIGN KEY ([TicketCollectorId])
    REFERENCES [Company] ([CompanyId]),
CONSTRAINT FK_TicketCollector_ControlSchedule FOREIGN KEY ([TicketCollectorId])
    REFERENCES [ControlSchedule] ([ControlScheduleId])


-- CONSTRAINT [FK_158] FOREIGN KEY ([ColleagueTicketCollectorId])  REFERENCES [TicketCollector]([TicketCollectorId]),

);
GO
ALTER TABLE [TicketCollector]
ADD CONSTRAINT FK_TicketCollector_TicketCollector FOREIGN KEY ([TicketCollectorId])
    REFERENCES TicketCollector ([TicketCollectorId])

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'#1 - working
#2 - vacation
#3 - suspended', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'TicketCollector', @level2type=N'COLUMN', @level2name=N'JobStatusId';
GO
-- ************************************** [Payroll]

CREATE TABLE [Payroll]
(
 [PayrollId]         int IDENTITY (1, 1) NOT NULL ,
 [TicketCollectorId] int NOT NULL ,
 [ContractTypeId]    int NOT NULL ,
 [GrossSalary]       float NOT NULL ,
 [Bonus]             float NOT NULL ,
 [Year]              int NOT NULL ,
 [Month]             int NOT NULL ,
  CONSTRAINT PK_Payroll PRIMARY KEY NONCLUSTERED ([PayrollId]),
 CONSTRAINT FK_Payroll_TicketCollector FOREIGN KEY ([PayrollId])
    REFERENCES [TicketCollector] ([TicketCollectorId])
);
GO

-- ************************************** [Fine]

CREATE TABLE [Fine]
(
 [FineId]                  int IDENTITY (1, 1) NOT NULL ,
 [TicketCollectorId]       int NOT NULL ,
 [PersonId]                int NOT NULL ,
 [WritingDate]             timestamp NOT NULL ,
 [FineTypeId]              int NOT NULL ,
 [FineAmountWithInterests] float NOT NULL ,
 [PaymentStatusId]         int NOT NULL ,
 CONSTRAINT PK_Fine PRIMARY KEY NONCLUSTERED ([FineId]),
 CONSTRAINT FK_Fine_TicketCollector FOREIGN KEY ([FineId])
    REFERENCES [TicketCollector] ([TicketCollectorId]),
CONSTRAINT FK_Fine_Person FOREIGN KEY ([FineId])
    REFERENCES [Person] ([PersonId])
);
GO


-- ************************************** [Complaint]

CREATE TABLE [Complaint]
(
 [ComplaintId]       int NOT NULL ,
 [PersonId]          int NULL ,
 [TicketCollectorId] int NULL ,
 [Justification]     varchar(3000) NOT NULL ,
 [ArrivalDate]       timestamp NOT NULL ,
 [ComplaintStatusId] int NOT NULL ,
 CONSTRAINT PK_Complaint PRIMARY KEY NONCLUSTERED ([ComplaintId]),
 CONSTRAINT FK_Complaint_TicketCollector FOREIGN KEY ([ComplaintId])
    REFERENCES [TicketCollector] ([TicketCollectorId]),
CONSTRAINT FK_Complaint_Person FOREIGN KEY ([ComplaintId])
    REFERENCES [Person] ([PersonId])
);
GO


EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'#1 - successfull
#2 - pending
#3 - rejected', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Complaint', @level2type=N'COLUMN', @level2name=N'ComplaintStatusId';
GO