source: ccsd/trunk/dbschema/ccs_billing.schema @ 1361

-- 
-- PostgreSQL create schema for CRCnet Configuration Systems billing module
--
-- All prices in these tables should be sales tax exclusive
--
-- Author:  Ivan Meredith <ijm1@cs.waikato.ac.nz>
--          Matt Brown  <matt@crc.net.nz>
-- Version:  $Id$
--

-- Create a customer contact_type
INSERT INTO contact_types VALUES ('customer', 'Customer');

-- List of plans that are dealt with via RADIUS
CREATE TABLE radius_plan (
    plan_id         SERIAL          PRIMARY KEY,
    -- machine readable name for the plan ascii only, no spaces
    plan_name       varchar(40)     NOT NULL,
    -- Upstream (away from customer) rate
    upstream_kbps   integer         NOT NULL,
    -- Downstream (towards customer) rate
    downstream_kbps integer         NOT NULL
);

-- Billing Plans that customers can be placed on
CREATE TABLE billing_plan (
    plan_id         SERIAL          PRIMARY KEY,
    plan_name       varchar(40)     NOT NULL,
    description     varchar(255)    NOT NULL,
    -- The monthly cost of the plan
    price           real            NOT NULL,
    -- The RADIUS plan that the user of the plan is initially placed on
    radius_plan     integer         NOT NULL REFERENCES radius_plan (plan_id)
                                                ON DELETE RESTRICT,
    -- The number of MB the user can transfer before they reach the 'cap', 
    -- NULL for uncapped
    included_mb     integer,
    -- What time period the cap operates on (daily, weekly, monthly)
    cap_period      varchar(20)     NOT NULL DEFAULT 'monthly',
    -- What to do when the 'cap' is reached, one of (throttle, purchase)
    cap_action      varchar(20)     NOT NULL DEFAULT 'throttle',
    -- Additional information for the cap 
    -- If cap_action is throttle, this is the ID of the RADIUS plan to 
    -- place the user onto when the cap is reached
    -- If cap_action is purchase, this is the number of mb to add to the 
    -- cap_at_mb parameter in the state table
    cap_param       integer,
    -- The cost of taking the cap action
    cap_price       real 
);

-- Discounts that can be applied to a customers account each month
CREATE TABLE billing_discount (
    discount_id     SERIAL          PRIMARY KEY,
    discount_name   varchar(40)     NOT NULL,
    description     varchar(255)    NOT NULL,
    -- The amount of the discount, must be a positive integer
    amount          real            NOT NULL
);

-- Further details about each customers account
CREATE TABLE customer_account (
    contact_id      integer         NOT NULL REFERENCES contact (contact_id)
                                                ON DELETE CASCADE,
    -- The billing plan the customer has chosen
    plan_id         integer         NOT NULL REFERENCES billing_plan (plan_id)
                                                ON DELETE RESTRICT,
    -- Who to make the invoice out to
    billing_name    varchar(50)     NOT NULL,
    -- The address to send the invoice to
    billing_address varchar(200)    NOT NULL,
    -- The customers current account balance
    account_balance real            DEFAULT 0,
        -- Join date of the customer, used for working out when data history starts
    join_date       timestamp with time zone DEFAULT current_timestamp
);

-- Details of the discounts that apply to individual customers
CREATE TABLE customer_discount (
    customer_discount_id integer    PRIMARY KEY,
    contact_id      integer         NOT NULL REFERENCES contact (contact_id)
                                                ON DELETE CASCADE,
    discount_id     integer         NOT NULL REFERENCES billing_discount
                                        (discount_id) ON DELETE CASCADE,
    -- The first date this discount should be applied to the customers account
    start_date      timestamp with time zone NOT NULL,
    -- True if the discount should be applied for every subsequent month
    recurring       boolean         NOT NULL,
    -- Followed by an optional (inclusive) end date after which the discount
    -- Will no longer be applied to the customers account
    end_date        timestamp with time zone
);
        
