-- Database definition
-- TODO Check indexes on all tables

-- Extensions
CREATE EXTENSION IF NOT EXISTS "cube";
CREATE EXTENSION IF NOT EXISTS "earthdistance";
CREATE EXTENSION IF NOT EXISTS "fuzzystrmatch";

-- BAN
CREATE TABLE IF NOT EXISTS zipgeo (
    city                TEXT,
    zipcode             TEXT,
    citycode            TEXT,
    latitude            FLOAT,
    longitude           FLOAT
);

--COPY zipgeo FROM PROGRAM 'zcat ./src/main/resources/cp-lat-lon.csv.gz' DELIMITER ';';

-- Types
DO $$
BEGIN

    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'order_status') THEN
        CREATE TYPE order_status AS ENUM ('canceled', 'finished', 'pending', 'paid', 'created');
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'discount_status') THEN
        CREATE TYPE discount_status AS ENUM ('usable', 'used', 'canceled');
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'payment') THEN
        CREATE TYPE payment AS ENUM ('none', 'CB', 'paypal');
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location') THEN
        CREATE TYPE location AS (
            address             TEXT,
            city                TEXT,
            postalcode          TEXT,

            latitude            FLOAT,
            longitude           FLOAT
        );
    END IF;

END $$;

-- Tables
CREATE TABLE IF NOT EXISTS person (
    id                  UUID       PRIMARY KEY,
    created             TIMESTAMP  NOT NULL,
    email               TEXT       UNIQUE NOT NULL,
    password            TEXT       NOT NULL
);

CREATE TABLE IF NOT EXISTS files (
    id                  UUID        PRIMARY KEY,
    personId            UUID        REFERENCES person,
    date                TIMESTAMP   NOT NULL,
    type                TEXT        NOT NULL,
    content             BYTEA
);

CREATE SEQUENCE IF NOT EXISTS customer_code_seq START 100000;

CREATE TABLE IF NOT EXISTS customer (
    id                  UUID       PRIMARY KEY REFERENCES person,
    code                INTEGER    NOT NULL UNIQUE DEFAULT nextval('customer_code_seq'),

    firstname           TEXT       NOT NULL,
    lastname            TEXT       NOT NULL,
    birthdate           DATE,
    address             location   NOT NULL,
    addressfallback     TIMESTAMP,
    mainphone           TEXT       NOT NULL,
    otherphone          TEXT,

    validatedEmail      UUID
);

ALTER SEQUENCE customer_code_seq OWNED BY customer.code;

CREATE TABLE IF NOT EXISTS gardener (
    id                  UUID       PRIMARY KEY REFERENCES person,
    enabled             BOOL       NOT NULL DEFAULT true,

    firstname           TEXT       NOT NULL,
    lastname            TEXT       NOT NULL,
    birthdate           DATE,
    address             location   NOT NULL,
    addressfallback     TIMESTAMP,
    mainphone           TEXT       NOT NULL,
    otherphone          TEXT,

    companyname         TEXT       NOT NULL,
    companynumber       TEXT       NOT NULL,
    companystatus       TEXT,
    companynumbervat    TEXT,
    companyrcs          TEXT,
    freelancer          BOOL       NOT NULL DEFAULT false,
    services            TEXT[]
);

CREATE SEQUENCE IF NOT EXISTS customer_order_code_seq START 100000;
CREATE SEQUENCE IF NOT EXISTS customer_order_billnumber_seq START 100000;

CREATE TABLE IF NOT EXISTS customer_order (
    id                      UUID            PRIMARY KEY,
    code                    INTEGER         NOT NULL UNIQUE DEFAULT nextval('customer_order_code_seq'),

    date                    TIMESTAMP       NOT NULL,
    status                  order_status    NOT NULL,
    statuspayment           payment         DEFAULT 'none',
    
    customer                UUID            NOT NULL REFERENCES customer,
    gardenaddress           location        NOT NULL,
    comment                 TEXT,
    admincomment            TEXT,

    gardener                UUID            REFERENCES gardener,

    -- Use by the status
    canceldate              DATE,
    validate                BOOL,
    validatecomment         TEXT,
    
    bill                    BYTEA,
    billnumber              INTEGER,
    billaddress             location        NOT NULL,
    tva                     DECIMAL         NOT NULL,
    marge                   DECIMAL         NOT NULL,
    fee                     DECIMAL         NOT NULL,
    
    totalamout              DECIMAL         NOT NULL,
    totaldiscount           DECIMAL         NOT NULL,
    totaltime               DECIMAL         NOT NULL
);

ALTER SEQUENCE customer_order_code_seq OWNED BY customer_order.code;
ALTER SEQUENCE customer_order_billnumber_seq OWNED BY customer_order.billnumber;

