BEGIN;

CREATE TABLE accounts_basics

(

  id SERIAL PRIMARY KEY,

  country text NOT NULL DEFAULT 'US'::text,

  email text NOT NULL,

  password_reset bool DEFAULT false,

  public_handle varchar(32) NOT NULL,

  valid_email bool NOT NULL DEFAULT false,

  modified_by int4,

  event_type text

); 





CREATE TABLE credit_card_type

(

  id SERIAL PRIMARY KEY,

  credit_card_type text NOT NULL,

  CONSTRAINT ck_cc_type UNIQUE (credit_card_type)

); 



CREATE TABLE credit_card

(

  id  SERIAL PRIMARY KEY,

  account_id int4 NOT NULL,

  profile_id int4 NOT NULL,

  expires date NOT NULL,

  active bool,

  cc_number varchar(64),

  credit_card_type text NOT NULL,

  billing_name varchar(30) NOT NULL,

  modified_by int4,

  event_type text,

  CONSTRAINT "$1" FOREIGN KEY (account_id)

      REFERENCES accounts_basics (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE,

  CONSTRAINT fk_cc_type FOREIGN KEY (credit_card_type)

      REFERENCES credit_card_type (credit_card_type) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

);





CREATE TABLE credit_card_audit

(

  credit_card_audit_id SERIAL PRIMARY KEY,

  actor text NOT NULL DEFAULT "current_user"(),

  "action" text NOT NULL,

  credit_card_action_time timestamptz NOT NULL DEFAULT now(),

  event_type text,

  modified_by int4,

  account_id int4,

   credit_card_old credit_card,

  credit_card_new credit_card,

  CONSTRAINT credit_card_audit_action_check CHECK ("action" = 'INSERT'::text OR "action" = 'UPDATE'::text OR "action" = 'DELETE'::text),

  CONSTRAINT credit_card_audit_modified_by_fkey FOREIGN KEY (modified_by)

      REFERENCES accounts_basics (id) MATCH FULL

      ON UPDATE NO ACTION ON DELETE NO ACTION,

   CONSTRAINT credit_card_audit_account_id_fkey FOREIGN KEY (account_id)

      REFERENCES accounts_basics (id) MATCH FULL

      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED

); 



CREATE OR REPLACE FUNCTION audit_credit_card ()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

DECLARE

rows_affected INTEGER;

BEGIN

PERFORM tablename

FROM pg_tables

WHERE tablename = TG_RELNAME || '_audit';

IF NOT FOUND THEN

    RAISE EXCEPTION 'No audit table found for %', TG_RELNAME;

END IF;

IF TG_OP = 'INSERT' THEN

    INSERT INTO public.credit_card_audit (action, event_type, modified_by, credit_card_new)

    VALUES ('INSERT', NEW.event_type, NEW.modified_by, NEW );

ELSIF TG_OP = 'UPDATE' THEN

    INSERT INTO public.credit_card_audit (action, event_type, modified_by, credit_card_old, credit_card_new)

    VALUES ('UPDATE', NEW.event_type, NEW.modified_by, OLD , NEW );

ELSIF TG_OP = 'DELETE' THEN

    INSERT INTO public.credit_card_audit (action, event_type, modified_by, credit_card_old)

    VALUES ('DELETE', OLD.event_type, OLD.modified_by, OLD );

ELSE

    RAISE EXCEPTION 'TG_OP is none of INSERT, UPDATE or DELETE.';

END IF;

GET DIAGNOSTICS rows_affected = ROW_COUNT;

IF rows_affected = 1 THEN

    IF TG_OP IN ('INSERT', 'UPDATE') THEN

        RETURN NEW;

    ELSE

        RETURN OLD;

    END IF;

ELSE

    RAISE EXCEPTION 'INSERT failed on public.credit_card_audit';

END IF;

END;

$$;



CREATE TRIGGER audit_credit_card

  BEFORE INSERT OR UPDATE OR DELETE

  ON credit_card

  FOR EACH ROW

  EXECUTE PROCEDURE audit_credit_card();



CREATE INDEX credit_card_audit_event_type_idx

  ON credit_card_audit

  USING btree

  (event_type);



CREATE INDEX credit_card_audit_modified_by_idx

  ON credit_card_audit

  USING btree

  (modified_by);





CREATE OR REPLACE RULE credit_card_audit_no_delete AS

    ON DELETE TO credit_card_audit DO INSTEAD NOTHING;



CREATE OR REPLACE RULE credit_card_audit_no_update AS

    ON UPDATE TO credit_card_audit DO INSTEAD NOTHING;



CREATE OR REPLACE VIEW cc_with_id_view AS 

 SELECT cc.id, cc.account_id, cc.profile_id, cc.expires, cc.active, cc.cc_number, cct.id AS credit_card_type_id

   FROM credit_card cc

   JOIN credit_card_type cct USING (credit_card_type);



INSERT INTO credit_card_type (credit_card_type) VALUES ('fake');   

INSERT INTO accounts_basics ( email, public_handle, modified_by, event_type ) 

  VALUES ('nobody@nowhere.int', 'Dude567', 1, 'Test');

INSERT INTO credit_card (account_id, profile_id, expires, active, cc_number, credit_card_type, billing_name, modified_by, event_type)

  VALUES (1, 1, '12/12/2020', 't', '1234567890', 'fake', 'The Dude', 1, 'test');

INSERT INTO credit_card (account_id, profile_id, expires, active, cc_number, credit_card_type, billing_name, event_type)

  VALUES (1, 1, '1/15/2006', 'f', '1234567890', 'fake', 'Jeff Lebowski', 'test');

COMMIT;