-- Current state of the customer
-- This table is updated regularly to track how much traffic the customer has
-- used. Only completed sessions are added to the traffic used in this table.
CREATE TABLE customer_state (
    contact_id      integer         NOT NULL REFERENCES contact (contact_id)
                                                ON DELETE CASCADE,
    -- The plan the customer is currently active on
    plan_id         integer         NOT NULL REFERENCES billing_plan (plan_id)
                                                ON DELETE RESTRICT,
    -- The date that this entry was last reset back to plan defaults 
    plan_start      timestamp with time zone NOT NULL,
    -- The current cap limit, may be greater than the plans included_mb if 
    -- extra MBs have been purchased
    cap_at_mb       integer         NOT NULL,        
    -- The number of MB used since the last reset of this entry
    used_mb         integer         NOT NULL DEFAULT 0,
    -- The last time the used_mb counter was updated
    last_updated    timestamp with time zone NOT NULL,
    -- The RADIUS plan that is currently active for this user
    radius_plan     integer         NOT NULL REFERENCES radius_plan (plan_id)
                                                ON DELETE RESTRICT
);

-- The real current state of this customer 
-- This takes into accunt the used_mb's recorded in the above state table, plus
-- the number of megabytes used in the currently active session
CREATE VIEW current_customer_state AS SELECT 
    cs.contact_id, cs.plan_start, cs.used_mb, 
    cs.used_mb+COALESCE(t.acctinputoctets+t.acctoutputoctets,0)/1024/1024 AS
    current_mb, ras.acctsessionid FROM customer_state cs, contact c LEFT JOIN
    radius_active_sessions ras ON c.username=ras.username LEFT JOIN 
    radius_latest_traffic t ON ras.acctsessionid=t.acctsessionid WHERE 
    cs.contact_id=c.contact_id;

-- Billing records for the customer
CREATE TABLE billing_record (
    contact_id      integer         NOT NULL REFERENCES contact (contact_id)
                                                ON DELETE CASCADE,
    -- The invoice that this record was billed on, NULL if not yet billed
    invoice_id      integer,
    -- The date this record was added
    record_date     timestamp with time zone,
    -- Description of the charge
    description     varchar(255)    NOT NULL,
    -- The per unit amount of the  charge (negative for refunds)
    amount          real            NOT NULL,
    -- The quantity to charge. Eg. final cost for record is amount * quantity
    quantity        integer
);

-- Record of invoices sent to the customer
CREATE TABLE billing_invoice (
    contact_id      integer         NOT NULL REFERENCES contact (contact_id)
                                                ON DELETE CASCADE,
    invoice_id      integer         NOT NULL,
    invoice_date    timestamp with time zone NOT NULL,
    invoice_subtotal real           NOT NULL,
    -- Filesystem path to the invoice PDF
    invoice_file    varchar(4096),
    -- True if invoice has been emailed to the customer
    email_sent      boolean         NOT NULL DEFAULT FALSE,
    -- The date and filesystem path to the file that this invoice was
    -- exported to for payment processing
    payment_export_date timestamp with time zone,
    payment_export_file varchar(4096),
    -- The date and filesystem path to the file that this invoice was
    -- exported to for external accounting purposes
    acct_export_date timestamp with time zone,
    acct_export_file varchar(4096),
    -- True if the invoice has been paid
    invoice_paid    boolean         NOT NULL DEFAULT FALSE,
    -- Details of the date and input file that we received the payment
    -- details via
    payment_import_date timestamp with time zone,
    payment_import_file varchar(4096)
);

-- Sequence to assist in generating invoice IDs
CREATE SEQUENCE billing_invoice_id_seq MINVALUE 100000 START 100000 NO CYCLE;

-- List of billing records that have not yet been invoived
CREATE VIEW non_invoiced_billing_records AS SELECT
        * FROM billing_record WHERE invoice_id IS NULL;

-- List of customer type contacts along with their additional information
CREATE VIEW customers AS SELECT c.*, a.plan_id, a.billing_name, 
    a.billing_address, a.account_balance, b.plan_name 
    FROM billing_plan b,contact c LEFT JOIN customer_account a ON 
    c.contact_id=a.contact_id HAVING c.type='customer' AND b.plan_id=a.plan_id;
        
-- List of plans overviews, including rate data.
CREATE VIEW plans AS SELECT p.*, r.plan_name AS rad_plan_name,r.upstream_kbps,
        r.downstream_kbps FROM billing_plan p, radius_plan r WHERE r.plan_id=p.radius_plan;

-- Gets information needed for rollover. use with "WHERE cap_period='weekly'
-- to get rollovers that are done weekly
CREATE VIEW rollover AS SELECT a.plan_name, a.description, a.price,
        a.included_mb, a.cap_period, a.radius_plan, b.contact_id FROM billing_plan a, 
        customer_state b WHERE b.plan_id=a.plan_id;

-- vim: ft=sql