CREATE TABLE [dbo].[CM_Canteens] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Location] nvarchar(max)  NOT NULL
);

CREATE TABLE [dbo].[CM_Menus] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [ImageUrl] nvarchar(max)  NOT NULL,
    [Nutrition] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Canteen_Id] int  NOT NULL
);

CREATE TABLE [dbo].[CM_CanteenEditor] (
    [CM_Canteen_Id] int  NOT NULL,
    [Editors_PrincipalID] int  NOT NULL
);

ALTER TABLE [dbo].[CM_Canteens]
ADD CONSTRAINT [PK_CM_Canteens]
    PRIMARY KEY CLUSTERED ([Id] ASC);

ALTER TABLE [dbo].[CM_Menus]
ADD CONSTRAINT [PK_CM_Menus]
    PRIMARY KEY CLUSTERED ([Id] ASC);

ALTER TABLE [dbo].[CM_CanteenEditor]
ADD CONSTRAINT [PK_CM_CanteenEditor]
    PRIMARY KEY CLUSTERED ([CM_Canteen_Id], [Editors_PrincipalID] ASC);

ALTER TABLE [dbo].[CM_CanteenEditor]
ADD CONSTRAINT [FK_CM_CanteenEditor_CM_Canteen]
    FOREIGN KEY ([CM_Canteen_Id])
    REFERENCES [dbo].[CM_Canteens]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE [dbo].[CM_CanteenEditor]
ADD CONSTRAINT [FK_CM_CanteenEditor_Principal]
    FOREIGN KEY ([Editors_PrincipalID])
    REFERENCES [dbo].[Principal]
        ([PrincipalID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX [IX_FK_CM_CanteenEditor_Principal]
ON [dbo].[CM_CanteenEditor]
    ([Editors_PrincipalID]);

ALTER TABLE [dbo].[CM_Menus]
ADD CONSTRAINT [FK_CM_MenuCM_Canteen]
    FOREIGN KEY ([Canteen_Id])
    REFERENCES [dbo].[CM_Canteens]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX [IX_FK_CM_MenuCM_Canteen]
ON [dbo].[CM_Menus]
    ([Canteen_Id]);

CREATE NONCLUSTERED INDEX [IX_DATE_CANTEENID_CM_Menu_IDX] ON [dbo].[CM_Menus]
(
	[Date] ASC,
	[Canteen_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