CREATE TABLE IF NOT EXISTS customer_order_detail (
    id                      UUID            PRIMARY KEY,
    customer                UUID            NOT NULL REFERENCES customer,
    customerorder           UUID            NOT NULL REFERENCES customer_order,

    service                 TEXT            NOT NULL,
    comment                 TEXT,
    amount                  DECIMAL         NOT NULL,
    time                    DECIMAL         NOT NULL,
    parameters              JSONB           NOT NULL
);

CREATE SEQUENCE IF NOT EXISTS customer_discount_code_seq START 100000;

CREATE TABLE IF NOT EXISTS customer_discount (
    id              UUID                PRIMARY KEY,
    created         TIMESTAMP           NOT NULL,
    code            INTEGER             NOT NULL UNIQUE DEFAULT nextval('customer_discount_code_seq'),
    publiccode      TEXT                NOT NULL UNIQUE,
    status          discount_status     NOT NULL,
    validity        TIMESTAMP           NOT NULL,
    amount          DECIMAL             NOT NULL,
    customer        UUID                NOT NULL REFERENCES customer,
    linkorder       UUID                REFERENCES customer_order,
    usedorder       UUID                REFERENCES customer_order
);

ALTER SEQUENCE customer_discount_code_seq OWNED BY customer_discount.code;

CREATE TABLE IF NOT EXISTS gardener_team (
    id                  UUID            PRIMARY KEY,
    name                TEXT            NOT NULL,
    number              INTEGER         DEFAULT 1,
    phone               TEXT,
    gardener            UUID            REFERENCES gardener,
    deleted             BOOL            DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS gardener_calendar (
    id                  UUID            PRIMARY KEY,
    day                 DATE,
    gardener            UUID            NOT NULL REFERENCES gardener,
    
    team                UUID            NOT NULL REFERENCES gardener_team,
    teamnumber          INTEGER         DEFAULT 1,

    slot                INTEGER         DEFAULT 1 CHECK (slot >= 1 AND slot <= 8), -- 1 to 4 for AM and 5 to 8 for PM

    customerorder       UUID            REFERENCES customer_order,
    datereserved        TIMESTAMP,
    dateconfirmed       TIMESTAMP,

    UNIQUE(day, team, slot)
);

CREATE TABLE IF NOT EXISTS pages (
    id                  UUID            PRIMARY KEY,
    path                TEXT            NOT NULL UNIQUE,
    title               TEXT            NOT NULL,
    content             TEXT            NOT NULL,
    lastmodification    TIMESTAMP       NOT NULL,
    lastpublication     TIMESTAMP
);

INSERT INTO pages (id, path, title, lastmodification, content) VALUES ('a0973ad6-8637-4dbf-9d89-cabaaee76608', 'help', 'Besoin d''aide', now() at time zone 'utc', '') ON CONFLICT (id) DO NOTHING ;
INSERT INTO pages (id, path, title, lastmodification, content) VALUES ('82550ad4-e48e-411b-bbc6-0c5d720fc222', 'who', 'Qui nous sommes', now() at time zone 'utc', '') ON CONFLICT (id) DO NOTHING ;
INSERT INTO pages (id, path, title, lastmodification, content) VALUES ('2bd95127-2807-42b4-af9a-54bcfe59642d', 'mentions', 'Mentions légales', now() at time zone 'utc', '') ON CONFLICT (id) DO NOTHING ;
INSERT INTO pages (id, path, title, lastmodification, content) VALUES ('9edc2789-ae36-4a73-b709-bc41c06f2ba2', 'cgu', 'CGU', now() at time zone 'utc', '') ON CONFLICT (id) DO NOTHING ;
INSERT INTO pages (id, path, title, lastmodification, content) VALUES ('0aad2baa-5082-4eca-8bb6-a4721c46a95b', 'cgv', 'CGV', now() at time zone 'utc', '') ON CONFLICT (id) DO NOTHING ;
INSERT INTO pages (id, path, title, lastmodification, content) VALUES ('dd3483f5-ea21-44b8-badd-34bab8c95c3c', 'comments', 'Avis', now() at time zone 'utc', '') ON CONFLICT (id) DO NOTHING ;

-- Roles
DO $$
BEGIN

    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'role_admin') THEN
        CREATE ROLE role_admin;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'role_customer') THEN
        CREATE ROLE role_customer;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'role_gardener') THEN
        CREATE ROLE role_gardener;
    END IF;

END $$;

ALTER TABLE person ENABLE ROW LEVEL SECURITY;
ALTER TABLE customer ENABLE ROW LEVEL SECURITY;
ALTER TABLE customer_order ENABLE ROW LEVEL SECURITY;
ALTER TABLE customer_discount ENABLE ROW LEVEL SECURITY;
ALTER TABLE gardener ENABLE ROW LEVEL SECURITY;
ALTER TABLE gardener_calendar ENABLE ROW LEVEL SECURITY;
ALTER TABLE gardener_team ENABLE ROW LEVEL SECURITY;
ALTER TABLE files ENABLE ROW LEVEL SECURITY;

