-- As postgres, create user and database
CREATE USER i3s WITH PASSWORD 'i3s';
CREATE DATABASE i3s OWNER i3s;

-- Add pgcrypto extension to 'i3s'
-- Connect to i3s as postgres first
CREATE EXTENSION pgcrypto;

-- For filters "ILIKE '%filter%'"
CREATE EXTENSION pg_trgm;

-- As i3s
CREATE SEQUENCE i3s_ver_seq AS BIGINT;
CREATE SEQUENCE i3s_account_seq AS BIGINT MINVALUE 2111000001;

----------------------------------------- Status -----------------------------------------

CREATE TABLE i3s_status (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    status_code INTEGER NOT NULL,
    status_desc TEXT NOT NULL,
    status_desc_bm TEXT NOT NULL,
    is_active BOOLEAN NOT NULL,
    is_usable BOOLEAN NOT NULL,
    UNIQUE (status_code)
);

----------------------------------------- Countries -----------------------------------------

CREATE TABLE i3s_country (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    country_code_alpha_2 TEXT NOT NULL,
    country_code_alpha_3 TEXT NOT NULL,
    country_code INTEGER NOT NULL,
    country_name TEXT NOT NULL,
    region TEXT NOT NULL,
    sub_region TEXT NULL,
    inter_region TEXT NULL,
    dial_country_code TEXT NULL,
    region_code INTEGER NULL,
    sub_region_code INTEGER NULL,
    inter_region_code INTEGER NULL,
    currency_code TEXT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (ver),
    UNIQUE (country_code_alpha_2),
    UNIQUE (country_code_alpha_3),
    UNIQUE (country_code)
);

COMMENT ON COLUMN i3s_country.country_code IS 'Numeric country code.';
COMMENT ON COLUMN i3s_country.country_code_alpha_2 IS 'Two-character country code.';
COMMENT ON COLUMN i3s_country.country_code_alpha_3 IS 'Three-character country code.';
COMMENT ON COLUMN i3s_country.dial_country_code IS 'International dialing country code (e.g. +60).';
COMMENT ON COLUMN i3s_country.region IS 'Region.';
COMMENT ON COLUMN i3s_country.sub_region IS 'Sub-region.';
COMMENT ON COLUMN i3s_country.inter_region IS 'Intermediate region.';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_country()
RETURNS TRIGGER
AS $$
DECLARE
    dial_code_arr TEXT[];
    dial_code TEXT := NULL;
    dial_codes TEXT := NULL;
BEGIN
    -- Sanitise country codes
    NEW.country_code_alpha_2 = UPPER(TRIM(NEW.country_code_alpha_2));
    NEW.country_code_alpha_3 = UPPER(TRIM(NEW.country_code_alpha_3));

    -- Sanitise other fields
    NEW.country_name = TRIM(NEW.country_name);
    NEW.region = TRIM(NEW.region);
    NEW.sub_region = TRIM(NEW.sub_region);
    NEW.inter_region = TRIM(NEW.inter_region);

    -- Add + prefix to dialing comma-separated country code(s)
    -- 60 => +60
    -- 1-809,1-829,1-849 => +1-809,+1-829,+1-849
    dial_code_arr := STRING_TO_ARRAY(NEW.dial_country_code, ',');
    IF dial_code_arr IS NOT NULL THEN
        FOREACH dial_code IN ARRAY dial_code_arr
        LOOP
            dial_code := TRIM(dial_code);
            IF left(dial_code, 1) <> '+' THEN
                dial_code := '+' || dial_code;
            END IF;

            IF dial_codes IS NULL THEN
                dial_codes := dial_code;
            ELSE
                IF length(dial_codes) > 0 THEN
                    dial_codes := dial_codes || ',';
                END IF;
                dial_codes := dial_codes || dial_code;
            END IF;
        END LOOP;
    END IF;
    NEW.dial_country_code = dial_codes;

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_country BEFORE INSERT OR UPDATE
  ON i3s_country FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_country();

----------------------------------------- State groups (for indicating weekends) -----------------------------------------

CREATE TABLE i3s_state_group (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    state_group TEXT NOT NULL,
    weekend TEXT NOT NULL,
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (ver),
    UNIQUE (state_group)
);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_state_group()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise STATE GROUP
    NEW.state_group = UPPER(TRIM(NEW.state_group));

    -- Sanitise weekend
    NEW.weekend = TRIM(NEW.weekend);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_state_group BEFORE INSERT OR UPDATE
  ON i3s_state_group FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_state_group();

----------------------------------------- States of countries -----------------------------------------

CREATE TABLE i3s_state (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    country_code_alpha_2 TEXT NOT NULL,
    state_code INTEGER NOT NULL,
    state_group TEXT NULL,
    state_name TEXT NOT NULL,
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (country_code_alpha_2) REFERENCES i3s_country (country_code_alpha_2),
    FOREIGN KEY (state_group) REFERENCES i3s_state_group (state_group),
    UNIQUE (ver),
    UNIQUE (country_code_alpha_2, state_code)
);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_state()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise codes
    NEW.country_code_alpha_2 = UPPER(TRIM(NEW.country_code_alpha_2));
    NEW.state_group = UPPER(TRIM(NEW.state_group));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_state BEFORE INSERT OR UPDATE
  ON i3s_state FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_state();

----------------------------------------- Cities of countries -----------------------------------------

CREATE TABLE i3s_city (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    country_code_alpha_2 TEXT NOT NULL,
    state_code INTEGER NOT NULL,
    city_name TEXT NOT NULL,
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (country_code_alpha_2, state_code) REFERENCES i3s_state (country_code_alpha_2, state_code),
    UNIQUE (ver)
);

