 
 
 --
+-- Name: fk__matches_project_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE INDEX fk__matches_project_id ON matches USING btree (project_id);
+
+
+--
+-- Name: fk__matches_user_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE INDEX fk__matches_user_id ON matches USING btree (user_id);
+
+
+--
+-- Name: fk__matchings_contribution_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE INDEX fk__matchings_contribution_id ON matchings USING btree (contribution_id);
+
+
+--
+-- Name: fk__matchings_match_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE INDEX fk__matchings_match_id ON matchings USING btree (match_id);
+
+
+--
 -- Name: fk__notifications_channel_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
 --
 

--

CREATE VIEW project_totals AS
    SELECT contributions.project_id, sum(contributions.value) AS pledged, ((sum(contributions.value) / projects.goal) * (100)::numeric) AS progress, sum(contributions.payment_service_fee) AS total_payment_service_fee, count(*) AS total_contributions, (sum(contributions.value) * (SELECT (configurations.value)::numeric AS value FROM configurations WHERE (configurations.name = 'platform_fee'::text))) AS platform_fee, (sum((contributions.value - CASE WHEN contributions.payment_service_fee_paid_by_user THEN (0)::numeric ELSE contributions.payment_service_fee END)) - (sum(contributions.value) * (SELECT (configurations.value)::numeric AS value FROM configurations WHERE (configurations.name = 'platform_fee'::text)))) AS net_amount FROM (contributions JOIN projects ON ((contributions.project_id = projects.id))) WHERE ((contributions.state)::text = ANY (ARRAY[('confirmed'::character varying)::text, ('refunded'::character varying)::text, ('requested_refund'::character varying)::text])) GROUP BY contributions.project_id, projects.goal;


--

--

ALTER SEQUENCE configurations_id_seq OWNED BY configurations.id;


--
-- Name: matches; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE matches (
    id integer NOT NULL,
    project_id integer NOT NULL,
    user_id integer,
    starts_at date NOT NULL,
    finishes_at date NOT NULL,
    value_unit numeric NOT NULL,
    value numeric,
    completed boolean DEFAULT false NOT NULL,
    payment_id character varying(255),
    payment_choice text,
    payment_method text,
    payment_token text,
    payment_service_fee numeric DEFAULT 0.0,
    payment_service_fee_paid_by_user boolean DEFAULT true,
    state character varying(255),
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    key character varying(255),
    confirmed_at timestamp without time zone
);


--
-- Name: matchings; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE matchings (
    id integer NOT NULL,
    match_id integer,
    contribution_id integer,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);


--
-- Name: contributions_fees; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW contributions_fees AS
    SELECT contributions.id, contributions.value, (contributions.value - CASE WHEN contributions.payment_service_fee_paid_by_user THEN (0)::numeric ELSE COALESCE(NULLIF(contributions.payment_service_fee, (0)::numeric), ((matches.payment_service_fee / matches.value) * contributions.value), (0)::numeric) END) AS net_payment, COALESCE(NULLIF(contributions.payment_service_fee, (0)::numeric), ((matches.payment_service_fee / matches.value) * contributions.value), (0)::numeric) AS payment_service_fee FROM ((contributions LEFT JOIN matchings ON ((contributions.matching_id = matchings.id))) LEFT JOIN matches ON ((matchings.match_id = matches.id)));


--
