-- Copyright 2019 Amazon.com, Inc. or its affiliates. All Rights Reserved.
--
-- Permission is hereby granted, free of charge, to any person obtaining a copy of this
-- software and associated documentation files (the "Software"), to deal in the Software
-- without restriction, including without limitation the rights to use, copy, modify,
-- merge, publish, distribute, sublicense, and/or sell copies of the Software, and to
-- permit persons to whom the Software is furnished to do so.
--
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
-- INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
-- PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
-- HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
-- OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
-- SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

-- Load up the UUID data type
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create a login role for the application to connect as so it is not connecting
-- as the master user and so that it is not the owner of the tables
DO $$
BEGIN
    IF NOT EXISTS(SELECT * FROM pg_roles WHERE rolname = '{{DB_APP_USER}}') THEN
        CREATE USER {{DB_APP_USER}} WITH LOGIN PASSWORD '{{DB_APP_PASS}}';
        GRANT USAGE ON SCHEMA public TO {{DB_APP_USER}};
        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO {{DB_APP_USER}};
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO {{DB_APP_USER}};
        GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO {{DB_APP_USER}};
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO {{DB_APP_USER}};
    END IF;
END
$$

-- Create a table for our tenants with indexes on the primary key and the tenant’s name
CREATE TABLE IF NOT EXISTS tenant (
    tenant_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name VARCHAR(255) UNIQUE,
    status VARCHAR(64) CHECK (status IN ('Active', 'Suspended')),
    tier VARCHAR(64) CHECK (tier IN ('Gold', 'Silver', 'Bronze'))
);

-- Turn on RLS
ALTER TABLE tenant ENABLE ROW LEVEL SECURITY;

-- Restrict read and write actions so tenants can only see their rows
-- cast the UUID value in tenant_id to match the type current_user returns
DO $$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_policies WHERE tablename = 'tenant' AND policyname = 'tenant_isolation_policy') THEN
        CREATE POLICY tenant_isolation_policy ON tenant
        USING (tenant_id = current_setting('app.current_tenant')::UUID);
    END IF;
END
$$

-- Create a table for users of a tenant
CREATE TABLE IF NOT EXISTS tenant_user (
    user_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenant (tenant_id) ON DELETE RESTRICT,
    email VARCHAR(255) NOT NULL UNIQUE,
    given_name VARCHAR(255) NOT NULL CHECK (given_name <> ''),
    family_name VARCHAR(255) NOT NULL CHECK (family_name <> '')
);

-- And apply RLS for the tenant users as we did for tenants
ALTER TABLE tenant_user ENABLE ROW LEVEL SECURITY;

DO $$
BEGIN
    IF NOT EXISTS(SELECT * FROM pg_policies WHERE tablename = 'tenant_user' AND policyname = 'tenant_user_isolation_policy') THEN
        CREATE POLICY tenant_user_isolation_policy ON tenant_user
        USING (tenant_id = current_setting('app.current_tenant')::UUID);
    END IF;
END
$$