-- Security admin
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO role_admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO role_admin;

DROP POLICY IF EXISTS person_admin_policy ON person;
CREATE POLICY person_admin_policy ON person TO role_admin USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS customer_admin_policy ON customer;
CREATE POLICY customer_admin_policy ON customer TO role_admin USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS customer_order_admin_policy ON customer_order;
CREATE POLICY customer_order_admin_policy ON customer_order TO role_admin USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS customer_order_detail_admin_policy ON customer_order;
CREATE POLICY customer_order_detail_admin_policy ON customer_order_detail TO role_admin USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS customer_discount_admin_policy ON customer_discount;
CREATE POLICY customer_discount_admin_policy ON customer_discount TO role_admin USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS gardener_admin_policy ON gardener;
CREATE POLICY gardener_admin_policy ON gardener TO role_admin USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS gardener_calendar_admin_policy ON gardener_calendar;
CREATE POLICY gardener_calendar_admin_policy ON gardener_calendar TO role_admin USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS gardener_team_admin_policy ON gardener_calendar;
CREATE POLICY gardener_team_admin_policy ON gardener_team TO role_admin USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS files_admin_policy ON files;
CREATE POLICY files_admin_policy ON files TO role_admin USING (true) WITH CHECK (true);

DROP USER IF EXISTS "8a873d22-e784-40ae-9fd2-24408c7acf39";
INSERT INTO person (id, created, email, password) VALUES ('8a873d22-e784-40ae-9fd2-24408c7acf39', now() at time zone 'utc', 'admin', 'K6Fbnprrt9hjJxEhuYImw3kMdQK0PMW/9GKArSIyQsyOzcKsHalGSiW+1qM9Q6xsFSe+/bAyrPm3j5zpCg3bdF+UE4Z5XIABq+tnzSRqHLTwiGwDx4dJpTntP9JDZ2yHDsgTAcWefWxlSQsPeMIto+YK+dKA+NcchMb97XkMf+E=:mUISmVOPLvphNDCSWBpXyUdgUh9hcwQv4MzO3pQxfSE=') ON CONFLICT (id) DO NOTHING ;
CREATE USER "8a873d22-e784-40ae-9fd2-24408c7acf39" IN ROLE role_admin;

-- Security for anonymous
DROP USER IF EXISTS anonymous;
CREATE USER anonymous;
GRANT SELECT ON zipgeo TO anonymous;
GRANT SELECT, INSERT, UPDATE(password) ON person TO anonymous;
GRANT ALL ON SEQUENCE customer_code_seq TO anonymous;
GRANT SELECT(validatedEmail), INSERT, UPDATE(validatedEmail) ON customer TO anonymous;
GRANT SELECT ON customer_discount TO anonymous;
GRANT SELECT(address, enabled) ON gardener TO anonymous;

DROP POLICY IF EXISTS person_anonymous_policy ON person;
CREATE POLICY person_anonymous_policy ON person TO anonymous USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS customer_anonymous_policy ON customer;
CREATE POLICY customer_anonymous_policy ON customer TO anonymous USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS customer_discount_anonymous_policy ON customer_discount;
CREATE POLICY customer_discount_anonymous_policy ON customer_discount TO anonymous USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS gardener_anonymous_policy ON gardener;
CREATE POLICY gardener_anonymous_policy ON gardener TO anonymous USING (true) WITH CHECK (true);

-- Security for customer
GRANT SELECT ON zipgeo TO role_customer;
GRANT ALL ON SEQUENCE customer_order_code_seq TO role_customer;
GRANT ALL ON SEQUENCE customer_order_billnumber_seq TO role_customer;
GRANT ALL ON SEQUENCE customer_discount_code_seq TO role_customer;
GRANT SELECT, UPDATE ON person TO role_customer;
GRANT SELECT, UPDATE ON customer TO role_customer;
GRANT SELECT(id, code, date, status, statuspayment, customer, 
gardenaddress , comment, gardener, canceldate, validate, billnumber, bill, billaddress, tva, marge, fee, 
totalamout, totaldiscount, totaltime), UPDATE, INSERT ON customer_order TO role_customer;
GRANT SELECT, UPDATE, INSERT ON customer_order_detail TO role_customer;
GRANT SELECT, UPDATE (status, usedorder), INSERT ON customer_discount TO role_customer;
GRANT SELECT ON gardener TO role_customer;
GRANT SELECT ON gardener_team TO role_customer;
GRANT SELECT, UPDATE(customerorder, datereserved, dateconfirmed) ON gardener_calendar TO role_customer;