CREATE INDEX ON i3s_city (country_code_alpha_2, state_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_city()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise codes
    NEW.country_code_alpha_2 = UPPER(TRIM(NEW.country_code_alpha_2));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_city BEFORE INSERT OR UPDATE
  ON i3s_city FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_city();

----------------------------------------- Bank -----------------------------------------

CREATE TABLE i3s_bank (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    bank_code TEXT NOT NULL,
    bank_name TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    ver BIGINT NOT NULL,
    dt_lastlogin TIMESTAMP WITH TIME ZONE NULL,
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    UNIQUE (ver),
    UNIQUE (bank_code)
);

CREATE INDEX ON i3s_bank (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_bank()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise student ID
    NEW.bank_code = UPPER(TRIM(NEW.bank_code));

    -- Sanitise other fields
    NEW.bank_name = TRIM(NEW.bank_name);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_bank BEFORE INSERT OR UPDATE
  ON i3s_bank FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_bank();

----------------------------------------- Users -----------------------------------------

CREATE TABLE i3s_user (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    allow_login BOOLEAN NOT NULL DEFAULT TRUE,
    ver BIGINT NOT NULL,
    id_type INTEGER NULL,
    id_no TEXT NOT NULL,
    full_name TEXT NOT NULL,
    date_of_birth DATE NULL,
    nationality TEXT NULL,
    mother_name TEXT NULL,
    race CHAR(1) NULL,
    gender CHAR(1) NULL,
    addr_1 TEXT NULL,
    addr_2 TEXT NULL,
    addr_city TEXT NULL,
    addr_post TEXT NULL,
    addr_state_code INTEGER NULL,
    addr_country_code_alpha_2 TEXT NULL,
    mobile_number TEXT NULL,
    email TEXT NULL,
    email_verify_url TEXT NULL,
    email_verify_expiry TIMESTAMP WITH TIME ZONE NULL,
    is_email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    otp TEXT NULL,
    otp_verify_expiry TIMESTAMP WITH TIME ZONE NULL,
    is_otp_verified BOOLEAN NOT NULL DEFAULT FALSE,
    otp_tries INTEGER NOT NULL DEFAULT 0,
    social_url TEXT NULL,
    contact_person TEXT NULL,
    contact_phone TEXT NULL,
    photo_url TEXT NULL,
    photo_id_url TEXT NULL,
    occupation TEXT NULL,
    employer_name TEXT NULL,
    bank_code TEXT NULL,
    bank_account_no TEXT NULL,
    bank_account_name TEXT NULL,
    marketing_flag CHAR(1) NOT NULL DEFAULT 'N',
    dt_lastlogin TIMESTAMP WITH TIME ZONE NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (nationality) REFERENCES i3s_country (country_code_alpha_2),
    FOREIGN KEY (addr_country_code_alpha_2, addr_state_code) REFERENCES i3s_state (country_code_alpha_2, state_code),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (bank_code) REFERENCES i3s_bank (bank_code),
    UNIQUE (ver),
    UNIQUE (user_id),
    UNIQUE NULLS NOT DISTINCT (id_no, id_type)
);

COMMENT ON COLUMN i3s_user.id_type IS '1 = Identity card, 2 = Passport';
COMMENT ON COLUMN i3s_user.id_no IS 'I/C, passport or any other recognised identification number.';
COMMENT ON COLUMN i3s_user.race IS 'M = Malay, C = Chinese, I = Indian, O = Others';
COMMENT ON COLUMN i3s_user.gender IS 'M = Male, F = Female';

CREATE INDEX ON i3s_user (id_no);
CREATE INDEX ON i3s_user (nationality);
CREATE INDEX ON i3s_user (addr_country_code_alpha_2, addr_state_code);
CREATE INDEX ON i3s_user (status_code);

-- For queries "SELECT ... FROM i3s_user WHERE full_name ILIKE '%john%'"
CREATE INDEX ON i3s_user USING GIN (full_name gin_trgm_ops);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_user()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise student ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Sanitise other fields
    NEW.full_name = TRIM(NEW.full_name);
    NEW.race = UPPER(TRIM(NEW.race));
    NEW.gender = UPPER(TRIM(NEW.gender));
    NEW.addr_1 = TRIM(NEW.addr_1);
    NEW.addr_2 = TRIM(NEW.addr_2);
    NEW.addr_city = TRIM(NEW.addr_city);
    NEW.addr_post = TRIM(NEW.addr_post);
    NEW.addr_country_code_alpha_2 = UPPER(TRIM(NEW.addr_country_code_alpha_2));
    NEW.gender = UPPER(TRIM(NEW.gender));

    NEW.mobile_number = TRIM(NEW.mobile_number);
    -- Set OTP verified to FALSE if mobile number is changed
    IF NEW.mobile_number IS NOT NULL THEN
        IF OLD.mobile_number IS NULL THEN
            NEW.is_otp_verified := FALSE;
        ELSE
            NEW.is_otp_verified := UPPER(NEW.mobile_number) = UPPER(OLD.mobile_number);
        END IF;
    END IF;

    NEW.email = TRIM(NEW.email);
    -- Set email verified to FALSE if email is changed changed
    IF NEW.email IS NOT NULL THEN
        IF OLD.email IS NULL THEN
            NEW.is_email_verified := FALSE;
        ELSE
            NEW.is_email_verified := UPPER(NEW.email) = UPPER(OLD.email);
        END IF;
    END IF;

    NEW.social_url = TRIM(NEW.social_url);
    NEW.contact_person = TRIM(NEW.contact_person);
    NEW.contact_phone = TRIM(NEW.contact_phone);
    NEW.photo_url = TRIM(NEW.photo_url);
    NEW.photo_id_url = TRIM(NEW.photo_id_url);

    NEW.bank_code = UPPER(TRIM(NEW.bank_code));
    NEW.bank_account_no = TRIM(NEW.bank_account_no);
    NEW.bank_account_name = TRIM(NEW.bank_account_name);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_user BEFORE INSERT OR UPDATE
  ON i3s_user FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_user();

----------------------------------------- POS terminal status lookup -----------------------------------------

CREATE TABLE i3s_pos_term_status (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    term_status_code INTEGER NOT NULL,
    term_status TEXT NOT NULL,
    term_status_bm TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    is_usable BOOLEAN NOT NULL DEFAULT true,
    ver BIGINT NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (ver),
    UNIQUE (term_status_code)
);

COMMENT ON COLUMN i3s_pos_term_status.is_active IS 'Indicates if the POS terminal is active.';
COMMENT ON COLUMN i3s_pos_term_status.is_usable IS 'Indicates if the POS terminal is usable.';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_pos_term_status()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_pos_term_status BEFORE INSERT OR UPDATE
  ON i3s_pos_term_status FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_pos_term_status();

----------------------------------------- RFID status lookup -----------------------------------------

CREATE TABLE i3s_rfid_status (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    rfid_status_code INTEGER NOT NULL,
    rfid_status TEXT NOT NULL,
    rfid_status_bm TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    is_usable BOOLEAN NOT NULL DEFAULT true,
    ver BIGINT NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (ver),
    UNIQUE (rfid_status_code)
);

COMMENT ON COLUMN i3s_rfid_status.is_active IS 'Indicates if the RFID card is active.';
COMMENT ON COLUMN i3s_rfid_status.is_usable IS 'Indicates if the RFID card is usable.';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_rfid_status()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_rfid_status BEFORE INSERT OR UPDATE
  ON i3s_rfid_status FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_rfid_status();

----------------------------------------- Enrolment -----------------------------------------

CREATE TABLE i3s_enrolment (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    inventory_enabled BOOLEAN NOT NULL DEFAULT false,
    inventory_card BOOLEAN NOT NULL DEFAULT false,
    inventory_pos_terminal BOOLEAN NOT NULL DEFAULT false,
    inventory_rfid_reader BOOLEAN NOT NULL DEFAULT false,
    inventory_server BOOLEAN NOT NULL DEFAULT false,
    inventory_sim_card BOOLEAN NOT NULL DEFAULT false,
    inventory_switch BOOLEAN NOT NULL DEFAULT false,
    inventory_wap BOOLEAN NOT NULL DEFAULT false,
    school_reg_enabled BOOLEAN NOT NULL DEFAULT false,
    user_reg_enabled BOOLEAN NOT NULL DEFAULT false,
    user_reg_merchant BOOLEAN NOT NULL DEFAULT false,
    user_reg_parent BOOLEAN NOT NULL DEFAULT false,
    user_reg_staff BOOLEAN NOT NULL DEFAULT false,
    user_reg_student BOOLEAN NOT NULL DEFAULT false,
    card_assign_enabled BOOLEAN NOT NULL DEFAULT false,
    cust_svc_enabled BOOLEAN NOT NULL DEFAULT false,
    cust_svc_new_ticket BOOLEAN NOT NULL DEFAULT false,
    cust_svc_search_data BOOLEAN NOT NULL DEFAULT false,
    cust_svc_blacklist_card BOOLEAN NOT NULL DEFAULT false,
    report_enabled BOOLEAN NOT NULL DEFAULT false,
    report_inventory BOOLEAN NOT NULL DEFAULT false,
    report_school BOOLEAN NOT NULL DEFAULT false,
    report_user BOOLEAN NOT NULL DEFAULT false,
    report_card_assign BOOLEAN NOT NULL DEFAULT false,
    report_card_trx BOOLEAN NOT NULL DEFAULT false,
    report_merch_pos_sale BOOLEAN NOT NULL DEFAULT false,
    report_merch_online_sale BOOLEAN NOT NULL DEFAULT false,
    report_merch_settle BOOLEAN NOT NULL DEFAULT false,
    report_system_log BOOLEAN NOT NULL DEFAULT false,
    report_cust_svc BOOLEAN NOT NULL DEFAULT false,
    report_list_merch BOOLEAN NOT NULL DEFAULT false,
    sys_config_enabled BOOLEAN NOT NULL DEFAULT false,
    sys_config_user_mgmt BOOLEAN NOT NULL DEFAULT false,
    sys_config_enrol_setup BOOLEAN NOT NULL DEFAULT false,
    sys_config_school_holiday BOOLEAN NOT NULL DEFAULT false,
    sys_config_public_holiday BOOLEAN NOT NULL DEFAULT false,
    sys_config_sys_bcast BOOLEAN NOT NULL DEFAULT false,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (ver)
);

COMMENT ON COLUMN i3s_enrolment.inventory_enabled IS 'Inventory Setup - Enabled or Disabled';
COMMENT ON COLUMN i3s_enrolment.inventory_card IS 'Inventory Setup - Submenu Card';
COMMENT ON COLUMN i3s_enrolment.inventory_pos_terminal IS 'Inventory Setup - Submenu POS Terminal';
COMMENT ON COLUMN i3s_enrolment.inventory_rfid_reader IS 'Inventory Setup - Submenu RFID Reader';
COMMENT ON COLUMN i3s_enrolment.inventory_server IS 'Inventory Setup - Submenu Server';
COMMENT ON COLUMN i3s_enrolment.inventory_sim_card IS 'Inventory Setup - Submenu SIM Card';
COMMENT ON COLUMN i3s_enrolment.inventory_switch IS 'Inventory Setup - Submenu Switch';
COMMENT ON COLUMN i3s_enrolment.inventory_wap IS 'Inventory Setup - Submenu Wireless Access Point';
COMMENT ON COLUMN i3s_enrolment.school_reg_enabled IS 'School Registration - Enabled or Disabled';
COMMENT ON COLUMN i3s_enrolment.user_reg_enabled IS 'User Registration - Enabled or Disabled';
COMMENT ON COLUMN i3s_enrolment.user_reg_merchant IS 'User Registration - Submenu Merchant';
COMMENT ON COLUMN i3s_enrolment.user_reg_parent IS 'User Registration - Submenu Parent';
COMMENT ON COLUMN i3s_enrolment.user_reg_staff IS 'User Registration - Submenu Staff';
COMMENT ON COLUMN i3s_enrolment.user_reg_student IS 'User Registration - Submenu Student';
COMMENT ON COLUMN i3s_enrolment.card_assign_enabled IS 'Card Assignment - Enabled or Disabled';
COMMENT ON COLUMN i3s_enrolment.cust_svc_enabled IS 'Customer Service - Enabled or Disabled';
COMMENT ON COLUMN i3s_enrolment.cust_svc_new_ticket IS 'Customer Service - Submenu New Ticket';
COMMENT ON COLUMN i3s_enrolment.cust_svc_search_data IS 'Customer Service - Submenu Search Data';
COMMENT ON COLUMN i3s_enrolment.cust_svc_blacklist_card IS 'Customer Service - Submenu Blacklist Card';
COMMENT ON COLUMN i3s_enrolment.report_enabled IS 'Reports - Enabled or Disabled';
COMMENT ON COLUMN i3s_enrolment.report_inventory IS 'Reports - Submenu Inventory';
COMMENT ON COLUMN i3s_enrolment.report_school IS 'Reports - Submenu School';
COMMENT ON COLUMN i3s_enrolment.report_user IS 'Reports - Submenu User';
COMMENT ON COLUMN i3s_enrolment.report_card_assign IS 'Reports - Submenu Card Assignment';
COMMENT ON COLUMN i3s_enrolment.report_card_trx IS 'Reports - Submenu Card Transaction';
COMMENT ON COLUMN i3s_enrolment.report_merch_pos_sale IS 'Reports - Subment Merchant POS Sales';
COMMENT ON COLUMN i3s_enrolment.report_merch_online_sale IS 'Reports - Submenu Merchant Online Sales';
COMMENT ON COLUMN i3s_enrolment.report_merch_settle IS 'Reports - Submenu Merchant Settlement';
COMMENT ON COLUMN i3s_enrolment.report_system_log IS 'Reports - Submenu System Log';
COMMENT ON COLUMN i3s_enrolment.report_cust_svc IS 'Reports - Submenu Customer Service';
COMMENT ON COLUMN i3s_enrolment.report_list_merch IS 'Reports - Submenu Listing of Merchants';
COMMENT ON COLUMN i3s_enrolment.sys_config_enabled IS 'System Configuration - Enabled or Disabled';
COMMENT ON COLUMN i3s_enrolment.sys_config_user_mgmt IS 'System Configuration - Submenu User Management';
COMMENT ON COLUMN i3s_enrolment.sys_config_enrol_setup IS 'System Configuration - Submenu Enrolment Setup';
COMMENT ON COLUMN i3s_enrolment.sys_config_school_holiday IS 'System Configuration - Submenu School Holidays';
COMMENT ON COLUMN i3s_enrolment.sys_config_public_holiday IS 'System Configuration - Submenu Public Holidays';
COMMENT ON COLUMN i3s_enrolment.sys_config_sys_bcast IS 'System Configuration - Submenu System Broadcast';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_enrolment()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_enrolment BEFORE INSERT OR UPDATE
  ON i3s_enrolment FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_enrolment();

----------------------------------------- School type -----------------------------------------

CREATE TABLE i3s_school_type (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    school_type_code INTEGER NOT NULL,
    school_type TEXT NOT NULL,
    UNIQUE (school_type_code)
);

----------------------------------------- School -----------------------------------------

CREATE TABLE i3s_school (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    school_id TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    school_type_code INTEGER NOT NULL,
    school_name TEXT NOT NULL,
    principal_name TEXT NOT NULL,
    phone_number TEXT NOT NULL,
    fax_number TEXT NULL,
    email TEXT NOT NULL,
    website_url TEXT NOT NULL,
    result_url TEXT NULL,
    ver BIGINT NOT NULL,
    addr_1 TEXT NOT NULL,
    addr_2 TEXT NULL,
    addr_city TEXT NULL,
    addr_post TEXT NULL,
    addr_state_code INTEGER NOT NULL,
    addr_country_code_alpha_2 TEXT NOT NULL,
    coordinates TEXT NULL,
    register_date DATE NULL,
    pic_1_url TEXT NULL,
    pic_2_url TEXT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (school_type_code) REFERENCES i3s_school_type (school_type_code),
    FOREIGN KEY (addr_country_code_alpha_2, addr_state_code) REFERENCES i3s_state (country_code_alpha_2, state_code),
    UNIQUE (ver),
    UNIQUE (school_id)
);

CREATE INDEX ON i3s_school (status_code);
CREATE INDEX ON i3s_school (school_type_code);
CREATE INDEX ON i3s_school (addr_country_code_alpha_2, addr_state_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_school()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise other fields
    NEW.school_name = TRIM(NEW.school_name);
    NEW.addr_1 = TRIM(NEW.addr_1);
    NEW.addr_2 = TRIM(NEW.addr_2);
    NEW.addr_city = UPPER(TRIM(NEW.addr_city));
    NEW.addr_post = UPPER(TRIM(NEW.addr_post));
    NEW.addr_country_code_alpha_2 = UPPER(TRIM(NEW.addr_country_code_alpha_2));
    NEW.email = TRIM(NEW.email);
    NEW.website_url = TRIM(NEW.website_url);
    NEW.result_url = TRIM(NEW.result_url);
    NEW.principal_name = TRIM(NEW.principal_name);
    NEW.phone_number = TRIM(NEW.phone_number);
    NEW.fax_number = TRIM(NEW.fax_number);
    NEW.pic_1_url = TRIM(NEW.pic_1_url);
    NEW.pic_2_url = TRIM(NEW.pic_2_url);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_school BEFORE INSERT OR UPDATE
  ON i3s_school FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_school();

----------------------------------------- Public holiday -----------------------------------------

CREATE TABLE i3s_public_holiday (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    country_code_alpha_2 TEXT NOT NULL,
    state_code INTEGER NULL,
    holi_date DATE NOT NULL,
    descript TEXT NOT NULL,
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (country_code_alpha_2) REFERENCES i3s_country (country_code_alpha_2),
    FOREIGN KEY (country_code_alpha_2, state_code) REFERENCES i3s_state (country_code_alpha_2, state_code),
    UNIQUE NULLS NOT DISTINCT (country_code_alpha_2, holi_date, state_code)
);

COMMENT ON COLUMN i3s_public_holiday.state_code IS 'State code for state holiday or use null for national holidays.';

----------------------------------------- School holiday -----------------------------------------

CREATE TABLE i3s_school_holiday (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    school_id TEXT NOT NULL,
    holi_date DATE NOT NULL,
    descript TEXT NOT NULL,
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    UNIQUE (school_id, holi_date)
);

----------------------------------------- Class session -----------------------------------------

CREATE TABLE i3s_school_session (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    school_id TEXT NOT NULL,
    session_id TEXT NOT NULL,
    day_of_week INTEGER NOT NULL,
    session_desc TEXT NOT NULL,
    session_desc_bm TEXT NOT NULL,
    start_time TIME WITHOUT TIME ZONE NOT NULL,
    end_time TIME WITHOUT TIME ZONE NOT NULL,
    late_in_time TIME WITHOUT TIME ZONE NOT NULL,
    recess_end_time TIME WITHOUT TIME ZONE NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    UNIQUE (ver),
    UNIQUE (school_id, day_of_week, session_id),
    CHECK (day_of_week >= 0 AND day_of_week <= 7)
);

CREATE INDEX ON i3s_school_session (status_code);
CREATE INDEX ON i3s_school_session (school_id);

COMMENT ON COLUMN i3s_school_session.session_id IS 'Session ID (e.g. A for morning session, B for afternoon session)';
COMMENT ON COLUMN i3s_school_session.session_desc IS 'Session description (e.g. Morning Session, Afternoon Session)';
COMMENT ON COLUMN i3s_school_session.session_desc_bm IS 'Session description in Bahasa Malaysia (e.g. Sesi Pagi, Sesi Petang)';
COMMENT ON COLUMN i3s_school_session.day_of_week IS '0 = Any day, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday';
COMMENT ON COLUMN i3s_school_session.start_time IS 'Start of session';
COMMENT ON COLUMN i3s_school_session.end_time IS 'End of session';
COMMENT ON COLUMN i3s_school_session.late_in_time IS 'Late cut-off time';
COMMENT ON COLUMN i3s_school_session.recess_end_time IS 'End of recess';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_school_session()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise session ID
    NEW.session_id = UPPER(TRIM(NEW.session_id));

    -- Sanitise session descriptions
    NEW.session_desc = TRIM(NEW.session_desc);
    NEW.session_desc_bm = TRIM(NEW.session_desc_bm);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_school_session BEFORE INSERT OR UPDATE
  ON i3s_school_session FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_school_session();

----------------------------------------- Class -----------------------------------------

CREATE TABLE i3s_school_class (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    school_id TEXT NOT NULL,
    class_id TEXT NOT NULL,
    class_name TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    UNIQUE (ver),
    UNIQUE (school_id, class_id)
);

CREATE INDEX ON i3s_school_class (school_id);
CREATE INDEX ON i3s_school_class (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_school_class()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise class ID
    NEW.class_id = UPPER(TRIM(NEW.class_id));

    -- Sanitise class name
    NEW.class_name = TRIM(NEW.class_name);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_school_class BEFORE INSERT OR UPDATE
  ON i3s_school_class FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_school_class();

----------------------------------------- Link Class to Session -----------------------------------------

CREATE TABLE i3s_link_school_class_session (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    school_id TEXT NOT NULL,
    class_id TEXT NOT NULL,
    session_id TEXT NOT NULL,
    day_of_week INTEGER NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (school_id, class_id) REFERENCES i3s_school_class (school_id, class_id),
    FOREIGN KEY (school_id, day_of_week, session_id) REFERENCES i3s_school_session (school_id, day_of_week, session_id),
    UNIQUE (ver),
    UNIQUE (school_id, class_id, day_of_week, session_id)
);

CREATE INDEX ON i3s_link_school_class_session (status_code);
CREATE INDEX ON i3s_link_school_class_session (school_id, class_id);
CREATE INDEX ON i3s_link_school_class_session (school_id, session_id, day_of_week);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_link_school_class_session()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise class ID
    NEW.class_id = UPPER(TRIM(NEW.class_id));

    -- Sanitise session ID
    NEW.session_id = UPPER(TRIM(NEW.session_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_link_school_class_session BEFORE INSERT OR UPDATE
  ON i3s_link_school_class_session FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_link_school_class_session();

----------------------------------------- Link Teacher to Class -----------------------------------------

CREATE TABLE i3s_link_school_class_teacher (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    school_id TEXT NOT NULL,
    class_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (school_id, class_id) REFERENCES i3s_school_class (school_id, class_id),
    FOREIGN KEY (user_id) REFERENCES i3s_user (user_id),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    UNIQUE (ver),
    UNIQUE (school_id, class_id, user_id)
);

CREATE INDEX ON i3s_link_school_class_teacher (user_id);
CREATE INDEX ON i3s_link_school_class_teacher (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_link_school_class_teacher()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise class ID
    NEW.class_id = UPPER(TRIM(NEW.class_id));

    -- Sanitise user ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_link_school_class_teacher BEFORE INSERT OR UPDATE
  ON i3s_link_school_class_teacher FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_link_school_class_teacher();

----------------------------------------- Student -----------------------------------------

CREATE TABLE i3s_school_class_student (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    school_id TEXT NOT NULL,
    class_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (school_id, class_id) REFERENCES i3s_school_class (school_id, class_id),
    FOREIGN KEY (user_id) REFERENCES i3s_user (user_id),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    UNIQUE (ver),
    UNIQUE (school_id, class_id, user_id)
);

CREATE INDEX ON i3s_school_class_student (user_id);
CREATE INDEX ON i3s_school_class_student (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_school_class_student()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise class ID
    NEW.class_id = UPPER(TRIM(NEW.class_id));

    -- Sanitise user ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_school_class_student BEFORE INSERT OR UPDATE
  ON i3s_school_class_student FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_school_class_student();

----------------------------------------- Attendance status -----------------------------------------

CREATE TABLE i3s_attendance_status (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    attend_status_code INTEGER NOT NULL,
    attend_status_desc TEXT NOT NULL,
    attend_status_desc_bm TEXT NOT NULL,
    is_present BOOLEAN NOT NULL,
    is_half_day BOOLEAN NULL,
    is_late BOOLEAN NULL,
    UNIQUE (attend_status_code)
);

----------------------------------------- Reasons for absence -----------------------------------------

CREATE TABLE i3s_absence_reason (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    absence_code INTEGER NOT NULL,
    absence_reason TEXT NOT NULL,
    absence_reason_bm TEXT NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (absence_code)
);

----------------------------------------- Class attendance -----------------------------------------

CREATE TABLE i3s_school_class_attendance (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    school_id TEXT NOT NULL,
    class_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    day_of_week INTEGER NOT NULL,
    session_id TEXT NOT NULL,
    class_name TEXT NOT NULL,
    attend_date DATE NOT NULL,
    auto_attend_time TIME WITHOUT TIME ZONE NULL,
    teacher_attend_status_code INT NULL,
    teacher_attend_time TIME WITHOUT TIME ZONE NULL,
    is_rfid_attend BOOLEAN NULL,
    rfid_attend_time TIME WITHOUT TIME ZONE NULL,
    start_time TIME WITHOUT TIME ZONE NOT NULL,
    end_time TIME WITHOUT TIME ZONE NOT NULL,
    late_in_time TIME WITHOUT TIME ZONE NOT NULL,
    recess_end_time TIME WITHOUT TIME ZONE NOT NULL,
    absence_reason TEXT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (school_id, class_id, user_id) REFERENCES i3s_school_class_student (school_id, class_id, user_id),
    FOREIGN KEY (teacher_attend_status_code) REFERENCES i3s_attendance_status (attend_status_code),
    UNIQUE (ver),
    UNIQUE (school_id, class_id, user_id, attend_date)
);

COMMENT ON COLUMN i3s_school_class_attendance.attend_date IS 'Attendance date set by the auto-attendance process.';
COMMENT ON COLUMN i3s_school_class_attendance.auto_attend_time IS 'Attendance time set by the auto-attendance process.';
COMMENT ON COLUMN i3s_school_class_attendance.teacher_attend_status_code IS 'Attendance marked by teacher (see i3s_attendance_status).';
COMMENT ON COLUMN i3s_school_class_attendance.teacher_attend_time IS 'Attendance time set by the teacher.';
COMMENT ON COLUMN i3s_school_class_attendance.is_rfid_attend IS 'Attendance marked by RFID.';
COMMENT ON COLUMN i3s_school_class_attendance.rfid_attend_time IS 'RFID attendance time relayed from school server.';
COMMENT ON COLUMN i3s_school_class_attendance.class_name IS 'Value copied from i3s_school_class.';
COMMENT ON COLUMN i3s_school_class_attendance.start_time IS 'Value copied from i3s_school_session.';
COMMENT ON COLUMN i3s_school_class_attendance.end_time IS 'Value copied from i3s_school_session.';
COMMENT ON COLUMN i3s_school_class_attendance.late_in_time IS 'Value copied from i3s_school_session.';
COMMENT ON COLUMN i3s_school_class_attendance.recess_end_time IS 'Value copied from i3s_school_session.';
COMMENT ON COLUMN i3s_school_class_attendance.absence_reason IS 'Reason for absence.';

CREATE INDEX ON i3s_school_class_attendance (school_id, class_id, day_of_week, user_id);
CREATE INDEX ON i3s_school_class_attendance (user_id, attend_date, school_id, class_id);
CREATE INDEX ON i3s_school_class_attendance (teacher_attend_status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_school_class_attendance()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise class ID
    NEW.class_id = UPPER(TRIM(NEW.class_id));

    -- Sanitise user ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Sanitise user ID
    NEW.session_id = UPPER(TRIM(NEW.session_id));

    -- Sanitise class name
    NEW.class_name = TRIM(NEW.class_name);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_school_class_attendance BEFORE INSERT OR UPDATE
  ON i3s_school_class_attendance FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_school_class_attendance();

----------------------------------------- Club -----------------------------------------

CREATE TABLE i3s_school_club (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    school_id TEXT NOT NULL,
    club_id TEXT NOT NULL,
    club_name TEXT NOT NULL,
    descript TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    owner_user_id TEXT NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    FOREIGN KEY (owner_user_id) REFERENCES i3s_user (user_id),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    UNIQUE (ver),
    UNIQUE (school_id, club_id)
);

COMMENT ON COLUMN i3s_school_club.owner_user_id IS 'Owner / creator of the club.';
COMMENT ON COLUMN i3s_school_club.club_name IS 'Name of the club.';
COMMENT ON COLUMN i3s_school_club.descript IS 'A short description of the club.';

CREATE INDEX ON i3s_school_club (school_id);
CREATE INDEX ON i3s_school_club (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_school_club()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise club ID
    NEW.club_id = UPPER(TRIM(NEW.club_id));

    -- Sanitise club name
    NEW.club_name = TRIM(NEW.club_name);

    -- Sanitise other fields
    NEW.descript = TRIM(NEW.descript);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_school_club BEFORE INSERT OR UPDATE
  ON i3s_school_club FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_school_club();

----------------------------------------- Club attendance -----------------------------------------

CREATE TABLE i3s_school_club_attendance (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    school_id TEXT NOT NULL,
    club_id TEXT NOT NULL,
    club_name TEXT NOT NULL,
    descript TEXT NOT NULL,
    user_id TEXT NOT NULL,
    is_invite BOOLEAN NOT NULL,
    is_accepted BOOLEAN NOT NULL,
    is_declined BOOLEAN NOT NULL,
    attend_date DATE NULL,
    attend_time TIME WITHOUT TIME ZONE NULL,
    absence_reason TEXT NULL,
    dt_accept TIMESTAMP WITH TIME ZONE NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (school_id, club_id) REFERENCES i3s_school_club (school_id, club_id),
    FOREIGN KEY (user_id) REFERENCES i3s_user (user_id),
    UNIQUE (ver),
    UNIQUE (school_id, club_id, user_id, attend_date)
);

CREATE INDEX ON i3s_school_club_attendance (school_id, club_name, user_id, attend_date);
CREATE INDEX ON i3s_school_club_attendance (user_id, school_id, club_name, attend_date);
CREATE INDEX ON i3s_school_club_attendance (user_id, attend_date, school_id, club_name);

COMMENT ON COLUMN i3s_school_club_attendance.is_invite IS 'TRUE - this is an invitation.';
COMMENT ON COLUMN i3s_school_club_attendance.is_accepted IS 'TRUE - invitation accepted.';
COMMENT ON COLUMN i3s_school_club_attendance.club_name IS 'Value copied from i3s_school_club.';
COMMENT ON COLUMN i3s_school_club_attendance.descript IS 'Value copied from i3s_school_club.';
COMMENT ON COLUMN i3s_school_club_attendance.attend_date IS 'Date of attendance (set to null if absent).';
COMMENT ON COLUMN i3s_school_club_attendance.attend_time IS 'Time of attendance (set to null if absent).';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_school_club_attendance()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise club ID
    NEW.club_id = UPPER(TRIM(NEW.club_id));

    -- Sanitise user ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_school_club_attendance BEFORE INSERT OR UPDATE
  ON i3s_school_club_attendance FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_school_club_attendance();

----------------------------------------- Merchant type -----------------------------------------

CREATE TABLE i3s_merchant_type (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    merch_type_code INTEGER NOT NULL,
    merch_type TEXT NOT NULL,
    merch_type_bm TEXT NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (merch_type_code)
);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_merchant_type()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise type
    NEW.merch_type = TRIM(NEW.merch_type);
    NEW.merch_type_bm = TRIM(NEW.merch_type_bm);

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_merchant_type BEFORE INSERT OR UPDATE
  ON i3s_merchant_type FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_merchant_type();

----------------------------------------- Merchant -----------------------------------------

CREATE TABLE i3s_merchant (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    merch_type_code INTEGER NOT NULL,
    merch_id TEXT NOT NULL i3s_unique_id(),
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    merch_name TEXT NOT NULL,
    merch_reg_no TEXT NULL,
    bank_code TEXT NULL,
    bank_account_no TEXT NULL,
    bank_account_name TEXT NULL,
    addr_1 TEXT NULL,
    addr_2 TEXT NULL,
    addr_city TEXT NULL,
    addr_post TEXT NULL,
    addr_state_code INTEGER NOT NULL,
    addr_country_code_alpha_2 TEXT NOT NULL,
    person_in_charge TEXT NOT NULL,
    phone_number TEXT NOT NULL,
    fax_number TEXT NULL,
    email TEXT NULL,
    website_url TEXT NULL,
    pic_1_url TEXT NULL,
    pic_2_url TEXT NULL,
    mdr_rate DECIMAL (11, 2) NOT NULL DEFAULT 0,
    sc_rate DECIMAL (11, 2) NOT NULL DEFAULT 0,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (addr_country_code_alpha_2, addr_state_code) REFERENCES i3s_state (country_code_alpha_2, state_code),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (bank_code) REFERENCES i3s_bank (bank_code),
    FOREIGN KEY (merch_type_code) REFERENCES i3s_merchant_type (merch_type_code),
    UNIQUE (ver),
    UNIQUE (merch_id)
);

CREATE INDEX ON i3s_merchant (addr_country_code_alpha_2, addr_state_code);
CREATE INDEX ON i3s_merchant (status_code);
CREATE INDEX ON i3s_merchant (bank_code);
CREATE INDEX ON i3s_merchant (merch_type_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_merchant()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Sanitise bank code
    NEW.bank_code = UPPER(TRIM(NEW.bank_code));

    -- Sanitise other fields
    NEW.merch_name = TRIM(NEW.merch_name);
    NEW.merch_reg_no = UPPER(TRIM(NEW.merch_reg_no));
    NEW.bank_account_no = UPPER(TRIM(NEW.bank_account_no));
    NEW.bank_account_name = UPPER(TRIM(NEW.bank_account_name));
    NEW.addr_1 = TRIM(NEW.addr_1);
    NEW.addr_2 = TRIM(NEW.addr_2);
    NEW.addr_city = TRIM(NEW.addr_city);
    NEW.addr_post = TRIM(NEW.addr_post);
    NEW.addr_country_code_alpha_2 = UPPER(TRIM(NEW.addr_country_code_alpha_2));
    NEW.email = TRIM(NEW.email);
    NEW.website_url = TRIM(NEW.website_url);
    NEW.phone_number = TRIM(NEW.phone_number);
    NEW.fax_number = TRIM(NEW.fax_number);
    NEW.person_in_charge = TRIM(NEW.person_in_charge);
    NEW.pic_1_url = TRIM(NEW.pic_1_url);
    NEW.pic_2_url = TRIM(NEW.pic_2_url);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_merchant BEFORE INSERT OR UPDATE
  ON i3s_merchant FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_merchant();

----------------------------------------- Link Merchant to School -----------------------------------------

CREATE TABLE i3s_link_merchant_school (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    school_id TEXT NOT NULL,
    merch_id TEXT NOT NULL,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    FOREIGN KEY (merch_id) REFERENCES i3s_merchant (merch_id),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    UNIQUE (ver),
    UNIQUE (school_id, merch_id)
);

COMMENT ON TABLE i3s_link_merchant_school IS 'Links a merchant to a school.';

CREATE INDEX ON i3s_link_merchant_school (school_id);
CREATE INDEX ON i3s_link_merchant_school (merch_id);
CREATE INDEX ON i3s_link_merchant_school (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_link_merchant_school()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_link_merchant_school BEFORE INSERT OR UPDATE
  ON i3s_link_merchant_school FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_link_merchant_school();

----------------------------------------- User session -----------------------------------------

CREATE TABLE i3s_user_session (
	id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
	user_id TEXT NOT NULL,
    cl_addr TEXT NULL,
    cl_port INTEGER NULL,
	session_id TEXT NOT NULL,
	fcm_token TEXT NULL,
	apns_token TEXT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    dt_logout TIMESTAMP WITH TIME ZONE NULL,
	dev_os CHAR(1) NULL, -- Device operating system
	dev_ver TEXT NULL, -- Operating system version
    dev_mod TEXT NULL, -- Device model
	dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
	dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
	FOREIGN KEY (user_id) REFERENCES i3s_user (user_id),
    UNIQUE (session_id),
    UNIQUE (fcm_token),
    UNIQUE (apns_token)
);

COMMENT ON TABLE i3s_user_session IS 'This table stores and tracks user login sessions.';
COMMENT ON COLUMN i3s_user_session.fcm_token IS 'Firebase Cloud Messaging token.';
COMMENT ON COLUMN i3s_user_session.apns_token IS 'Apple Push Notification service token.';
COMMENT ON COLUMN i3s_user_session.dev_os IS 'W = Windows, M = MacOS, A = Android, I = iOS.';
COMMENT ON COLUMN i3s_user_session.dev_ver IS 'Operating system version.';
COMMENT ON COLUMN i3s_user_session.dev_mod IS 'Device model.';

CREATE INDEX ON i3s_user_session (user_id);

----------------------------------------- Link User to Merchant -----------------------------------------

CREATE TABLE i3s_link_user_merchant (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    merch_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (merch_id) REFERENCES i3s_merchant (merch_id),
    FOREIGN KEY (user_id) REFERENCES i3s_user (user_id),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    UNIQUE (ver),
    UNIQUE (merch_id, user_id)
);

COMMENT ON TABLE i3s_link_user_merchant IS 'Links a user to a merchant.';

CREATE INDEX ON i3s_link_user_merchant (user_id);
CREATE INDEX ON i3s_link_user_merchant (merch_id);
CREATE INDEX ON i3s_link_user_merchant (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_link_user_merchant()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise user ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_link_user_merchant BEFORE INSERT OR UPDATE
  ON i3s_link_user_merchant FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_link_user_merchant();

----------------------------------------- Link User to School -----------------------------------------

CREATE TABLE i3s_link_user_school (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id TEXT NOT NULL,
    school_id TEXT NOT NULL,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    staff_no TEXT NULL,
    student_no TEXT NULL,
    is_super_admin BOOLEAN NOT NULL DEFAULT FALSE,
    is_admin BOOLEAN NOT NULL DEFAULT FALSE,
    is_warden BOOLEAN NOT NULL DEFAULT FALSE,
    is_principal BOOLEAN NOT NULL DEFAULT FALSE,
    is_teacher BOOLEAN NOT NULL DEFAULT FALSE,
    is_staff BOOLEAN NOT NULL DEFAULT FALSE,
    is_student BOOLEAN NOT NULL DEFAULT FALSE,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (user_id) REFERENCES i3s_user (user_id),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    UNIQUE (ver),
    UNIQUE (school_id, user_id)
);

COMMENT ON TABLE i3s_link_user_school IS 'Links a user to a school.';

CREATE INDEX ON i3s_link_user_school (user_id);
CREATE INDEX ON i3s_link_user_school (school_id);
CREATE INDEX ON i3s_link_user_school (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_link_user_school()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise user ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_link_user_school BEFORE INSERT OR UPDATE
  ON i3s_link_user_school FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_link_user_school();

----------------------------------------- Relationship between two users -----------------------------------------

CREATE TABLE i3s_relationship (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    rel_code TEXT NOT NULL,
    descript TEXT NOT NULL,
    descript_bm TEXT NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (ver),
    UNIQUE (rel_code)
);

COMMENT ON TABLE i3s_relationship IS 'Relationship between users.';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_relationship()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise relationship code
    NEW.rel_code = UPPER(TRIM(NEW.rel_code));

    -- Sanitise descriptions
    NEW.descript = TRIM(NEW.descript);
    NEW.descript_bm = TRIM(NEW.descript_bm);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_relationship BEFORE INSERT OR UPDATE
  ON i3s_relationship FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_relationship();

----------------------------------------- Link Parent to Student (Users) -----------------------------------------

CREATE TABLE i3s_link_users (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id_from TEXT NOT NULL,
    user_id_to TEXT NOT NULL,
    rel_code TEXT NULL,
    is_guardian BOOLEAN NOT NULL DEFAULT FALSE,
    is_sibling BOOLEAN NOT NULL DEFAULT FALSE,
    is_spouse BOOLEAN NOT NULL DEFAULT FALSE,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY(user_id_from) REFERENCES i3s_user (user_id),
    FOREIGN KEY(user_id_to) REFERENCES i3s_user (user_id),
    FOREIGN KEY(rel_code) REFERENCES i3s_relationship (rel_code),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    UNIQUE (ver),
    UNIQUE (user_id_from, user_id_to)
);

COMMENT ON COLUMN i3s_link_users.user_id_from IS 'Typically refers to the parent (i.e. PARENT to CHILD).';
COMMENT ON COLUMN i3s_link_users.user_id_to IS 'Typically refers to the student.';

CREATE INDEX ON i3s_link_users (rel_code);
CREATE INDEX ON i3s_link_users (user_id_from);
CREATE INDEX ON i3s_link_users (user_id_to);
CREATE INDEX ON i3s_link_users (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_link_users()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise user IDs
    NEW.user_id_from = UPPER(TRIM(NEW.user_id_from));
    NEW.user_id_to = UPPER(TRIM(NEW.user_id_to));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_link_users BEFORE INSERT OR UPDATE
  ON i3s_link_users FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_link_users();

----------------------------------------- Product -----------------------------------------

CREATE TABLE i3s_product (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    merch_id TEXT NOT NULL,
    product_id TEXT NOT NULL DEFAULT i3s_unique_id(),
    title TEXT NOT NULL,
    descript TEXT NOT NULL,
    photo_url TEXT NULL,
    photo_mime_type TEXT NULL,
    photo_file TEXT NULL,
    sku TEXT NULL,
    price DECIMAL (11, 2) NOT NULL,
    bal_qty INTEGER NULL,
    is_food BOOLEAN NOT NULL,
    nutrition_info TEXT NULL,
    measure_unit TEXT NULL,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (ver),
    UNIQUE (merch_id, product_id),
    UNIQUE (merch_id, sku),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (merch_id) REFERENCES i3s_merchant (merch_id),
    CHECK (is_food = FALSE OR nutrition_info IS NOT NULL),
    CHECK (price >= 0),
    CHECK (bal_qty >= 0)
);

COMMENT ON COLUMN i3s_product.is_food IS 'TRUE = this is a food product (nutritional information is required). FALSE = this is not a food product';
COMMENT ON COLUMN i3s_product.bal_qty IS 'Balance quantity.';
COMMENT ON COLUMN i3s_product.measure_unit IS 'Unit of measurement (e.g. kg, cup, bottle, etc).';
COMMENT ON COLUMN i3s_product.nutrition_info IS 'Nutritional information.';

CREATE INDEX ON i3s_product (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_product()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Sanitise product ID
    NEW.product_id = UPPER(TRIM(NEW.product_id));

    -- Sanitise SKU
    NEW.sku = UPPER(TRIM(NEW.sku));

    -- Sanitise other fields
    NEW.title = TRIM(NEW.title);
    NEW.descript = TRIM(NEW.descript);
    NEW.nutrition_info = TRIM(NEW.nutrition_info);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_product BEFORE INSERT OR UPDATE
  ON i3s_product FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_product();

----------------------------------------- Menu -----------------------------------------

CREATE TABLE i3s_menu (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    school_id TEXT NOT NULL,
    category TEXT NOT NULL,
    merch_id TEXT NOT NULL,
    title TEXT NOT NULL,
    descript TEXT NOT NULL,
    photo_url TEXT NULL,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (merch_id) REFERENCES i3s_merchant (merch_id),
    UNIQUE (ver),
    UNIQUE (school_id, category, merch_id)
);

COMMENT ON COLUMN i3s_menu.category IS 'Nominally SHOP, STAFF or STUDENT.';

CREATE INDEX ON i3s_menu (merch_id);
CREATE INDEX ON i3s_menu (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_menu()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise category
    NEW.category = UPPER(TRIM(NEW.category));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Sanitise other fields
    NEW.title = TRIM(NEW.title);
    NEW.descript = TRIM(NEW.descript);
    NEW.photo_url = TRIM(NEW.photo_url);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_menu BEFORE INSERT OR UPDATE
  ON i3s_menu FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_menu();

----------------------------------------- Menu item -----------------------------------------

CREATE TABLE i3s_menu_item (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    school_id TEXT NOT NULL,
    category TEXT NOT NULL,
    merch_id TEXT NOT NULL,
    product_id TEXT NOT NULL,
    disp_order INTEGER NOT NULL,
    price DECIMAL (11, 2) NOT NULL,
    disc_pct DECIMAL (5, 2) NULL,
    disc_price DECIMAL (11, 2) NULL,
    which_disc INTEGER NOT NULL DEFAULT 0,
    title TEXT NOT NULL,
    descript TEXT NOT NULL,
    photo_url TEXT NULL,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE (ver),
    UNIQUE (school_id, category, merch_id, product_id),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (school_id, category, merch_id) REFERENCES i3s_menu (school_id, category, merch_id),
    FOREIGN KEY (merch_id, product_id) REFERENCES i3s_product (merch_id, product_id),
    CHECK (price >= 0),
    CHECK (disc_pct IS NULL OR (disc_pct >= 0 AND disc_pct <= 100)),
    CHECK (disc_price IS NULL OR disc_price < price),
    CHECK (which_disc = 1 AND disc_pct IS NOT NULL),
    CHECK (which_disc = 2 AND disc_price IS NOT NULL)
);

COMMENT ON COLUMN i3s_menu_item.category IS 'Nominally SHOP, STAFF or STUDENT.';
COMMENT ON COLUMN i3s_menu_item.disp_order IS 'Display order.';
COMMENT ON COLUMN i3s_menu_item.disc_pct IS 'Discount percentage (e.g. 5 = 5% discount).';
COMMENT ON COLUMN i3s_menu_item.disc_price IS 'Discounted price.';
COMMENT ON COLUMN i3s_menu_item.which_disc IS '0 = no discount. 1 = apply discount percentage. 2 = use discounted price.';
COMMENT ON COLUMN i3s_menu_item.price IS 'Copied from product but may be changed.';

CREATE INDEX ON i3s_menu_item (merch_id, product_id);
CREATE INDEX ON i3s_menu_item (status_code);
CREATE INDEX ON i3s_menu_item (disp_order);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_menu_item()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_menu_item BEFORE INSERT OR UPDATE
  ON i3s_menu_item FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_menu_item();

----------------------------------------- Account -----------------------------------------

CREATE TABLE i3s_account (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    account_id TEXT NOT NULL,
    merch_id TEXT NULL,
    user_id TEXT NULL,
    status_code INTEGER NOT NULL,
    account_no BIGINT NOT NULL DEFAULT nextval('i3s_account_seq'),
    ver BIGINT NOT NULL,
    descript TEXT NOT NULL,
    balance DECIMAL (15, 2) NOT NULL,
    min_balance DECIMAL (15, 2) NULL,
    max_balance DECIMAL (15, 2) NULL,
    daily_limit DECIMAL (15, 2) NULL,
    daily_limit_date DATE NULL,
    daily_limit_used DECIMAL (15, 2) NOT NULL DEFAULT 0,
    min_trx_limit DECIMAL (15, 2) NOT NULL DEFAULT 1,
    max_trx_limit DECIMAL (15, 2) NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (merch_id) REFERENCES i3s_merchant (merch_id),
    FOREIGN KEY (user_id) REFERENCES i3s_user (user_id),
    UNIQUE (ver),
    UNIQUE (user_id, account_no),
    UNIQUE (merch_id, account_no),
    UNIQUE (account_id),
    CHECK ((user_id IS NOT NULL AND merch_id IS NULL) OR (user_id IS NULL AND merch_id IS NOT NULL))
);

CREATE INDEX ON i3s_account (merch_id);
CREATE INDEX ON i3s_account (user_id);
CREATE INDEX ON i3s_account (status_code);

COMMENT ON TABLE i3s_account IS 'A account must belong to either a USER or a MERCHANT but not both.';
COMMENT ON COLUMN i3s_account.balance IS 'Positive = available / credit balance.';
COMMENT ON COLUMN i3s_account.daily_limit IS 'Maximum amount allowed to be spent in a day.';
COMMENT ON COLUMN i3s_account.daily_limit_date IS 'For tracking daily limit.';
COMMENT ON COLUMN i3s_account.daily_limit_used IS 'The amount used for the stated date.';
COMMENT ON COLUMN i3s_account.account_no IS 'The account number shown to the user.';
COMMENT ON COLUMN i3s_account.min_trx_limit IS 'Minimum amount per transaction.';
COMMENT ON COLUMN i3s_account.max_trx_limit IS 'Maximum amount per transaction.';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_account()
RETURNS TRIGGER
AS $$
DECLARE
    c INTEGER := 0;
BEGIN
    -- Sanitise user ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Sanitise account ID
    NEW.account_id = UPPER(TRIM(NEW.account_id));

    IF TG_OP = 'INSERT' THEN
        -- Ensure there is no more than one active account for a user
        IF NEW.user_id IS NOT NULL THEN
            -- Count the number of active RFID tags for the account
            SELECT count(*) FROM i3s_account a INNER JOIN i3s_status s
                ON a.status_code=s.status_code 
                WHERE a.user_id=NEW.user_id AND s.is_active INTO c;
            IF c > 0 THEN
                -- Too many active accounts for this user
                RAISE EXCEPTION 'Too many active accounts for user %', NEW.user_id;
            END IF;
        END IF;

        -- Ensure there is no more than one active account for a user
        IF NEW.merch_id IS NOT NULL THEN
            -- Count the number of active RFID tags for the account
            SELECT count(*) FROM i3s_account a INNER JOIN i3s_status s
                ON a.status_code=s.status_code 
                WHERE a.merch_id=NEW.merch_id AND s.is_active INTO c;
            IF c > 0 THEN
                -- Too many active accounts for this user
                RAISE EXCEPTION 'Too many active accounts for merchant %', NEW.merch_id;
            END IF;
        END IF;
    END IF;

    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_account BEFORE INSERT OR UPDATE
  ON i3s_account FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_account();

----------------------------------------- RFID -----------------------------------------

CREATE TABLE i3s_rfid (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    rfid_status_code INTEGER NOT NULL,
    rfid_tag TEXT NOT NULL,
    rfid_card_no TEXT NOT NULL,
    account_id TEXT NULL,
    card_standard TEXT NULL,
    card_type TEXT NULL,
    card_implement TEXT NULL,
    issue_date DATE NULL,
    valid_start_date DATE NULL,
    valid_end_date DATE NULL,
    is_replaced BOOLEAN NOT NULL DEFAULT false,
    replace_reason TEXT NULL,
    replace_date DATE NULL,
    ver BIGINT NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (account_id) REFERENCES i3s_account (account_id),
    FOREIGN KEY (rfid_status_code) REFERENCES i3s_rfid_status (rfid_status_code),
    UNIQUE (ver),
    UNIQUE (rfid_tag),
    UNIQUE (rfid_card_no)
);

COMMENT ON COLUMN i3s_rfid.card_standard IS 'e.g. ISO 14443.';
COMMENT ON COLUMN i3s_rfid.card_implement IS 'Card implementation (e.g. MIFARE).';
COMMENT ON COLUMN i3s_rfid.rfid_tag IS 'Tag / ID returned by the card reader (e.g. 79F9FCD0).';
COMMENT ON COLUMN i3s_rfid.rfid_card_no IS 'Number printed on the card by the manufacturer (e.g. 6060 0501 0000 3755).';
COMMENT ON COLUMN i3s_rfid.is_replaced IS 'TRUE = This card has been replaced and is no longer in use.';
COMMENT ON COLUMN i3s_rfid.replace_reason IS 'Reason for replacement.';
COMMENT ON COLUMN i3s_rfid.replace_date IS 'Date of replacement.';
COMMENT ON COLUMN i3s_rfid.issue_date IS 'Date of issuing the card.';
COMMENT ON COLUMN i3s_rfid.valid_start_date IS 'Validity date (start).';
COMMENT ON COLUMN i3s_rfid.valid_end_date IS 'Validity date (end).';

CREATE INDEX ON i3s_rfid (rfid_status_code);
CREATE INDEX ON i3s_rfid (account_id);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_rfid_bef()
RETURNS TRIGGER
AS $$
DECLARE
    c INTEGER := 0;
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise RFID tag and number
    NEW.rfid_tag = UPPER(TRIM(NEW.rfid_tag));
    NEW.rfid_card_no = UPPER(TRIM(NEW.rfid_card_no));

    -- Sanitise account ID
    NEW.account_id = UPPER(TRIM(NEW.account_id));

    -- Ensure there is no more than one active RFID tag for a account
    IF NEW.account_id IS NOT NULL THEN
        -- Count the number of active RFID tags for the account
        SELECT count(*) FROM i3s_rfid r INNER JOIN i3s_rfid_status s
            ON r.rfid_status_code=s.rfid_status_code 
            WHERE r.account_id=NEW.account_id AND s.is_active INTO c;
        IF c > 0 THEN
            -- Too many active RFID tags for this account
            RAISE EXCEPTION 'Too many active RFID tags for account %', NEW.account_id;
        END IF;
    END IF;

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_rfid_bef BEFORE INSERT OR UPDATE
  ON i3s_rfid FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_rfid_bef();

----------------------------------------- POS terminal -----------------------------------------

-- Is serial number unique enough? No need to include make and model?
CREATE TABLE i3s_pos_term (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    term_status_code INTEGER NOT NULL,
    term_model TEXT NOT NULL,
    term_serial_no TEXT NOT NULL,
    tag_no TEXT NOT NULL,
    merch_id TEXT NULL,
    descript TEXT NULL,
    ver BIGINT NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (term_status_code) REFERENCES i3s_pos_term_status (term_status_code),
    FOREIGN KEY (merch_id) REFERENCES i3s_merchant (merch_id),
    UNIQUE (ver),
    UNIQUE (term_serial_no)
);

CREATE INDEX ON i3s_pos_term (merch_id);
CREATE INDEX ON i3s_pos_term (term_status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_pos_term()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_pos_term BEFORE INSERT OR UPDATE
  ON i3s_pos_term FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_pos_term();

----------------------------------------- Rewards -----------------------------------------

CREATE TABLE i3s_reward_campaign (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    campaign_id TEXT NOT NULL DEFAULT i3s_unique_id(),
    school_id TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    reward_type CHAR(1) NOT NULL,
    campaign_start TIMESTAMP WITH TIME ZONE NOT NULL,
    campaign_end TIMESTAMP WITH TIME ZONE NOT NULL,
    descript TEXT NOT NULL,
    credits_alloc INTEGER NOT NULL,
    credits_used INTEGER NOT NULL DEFAULT 0,
    is_new_child_multiplier BOOLEAN NOT NULL,
    loyalty_calc_date DATE NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    UNIQUE (ver),
    UNIQUE (campaign_id),
    CHECK (reward_type IN ('N', 'R', 'P', 'L'))
);

COMMENT ON COLUMN i3s_reward_campaign.reward_type IS 'N = New registration. R = Reload. P = Performance (for student purchases). L = Loyalty.';
COMMENT ON COLUMN i3s_reward_campaign.school_id IS 'The school running the campaign.';
COMMENT ON COLUMN i3s_reward_campaign.campaign_start IS 'Start of the campaign.';
COMMENT ON COLUMN i3s_reward_campaign.campaign_end IS 'End of the campaign.';
COMMENT ON COLUMN i3s_reward_campaign.descript IS 'Description of the campaign.';
COMMENT ON COLUMN i3s_reward_campaign.credits_alloc IS 'The number of credits allocated for the campaign.';
COMMENT ON COLUMN i3s_reward_campaign.credits_used IS 'The number of credits used.';
COMMENT ON COLUMN i3s_reward_campaign.is_new_child_multiplier IS 'For new registrations: TRUE - Reward is multiplied by the number of children. FALSE - Reward is given only once.';
COMMENT ON COLUMN i3s_reward_campaign.loyalty_calc_date IS 'Date of calculating loyalty rewards.';

CREATE INDEX ON i3s_reward_campaign (school_id, campaign_id);
CREATE INDEX ON i3s_reward_campaign (status_code);
CREATE INDEX ON i3s_reward_campaign (reward_type);
CREATE INDEX ON i3s_reward_campaign (school_id, reward_type);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_reward_campaign()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise campaign ID
    NEW.campaign_id = UPPER(TRIM(NEW.campaign_id));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise reward type
    NEW.reward_type = UPPER(TRIM(NEW.reward_type));

    -- Sanitise description
    NEW.descript = TRIM(NEW.descript);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_reward_campaign BEFORE INSERT OR UPDATE
  ON i3s_reward_campaign FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_reward_campaign();

----------------------------------------- Reward tiers -----------------------------------------

CREATE TABLE i3s_reward_campaign_tier (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    campaign_id TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    principal_up_to DECIMAL (15, 2) NULL,
    pct_to_apply DECIMAL (5, 2) NOT NULL,
    pct_max_amount DECIMAL (17, 2) NOT NULL,
    flat_to_apply DECIMAL (15, 2) NOT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (campaign_id) REFERENCES i3s_reward_campaign (campaign_id),
    UNIQUE (ver)
);

COMMENT ON COLUMN i3s_reward_campaign_tier.campaign_id IS 'Unique identifier of the campaign.';
COMMENT ON COLUMN i3s_reward_campaign_tier.principal_up_to IS 'The ceiling amount (for determining the reward). May be null as a catch-all.';
COMMENT ON COLUMN i3s_reward_campaign_tier.pct_to_apply IS 'The percentage to apply (e.g. 5.00 = 5%).';
COMMENT ON COLUMN i3s_reward_campaign_tier.pct_max_amount IS 'The maximum amount calculated from percentage to apply.';
COMMENT ON COLUMN i3s_reward_campaign_tier.flat_to_apply IS 'The (absolute) amount to apply.';

CREATE INDEX ON i3s_reward_campaign_tier (campaign_id);
CREATE INDEX ON i3s_reward_campaign_tier (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_reward_campaign_tier()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise campaign ID
    NEW.campaign_id = UPPER(TRIM(NEW.campaign_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_reward_campaign_tier BEFORE INSERT OR UPDATE
  ON i3s_reward_campaign_tier FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_reward_campaign_tier();

----------------------------------------- Rewards given -----------------------------------------

CREATE TABLE i3s_reward (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    account_id TEXT NOT NULL,
    campaign_id TEXT NOT NULL,
    principal_amount DECIMAL (15, 2) NOT NULL,
    pct_to_apply DECIMAL (5, 2) NOT NULL,
    pct_max_amount DECIMAL (17, 2) NOT NULL,
    flat_to_apply DECIMAL (15, 2) NOT NULL,
    reward_amount DECIMAL (15, 2) NOT NULL,
    UNIQUE (ver),
    FOREIGN KEY (account_id) REFERENCES i3s_account (account_id),
    FOREIGN KEY (campaign_id) REFERENCES i3s_reward_campaign (campaign_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

COMMENT ON TABLE i3s_reward IS 'Stores rewards given to users.';
COMMENT ON COLUMN i3s_reward.account_id IS 'The account receiving the reward.';
COMMENT ON COLUMN i3s_reward.principal_amount IS 'The principal amount on which the reward is based.';
COMMENT ON COLUMN i3s_reward.pct_to_apply IS 'Copied from i3s_reward_campaign.';
COMMENT ON COLUMN i3s_reward.pct_max_amount IS 'Copied from i3s_reward_campaign.';
COMMENT ON COLUMN i3s_reward.flat_to_apply IS 'Copied from i3s_reward_campaign.';
COMMENT ON COLUMN i3s_reward.reward_amount IS 'The amount of reward.';

CREATE INDEX ON i3s_reward (account_id, campaign_id);
CREATE INDEX ON i3s_reward (campaign_id);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_reward()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise account ID
    NEW.account_id = UPPER(TRIM(NEW.account_id));

    -- Sanitise campaign ID
    NEW.campaign_id = UPPER(TRIM(NEW.campaign_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_reward BEFORE INSERT OR UPDATE
  ON i3s_reward FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_reward();

----------------------------------------- Transaction fees  -----------------------------------------

CREATE TABLE i3s_trx_fee (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    status_code INTEGER NOT NULL,
    trx_type CHAR(1) NOT NULL,
    descript TEXT NOT NULL,
    principal_up_to DECIMAL (15, 2) NULL,
    pct_method CHAR(1) NOT NULL,
    pct_to_apply DECIMAL (5, 2) NOT NULL,
    pct_max_amount DECIMAL (17, 2) NOT NULL,
    flat_method CHAR(1) NOT NULL,
    flat_to_apply DECIMAL (15, 2) NOT NULL,
    UNIQUE (ver),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    CHECK (trx_type IN ('R', 'P', 'S', 'T')),
    CHECK (pct_method IN ('N', 'A', 'S', 'B')),
    CHECK (flat_method IN ('N', 'A', 'S', 'B')),
    CHECK (pct_to_apply >= 0),
    CHECK (flat_to_apply >= 0),
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

COMMENT ON COLUMN i3s_trx_fee.trx_type IS 'Transaction type: R = reload, P = purchase, S = sale, T = transfer.';
COMMENT ON COLUMN i3s_trx_fee.descript IS 'Description of this transaction fee.';
COMMENT ON COLUMN i3s_trx_fee.principal_up_to IS 'The ceiling amount (for determining the tier). May be null as a catch-all.';
COMMENT ON COLUMN i3s_trx_fee.pct_method IS 'The method to apply percentage: N = no percentage applied. A = Debit principal + percentage fee, credit principal. S = Debit principal, credit principal - percentage fee. B = Debit principal + percentage fee, credit principal - percentage fee.';
COMMENT ON COLUMN i3s_trx_fee.pct_to_apply IS 'The percentage to apply.';
COMMENT ON COLUMN i3s_trx_fee.pct_max_amount IS 'The maximum amount calculated from percentage to apply.';
COMMENT ON COLUMN i3s_trx_fee.flat_method IS 'The method to apply a flat fee: N = no flat fee applied. A = Debit principal + flat fee, credit principal. S = Debit principal, credit principal - flat fee. B = Debit principal + flat fee, credit principal - flat fee.';
COMMENT ON COLUMN i3s_trx_fee.flat_to_apply IS 'The amount of flat fee to apply.';

CREATE INDEX ON i3s_trx_fee (status_code);
CREATE INDEX ON i3s_trx_fee (trx_type, principal_up_to);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_trx_fee()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise transaction type
    NEW.trx_type = UPPER(TRIM(NEW.trx_type));

    -- Sanitise description
    NEW.descript = TRIM(NEW.descript);

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_trx_fee BEFORE INSERT OR UPDATE
  ON i3s_trx_fee FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_trx_fee();

----------------------------------------- Account transactions -----------------------------------------

CREATE TABLE i3s_account_trx (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    ver_before_sync BIGINT NULL,
    account_id TEXT NOT NULL,
    trx_id TEXT NOT NULL,
    trx_type CHAR(1) NOT NULL,
    school_id TEXT NULL,
    rfid_tag TEXT NULL,
    rfid_card_no TEXT NULL,
    descript TEXT NOT NULL,
    amount DECIMAL (15, 2) NOT NULL,
    server_id TEXT NULL,
    merch_id TEXT NULL,
    user_id TEXT NULL,
    term_serial_no TEXT NULL,
    is_success BOOLEAN NOT NULL,
    err_reason TEXT NULL,
    dt_trx TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT transaction_timestamp(),
    is_sync BOOLEAN NOT NULL DEFAULT FALSE,
    dt_sync TIMESTAMP WITH TIME ZONE NULL,
    is_online BOOLEAN NOT NULL DEFAULT FALSE,
    redeem_date DATE NULL,
    redeem_account_id TEXT NULL,

    -- Transaction fees
    trx_fee DECIMAL (15, 2) NOT NULL,
    trx_fee_descript TEXT NULL,
    trx_fee_principal_up_to DECIMAL (15, 2) NULL,
    trx_fee_pct_method CHAR(1) NULL,
    trx_fee_pct_to_apply DECIMAL (5, 2) NULL,
    trx_fee_pct_max_amount DECIMAL (17, 2) NULL,
    trx_fee_flat_method CHAR(1) NULL,
    trx_fee_flat_to_apply DECIMAL (15, 2) NULL,

    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (rfid_tag) REFERENCES i3s_rfid (rfid_tag),
    FOREIGN KEY (rfid_card_no) REFERENCES i3s_rfid (rfid_card_no),
    FOREIGN KEY (account_id) REFERENCES i3s_account (account_id),
    FOREIGN KEY (redeem_account_id) REFERENCES i3s_account (account_id),
    FOREIGN KEY (term_serial_no) REFERENCES i3s_pos_term (term_serial_no),
    FOREIGN KEY (user_id) REFERENCES i3s_user (user_id),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    UNIQUE (ver),
    UNIQUE (account_id, trx_id),
    CHECK (trx_type IN ('P', 'S', 'T')),
    CHECK (is_online = FALSE OR redeem_account_id IS NOT NULL)
);

COMMENT ON COLUMN i3s_account_trx.school_id IS 'Contains the school ID for transactions taking place in a school.';
COMMENT ON COLUMN i3s_account_trx.rfid_tag IS 'Tag / ID returned by the card reader (e.g. 79F9FCD0).';
COMMENT ON COLUMN i3s_account_trx.rfid_card_no IS 'Number printed on the card by the manufacturer (e.g. 6060 0501 0000 3755).';
COMMENT ON COLUMN i3s_account_trx.server_id IS 'School server ID.';
COMMENT ON COLUMN i3s_account_trx.merch_id IS 'Merchant ID involved in the sale.';
COMMENT ON COLUMN i3s_account_trx.user_id IS 'Merchant user involved in the sale.';
COMMENT ON COLUMN i3s_account_trx.term_serial_no IS 'Serial number of the POS terminal.';
COMMENT ON COLUMN i3s_account_trx.trx_id IS 'A unique transaction ID (UUID generated by PostgreSQL).';
COMMENT ON COLUMN i3s_account_trx.amount IS 'The principal amount, not including any transaction fees. Positive (credit) to increase balance, negative (debit) to decrease balance.';
COMMENT ON COLUMN i3s_account_trx.trx_type IS 'P = Purchase. S = Sale. T = Transfer.';
COMMENT ON COLUMN i3s_account_trx.dt_trx IS 'Date and time of transaction (using transaction_timestamp() and not now()).';
COMMENT ON COLUMN i3s_account_trx.ver IS 'Version number set and used on enterprise server.';
COMMENT ON COLUMN i3s_account_trx.ver_before_sync IS 'Set by school servers before synchronising with the enterprise server.';
COMMENT ON COLUMN i3s_account_trx.is_success IS 'TRUE = transaction is successful.';
COMMENT ON COLUMN i3s_account_trx.err_reason IS 'The reason for failure.';
COMMENT ON COLUMN i3s_account_trx.is_sync IS 'Used by school servers to indicate the transaction needs to be synchronised to the enterprise server.';
COMMENT ON COLUMN i3s_account_trx.dt_sync IS 'Used by school servers to indicate date and time of last synchronisation.';
COMMENT ON COLUMN i3s_account_trx.is_online IS 'Indicates an online order, to be redeemed with a matching account ID on the redemption date.';
COMMENT ON COLUMN i3s_account_trx.redeem_date IS 'The date to redeem the order by the student using a account ID.';
COMMENT ON COLUMN i3s_account_trx.redeem_account_id IS 'Set when ordering, this order may be redeemed with a matching account ID. Required for online orders.';

-- Transaction fees
COMMENT ON COLUMN i3s_account_trx.trx_fee IS 'The transaction fee charged for this transaction.';
COMMENT ON COLUMN i3s_account_trx.trx_fee_descript IS 'Refer to i3s_trx_fee.';
COMMENT ON COLUMN i3s_account_trx.trx_fee_principal_up_to IS 'Refer to i3s_trx_fee.';
COMMENT ON COLUMN i3s_account_trx.trx_fee_pct_method IS 'Refer to i3s_trx_fee.';
COMMENT ON COLUMN i3s_account_trx.trx_fee_pct_to_apply IS 'Refer to i3s_trx_fee.';
COMMENT ON COLUMN i3s_account_trx.trx_fee_pct_max_amount IS 'Refer to i3s_trx_fee.';
COMMENT ON COLUMN i3s_account_trx.trx_fee_flat_method IS 'Refer to i3s_trx_fee.';
COMMENT ON COLUMN i3s_account_trx.trx_fee_flat_to_apply IS 'Refer to i3s_trx_fee.';

CREATE INDEX ON i3s_account_trx (rfid_tag);
CREATE INDEX ON i3s_account_trx (rfid_card_no);
CREATE INDEX ON i3s_account_trx (term_serial_no);
CREATE INDEX ON i3s_account_trx (user_id);
CREATE INDEX ON i3s_account_trx (school_id);
CREATE INDEX ON i3s_account_trx (ver_before_sync);
CREATE INDEX ON i3s_account_trx (is_online, redeem_date, redeem_account_id);
CREATE INDEX ON i3s_account_trx (is_sync, dt_sync);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_account_trx()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise account ID
    NEW.account_id = UPPER(TRIM(NEW.account_id));
    NEW.redeem_account_id = UPPER(TRIM(NEW.redeem_account_id));

    -- Sanitise transaction ID
    NEW.trx_id = UPPER(TRIM(NEW.trx_id));

    -- Sanitise RFID tag
    NEW.rfid_tag = UPPER(TRIM(NEW.rfid_tag));

    -- Sanitise POS terminal ID
    NEW.term_serial_no = UPPER(TRIM(NEW.term_serial_no));

    -- Sanitise user ID
    NEW.user_id = UPPER(TRIM(NEW.user_id));

    -- Sanitise server ID
    NEW.server_id = UPPER(TRIM(NEW.server_id));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_account_trx BEFORE INSERT OR UPDATE
  ON i3s_account_trx FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_account_trx();

----------------------------------------- Account transaction items -----------------------------------------

CREATE TABLE i3s_account_trx_item (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    account_id TEXT NOT NULL,
    trx_id TEXT NOT NULL,

    -- Product and menu
    school_id TEXT NOT NULL,
    category TEXT NOT NULL,
    merch_id TEXT NOT NULL,
    product_id TEXT NOT NULL,

    item_desc TEXT NOT NULL,
    item_price DECIMAL (11, 2) NOT NULL,
    quantity INTEGER NOT NULL,
    nutrition_info TEXT NULL,
    dt_redeem TIMESTAMP WITH TIME ZONE NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (account_id, trx_id) REFERENCES i3s_account_trx (account_id, trx_id),
    FOREIGN KEY (school_id, category, merch_id, product_id) REFERENCES i3s_menu_item (school_id, category, merch_id, product_id)
);


COMMENT ON COLUMN i3s_account_trx_item.dt_redeem IS 'Timestamp of redemption (null means the item has not been redeemed yet). Use this in conjunction with i3s_account_trx to determine if the order is fully redeemed.';
COMMENT ON COLUMN i3s_account_trx_item.category IS 'Nominally SHOP, STAFF or STUDENT.';

CREATE INDEX ON i3s_account_trx_item (account_id, trx_id);
CREATE INDEX ON i3s_account_trx_item (school_id, category, merch_id, product_id);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_account_trx_item()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    -- Sanitise account ID
    NEW.account_id = UPPER(TRIM(NEW.account_id));

    -- Sanitise transaction ID
    NEW.trx_id = UPPER(TRIM(NEW.trx_id));

    -- Sanitise school ID
    NEW.school_id = UPPER(TRIM(NEW.school_id));

    -- Sanitise merchant ID
    NEW.merch_id = UPPER(TRIM(NEW.merch_id));

    -- Sanitise menu ID
    NEW.menu_id = UPPER(TRIM(NEW.menu_id));

    -- Sanitise item SKU
    NEW.item_sku = UPPER(TRIM(NEW.item_sku));

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_account_trx_item BEFORE INSERT OR UPDATE
  ON i3s_account_trx_item FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_account_trx_item();

----------------------------------------- Bulletins -----------------------------------------

CREATE TABLE i3s_bulletin (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ver BIGINT NOT NULL,
    bulletin_type INTEGER NOT NULL,
    school_id TEXT NOT NULL,
    class_id TEXT NULL,
    club_id TEXT NULL,
    bulletin_id TEXT NOT NULL DEFAULT i3s_unique_id(),
    bulletin_title TEXT NOT NULL,
    bulletin_content TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    author TEXT NULL,
    attach_file TEXT NULL,
    attach_url TEXT NULL,
    attach_mime_type TEXT NULL,
    created_by TEXT NULL REFERENCES i3s_user (user_id),
    updated_by TEXT NULL REFERENCES i3s_user (user_id),
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    dt_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (status_code) REFERENCES i3s_status (status_code),
    FOREIGN KEY (author) REFERENCES i3s_user (user_id),
    FOREIGN KEY (school_id, class_id) REFERENCES i3s_school_class (school_id, class_id),
    FOREIGN KEY (school_id, club_id) REFERENCES i3s_school_club (school_id, club_id),
    UNIQUE (ver),
    UNIQUE (bulletin_id),
    CHECK (bulletin_type >= 1 AND bulletin_type <= 3),
    CHECK (bulletin_type <> 1 OR (bulletin_type = 1 AND class_id IS NULL AND club_id IS NULL)),
    CHECK (bulletin_type <> 2 OR (bulletin_type = 2 AND class_id IS NOT NULL AND club_id IS NULL)),
    CHECK (bulletin_type <> 3 OR (bulletin_type = 3 AND class_id IS NULL AND club_id IS NOT NULL))
);

COMMENT ON COLUMN i3s_bulletin.bulletin_type IS '1=School bulletin. 2=Class bulletin. 3=Club bulletin.';

CREATE INDEX ON i3s_bulletin (school_id, class_id);
CREATE INDEX ON i3s_bulletin (school_id, club_id);
CREATE INDEX ON i3s_bulletin (author);
CREATE INDEX ON i3s_bulletin (status_code);

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_bulletin()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise 'created by' and 'updated by' user IDs
    NEW.created_by = UPPER(TRIM(NEW.created_by));
    NEW.updated_by = UPPER(TRIM(NEW.updated_by));

    NEW.school_id = UPPER(TRIM(NEW.school_id));
    NEW.class_id = UPPER(TRIM(NEW.class_id));
    NEW.club_id = UPPER(TRIM(NEW.club_id));

    -- Increment the version number
    NEW.ver = nextval('i3s_ver_seq');

    -- Update time
    NEW.dt_update = now();

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_bulletin BEFORE INSERT OR UPDATE
  ON i3s_bulletin FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_bulletin();

----------------------------------------- Account transaction items -----------------------------------------

CREATE TABLE i3s_activity_log (
    id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    category TEXT NOT NULL,
    severity INTEGER NOT NULL,
    school_id TEXT NULL,
    merch_id TEXT NULL,
    user_id TEXT NULL,
    cl_addr TEXT NULL,
    cl_port INTEGER NULL,
    descript TEXT NOT NULL,
    dt_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (school_id) REFERENCES i3s_school (school_id),
    FOREIGN KEY (merch_id) REFERENCES i3s_merchant (merch_id),
    FOREIGN KEY (user_id) REFERENCES i3s_user (user_id)
);

CREATE INDEX ON i3s_activity_log (school_id);
CREATE INDEX ON i3s_activity_log (merch_id);
CREATE INDEX ON i3s_activity_log (user_id);
CREATE INDEX ON i3s_activity_log (category);
CREATE INDEX ON i3s_activity_log (severity);
CREATE INDEX ON i3s_activity_log (dt_create);
CREATE INDEX ON i3s_activity_log (cl_addr, cl_port);

COMMENT ON COLUMN i3s_activity_log.category IS 'Category of activity (e.g. AUTH, ADMIN).';
COMMENT ON COLUMN i3s_activity_log.severity IS '0 = Success, 1 = Warning, 2 = Error';

-- This is a trigger function
CREATE OR REPLACE FUNCTION i3s_sanitise_activity_log()
RETURNS TRIGGER
AS $$
BEGIN
    -- Sanitise category
    NEW.category = UPPER(TRIM(NEW.category));

    -- Sanitise IP address
    NEW.cl_addr = UPPER(TRIM(NEW.cl_addr));

    -- Sanitise description
    NEW.descript = TRIM(NEW.descript);

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER i3s_trig_activity_log BEFORE INSERT OR UPDATE
  ON i3s_activity_log FOR EACH ROW EXECUTE PROCEDURE i3s_sanitise_activity_log();

