/* Set up database */
USE [master];
GO

CREATE DATABASE RowSecurity;
GO

/* Create tables */
USE [RowSecurity];
GO

CREATE TABLE Users (
  UserId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Username VARCHAR(50) NOT NULL
);

CREATE TABLE Regions (
  RegionId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  RegionName VARCHAR(50) NOT NULL
);

CREATE TABLE Sites (
  SiteId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  RegionId INT NOT NULL REFERENCES Regions(RegionId),
  SiteName VARCHAR(50) NOT NULL
);

CREATE TABLE UserRegions (
  UserId INT NOT NULL REFERENCES Users(UserId),
  RegionId INT NOT NULL REFERENCES Regions(RegionId)
);

/* Populate tables with sample data */
INSERT INTO Users (Username) VALUES ('Alice');
INSERT INTO Users (Username) VALUES ('Bob');
INSERT INTO Users (Username) VALUES ('Charlie');
INSERT INTO Users (Username) VALUES ('David');

INSERT INTO Regions (RegionName) VALUES ('East');
INSERT INTO Regions (RegionName) VALUES ('Central');
INSERT INTO Regions (RegionName) VALUES ('West');

INSERT INTO Sites (SiteName, RegionId)
SELECT 'New York City', RegionId
  FROM Regions
 WHERE RegionName = 'East';

INSERT INTO Sites (SiteName, RegionId)
SELECT 'Boston', RegionId
  FROM Regions
 WHERE RegionName = 'East';

INSERT INTO Sites (SiteName, RegionId)
SELECT 'Charleston', RegionId
  FROM Regions
 WHERE RegionName = 'East';
 
INSERT INTO Sites (SiteName, RegionId)
SELECT 'Chicago', RegionId
  FROM Regions
 WHERE RegionName = 'Central';
 
INSERT INTO Sites (SiteName, RegionId)
 SELECT 'Detroit', RegionId
   FROM Regions
  WHERE RegionName = 'Central';

INSERT INTO Sites (SiteName, RegionId)
 SELECT 'St. Louis', RegionId
   FROM Regions
  WHERE RegionName = 'Central';

INSERT INTO Sites (SiteName, RegionId)
 SELECT 'Seattle', RegionId
   FROM Regions
  WHERE RegionName = 'West';

INSERT INTO Sites (SiteName, RegionId)
 SELECT 'Los Angeles', RegionId
   FROM Regions
  WHERE RegionName = 'West';

INSERT INTO Sites (SiteName, RegionId)
 SELECT 'Provo', RegionId
   FROM Regions
  WHERE RegionName = 'West';
  
/* Create records for users and regions */
INSERT INTO UserRegions (UserId, RegionId)
SELECT UserId, RegionId
  FROM Users 
       CROSS JOIN Regions
 WHERE Username = 'Alice'
 
INSERT INTO UserRegions (UserId, RegionId)
SELECT UserId, RegionId
  FROM Users
       INNER JOIN Regions ON RegionName = 'East'
 WHERE Username = 'Bob'

INSERT INTO UserRegions (UserId, RegionId)
SELECT UserId, RegionId
  FROM Users
       INNER JOIN Regions ON RegionName = 'Central'
 WHERE Username = 'Charlie';
 
INSERT INTO UserRegions (UserId, RegionId)
SELECT UserId, REgionId
  FROM Users
       INNER JOIN Regions ON RegionName = 'West'
 WHERE Username = 'David';

/* A query to check the data */
SELECT u.Username,
       r.RegionName,
       s.SiteName
  FROM dbo.Users AS u
       INNER JOIN dbo.UserRegions AS ur
          ON u.UserId = ur.UserId
       INNER JOIN dbo.Regions AS r
          ON ur.RegionId = r.RegionId
       INNER JOIN dbo.Sites AS s
          ON r.RegionId = s.RegionId
 ORDER BY Username, RegionName, SiteName;

/* Create sales contacts table */
CREATE TABLE SalesContacts (
  SalesContactId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  SiteId INT NOT NULL REFERENCES Sites(SiteId),
  Name VARCHAR(50) NOT NULL,
  PhoneNumber VARCHAR(10) NOT NULL
);

/* Create sales contacts data 
   Names generated from http://www.fakenamegenerator.com/ */
INSERT INTO SalesContacts VALUES (1, 'Emma L. Atkins', '339-221-1843');
INSERT INTO SalesContacts VALUES (2, 'Linda R. Oneil', '734-238-6974');
INSERT INTO SalesContacts VALUES (3, 'Tristan P. Pineda', '201-326-3782');
INSERT INTO SalesContacts VALUES (4, 'Nicholas M. Garza', '818-917-9238');
INSERT INTO SalesContacts VALUES (5, 'Ronald C. Villalpando', '603-537-9666');
INSERT INTO SalesContacts VALUES (6, 'Helen M. Ross', '603-664-8708');
INSERT INTO SalesContacts VALUES (7, 'Patricia T. Haak', '906-748-3668');
INSERT INTO SalesContacts VALUES (8, 'Bryant J. Melton', '859-328-7422');
INSERT INTO SalesContacts VALUES (9, 'Justin J. Norton', '206-835-5862');

CREATE PROCEDURE SalesContactsForUser(
  @UserId INT
) WITH EXECUTE AS OWNER AS
SET NOCOUNT ON;

SELECT sc.SalesContactId,
       sc.Name,
       sc.PhoneNumber
  FROM SalesContacts AS sc
       INNER JOIN Sites AS s
          ON sc.SiteId = s.SiteId
       INNER JOIN Regions AS r
          ON s.RegionId = r.RegionId
       INNER JOIN UserRegions AS ur
          ON r.RegionId = ur.RegionId
 WHERE ur.UserId = @UserID;