p4JqSTTH

1## Program, Promotion, Member and Transaction
2
3drop table if exists "transaction";
4drop table if exists "transaction_type";
5
6drop table if exists "member";
7drop table if exists "member_identification_type";
8
9drop table if exists "rule_field_operator_map";
10
11drop table if exists "rule_criteria";
12drop table if exists "rule_operator";
13
14drop table if exists "rule_point";
15drop table if exists "rule_point_type";
16
17drop table if exists "rule_field";
18drop table if exists "rule_field_type";
19
20drop table if exists "rule";
21drop table if exists "rule_type";
22
23drop table if exists "promotion";
24drop table if exists "program";
25
26create table program (
27    id bigserial not null primary key,
28    name character varying(255) not null,
29    description text,
30    status boolean not null default 't'
31);
32
33create table promotion (
34    id bigserial not null primary key,
35    programId bigint not null references "program" (id),
36    name character varying(255) not null,
37    initialDate date not null,
38    finalDate date not null,
39    status boolean not null default 't'
40);
41
42create table member_identification_type (
43    id bigserial not null primary key,
44    name character varying(255) not null
45);
46
47create table "member" (
48    id bigserial not null primary key,
49    programId bigint not null references "program" (id),
50    memberIdentificationTypeId bigint not null references "member_identification_type" (id),
51    identificationNumber character varying(255) not null,
52    firstName character varying(255) not null,
53    lastName character varying(255) not null,
54    email character varying(255) not null,
55    -- gender 1 male, 0 female... hard coded
56    gender smallint,
57    birthdate date,
58    points bigint
59);
60
61create table transaction_type (
62    id bigserial not null primary key,
63    name character varying(255) not null
64);
65
66create table transaction (
67    id bigserial not null primary key,
68    transactionTypeId bigint not null references "transaction_type" (id),
69    memberId bigint not null references "member" (id),
70    purchaseValue double precision not null,
71    stamp timestamp not null,
72    code character varying(255) not null
73);
74
75insert into member_identification_type (name) values ('Passport'), ('Mobile'); 
76
77insert into transaction_type (name) values ('Purchase'),('Referral - Step 1'),('Referral - Step 2'),('Registration'),('Code Insert');
78
79
80
81
82
83
84
85
86
87create table "rule_type" (
88    id bigserial not null primary key,
89    name character varying(255) not null
90);
91
92create table "rule" (
93    id bigserial not null primary key,
94    promotionId bigint not null references "promotion" (id),
95    ruleTypeId bigint not null references "rule_type" (id),
96    name character varying(255) not null,
97    status boolean not null default 't'
98);
99
100create table "rule_operator" (
101    id bigserial not null primary key,
102    name character varying(255) not null
103);
104
105create table "rule_field_type" (
106    id bigserial not null primary key,
107    name character varying(255) not null
108);
109
110create table "rule_field" (
111    id bigserial not null primary key,
112    ruleFieldTypeId bigint not null references "rule_field_type" (id), 
113    parent bigint not null default 0,
114    name character varying(255) not null,
115    "table" character varying(255) default null,
116    "column" character varying(255) default null
117);
118
119create table "rule_criteria" (
120    id bigserial not null primary key,
121    ruleId bigint not null references "rule" (id),
122    ruleFieldId bigint not null references "rule_field" (id),
123    ruleOperatorId bigint not null references "rule_operator" (id),
124    "value" character varying(255) not null
125);
126
127create table "rule_point_type" (
128    id bigserial not null primary key,
129    name character varying(255) not null
130);
131
132create table "rule_point" (
133    id bigserial not null primary key,
134    ruleId bigint not null references "rule" (id),
135    rulePointTypeId bigint not null references "rule_point_type" (id),
136    "value" character varying(255) not null,
137    monthsUntillExpir int  
138);
139
140create table "rule_field_operator_map" (
141    ruleFieldId bigint not null references "rule_field" (id),
142    ruleOperatorId bigint not null references "rule_operator" (id),
143    primary key (ruleFieldId, ruleOperatorId)  
144);
145
146insert into "rule_type" (name) values ('Best Option'), ('No Competition');
147
148insert into "rule_operator" (name) values ('equals'),('not equals'),('less than'),('less or equal'),('greater than'),('greater or equal');
149
150insert into "rule_field_type" (name) values ('criteria'),('point');
151
152insert into "rule_field" (ruleFieldTypeId, parent, name, "table", "column") values (1, 0, 'Transaction Approved', 't1', 'c1'),(1, 0, 'Transaction Member', null, null),(1, 2, 'Member Email', 't2', 'c2'),(1, 2, 'Member Age', 't3', 'c3'),(1, 2, 'Member Birthday', 't4', 'c4'),(1, 0, 'Transaction Cost', 't5', 'c5'),(2, 0, 'Transaction Cost', 't6', 'c6'),(2, 0, 'Transaction Point Cost', 't7', 'c7'),(2, 0, 'Transaction Purchase Value', 't8', 'c8'),(2, 0, 'Transaction Revenue', 't9', 'c9');
153
154insert into "rule_point_type" (name)  values ('None'),('Fix Value'),('Factor'),('Multiplier'),('Custom Formula');
155
156insert into "rule_field_operator_map" (ruleFieldId, ruleOperatorId) values 
157(1, 1),(1, 2),
158(3, 1),(3, 2),(3, 3),(3, 4), (3, 5),
159(4, 1),(4, 2),(4, 3),(4, 4), (4, 5),
160(5, 1),(5, 2),(5, 3),(5, 4), (5, 5),
161(6, 1),(6, 2),(6, 3),(6, 4), (6, 5);