DROP POLICY IF EXISTS person_customer_policy ON person;
CREATE POLICY person_customer_policy ON person TO role_customer USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS customer_policy ON customer;
CREATE POLICY customer_policy ON customer TO role_customer USING (id::TEXT = current_user::TEXT);

DROP POLICY IF EXISTS customer_order_policy ON customer_order;
CREATE POLICY customer_order_policy ON customer_order TO role_customer USING (customer::TEXT = current_user::TEXT);

DROP POLICY IF EXISTS customer_order_detail_policy ON customer_order_detail;
CREATE POLICY customer_order_detail_policy ON customer_order_detail TO role_customer USING (customer::TEXT = current_user::TEXT);

DROP POLICY IF EXISTS gardener_customer_policy ON gardener;
CREATE POLICY gardener_customer_policy ON gardener TO role_customer USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS gardener_team_customer_policy ON gardener_team;
CREATE POLICY gardener_team_customer_policy ON gardener_team TO role_customer USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS gardener_calendar_customer_policy ON gardener_calendar;
CREATE POLICY gardener_calendar_customer_policy ON gardener_calendar TO role_customer USING (true) WITH CHECK (true); -- TODO: check dates and the order of the customer

DROP POLICY IF EXISTS customer_discount_policy ON customer_discount;
CREATE POLICY customer_discount_policy ON customer_discount TO role_customer USING (customer::TEXT = current_user::TEXT);

-- Security for gardener
GRANT SELECT ON zipgeo TO role_gardener;
GRANT SELECT, UPDATE ON person TO role_gardener;
GRANT SELECT, UPDATE(firstname, lastname, birthdate, address, addressfallback, mainphone, otherphone) ON gardener TO role_gardener;
GRANT SELECT ON customer TO role_gardener;
GRANT SELECT, INSERT(id, day, gardener, slot, team, teamnumber), UPDATE(teamnumber) ON gardener_calendar TO role_gardener;
GRANT SELECT, UPDATE, INSERT ON gardener_team TO role_gardener;
GRANT SELECT(id, code, date, status, statuspayment, customer, 
gardenaddress , comment, gardener, canceldate, validate, 
validatecomment, billnumber, bill, billaddress, tva, marge, fee, totalamout, totaldiscount, totaltime), UPDATE(status, validate, validatecomment) ON customer_order TO role_gardener;
GRANT SELECT ON customer_order_detail TO role_gardener;
GRANT SELECT, INSERT ON files TO role_gardener;

DROP POLICY IF EXISTS person_gardener_policy ON person;
CREATE POLICY person_gardener_policy ON person TO role_gardener USING (id::TEXT = current_user::TEXT);

DROP POLICY IF EXISTS customer_gardener_policy ON customer;
CREATE POLICY customer_gardener_policy ON customer TO role_gardener USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS gardener_policy ON gardener;
CREATE POLICY gardener_policy ON gardener TO role_gardener USING (id::TEXT = current_user::TEXT);

DROP POLICY IF EXISTS gardener_calendar_policy ON gardener_calendar;
CREATE POLICY gardener_calendar_policy ON gardener_calendar TO role_gardener USING (gardener::TEXT = current_user::TEXT);

DROP POLICY IF EXISTS gardener_team_policy ON gardener_team;
CREATE POLICY gardener_team_policy ON gardener_team TO role_gardener USING (gardener::TEXT = current_user::TEXT);

DROP POLICY IF EXISTS customer_order_gardener_policy ON customer_order;
CREATE POLICY customer_order_gardener_policy ON customer_order TO role_gardener USING (gardener::TEXT = current_user::TEXT);

DROP POLICY IF EXISTS customer_order_detail_gardener_policy ON customer_order_detail;
CREATE POLICY customer_order_detail_gardener_policy ON customer_order_detail TO role_gardener USING (customerorder IN (SELECT o.id FROM customer_order o WHERE o.gardener::TEXT = current_user::TEXT));

DROP POLICY IF EXISTS files_gardener_policy ON files;
CREATE POLICY files_gardener_policy ON files TO role_gardener USING (personId::TEXT = current_user::TEXT);

-- Indexes
CREATE INDEX ON gardener_calendar(day);
CREATE INDEX ON gardener_calendar(gardener);
CREATE INDEX ON gardener_calendar(team);
CREATE INDEX ON gardener_calendar(teamnumber);
CREATE INDEX ON gardener_calendar(slot);
CREATE INDEX ON gardener_calendar(customerorder);
CREATE INDEX ON gardener_calendar(datereserved);
CREATE INDEX ON gardener_calendar(dateconfirmed);
CREATE INDEX ON gardener_calendar(team, day, slot, datereserved, dateconfirmed);
CREATE INDEX ON zipgeo(zipcode);
