

--
-- TOC entry 7 (class 2615 OID 16385)
-- Name: scpos; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA scpos;


ALTER SCHEMA scpos OWNER TO postgres;

--
-- TOC entry 4 (class 2615 OID 24577)
-- Name: ws; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA ws;


ALTER SCHEMA ws OWNER TO postgres;

--
-- TOC entry 4313 (class 0 OID 0)
-- Dependencies: 4
-- Name: SCHEMA ws; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA ws IS 'Database schema for Water Utility Management System for Land Lords, Small Community and Small Towns';


--
-- TOC entry 932 (class 1247 OID 16387)
-- Name: payment_method; Type: TYPE; Schema: scpos; Owner: postgres
--

CREATE TYPE scpos.payment_method AS ENUM (
    'cash',
    'card',
    'mobile_money',
    'bank_transfer',
    'mixed'
);


ALTER TYPE scpos.payment_method OWNER TO postgres;

--
-- TOC entry 1040 (class 1247 OID 33024)
-- Name: export_status_enum; Type: TYPE; Schema: ws; Owner: postgres
--

CREATE TYPE ws.export_status_enum AS ENUM (
    'pending',
    'exported',
    'anomaly'
);


ALTER TYPE ws.export_status_enum OWNER TO postgres;

--
-- TOC entry 1046 (class 1247 OID 33053)
-- Name: reading_source_enum; Type: TYPE; Schema: ws; Owner: postgres
--

CREATE TYPE ws.reading_source_enum AS ENUM (
    'manual',
    'ultrasonic',
    'electromagnetic',
    'other'
);


ALTER TYPE ws.reading_source_enum OWNER TO postgres;

--
-- TOC entry 313 (class 1255 OID 41561)
-- Name: generate_disconnection_no(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.generate_disconnection_no() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF NEW.disconnection_no IS NULL OR NEW.disconnection_no = '' THEN
        NEW.disconnection_no := 'DISC-' || 
                               TO_CHAR(NOW(), 'YYYY') || '-' || 
                               LPAD(NEW.disconnection_id::TEXT, 6, '0');
    END IF;
    RETURN NEW;
END;
$$;


ALTER FUNCTION public.generate_disconnection_no() OWNER TO postgres;

--
-- TOC entry 4314 (class 0 OID 0)
-- Dependencies: 313
-- Name: FUNCTION generate_disconnection_no(); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.generate_disconnection_no() IS 'Auto-generates disconnection number on insert';


--
-- TOC entry 314 (class 1255 OID 41563)
-- Name: log_disconnection_changes(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.log_disconnection_changes() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND OLD.status != NEW.status THEN
        INSERT INTO ws.disconnection_history (
            disconnection_id,
            action,
            old_status,
            new_status,
            action_notes,
            system_notes,
            performed_by_user_id
        ) VALUES (
            NEW.disconnection_id,
            'status_change',
            OLD.status,
            NEW.status,
            NEW.internal_notes,
            'Status changed from ' || OLD.status || ' to ' || NEW.status,
            NEW.updated_by_user_id
        );
    END IF;
    RETURN NEW;
END;
$$;


ALTER FUNCTION public.log_disconnection_changes() OWNER TO postgres;

--
-- TOC entry 4315 (class 0 OID 0)
-- Dependencies: 314
-- Name: FUNCTION log_disconnection_changes(); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.log_disconnection_changes() IS 'Logs all status changes to disconnection_history';


--
-- TOC entry 310 (class 1255 OID 32977)
-- Name: update_timestamp(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_timestamp() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_timestamp() OWNER TO postgres;

--
-- TOC entry 312 (class 1255 OID 41556)
-- Name: update_updated_at_column(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_updated_at_column() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_updated_at_column() OWNER TO postgres;

--
-- TOC entry 318 (class 1255 OID 16397)
-- Name: gen_invoice_no(timestamp with time zone); Type: FUNCTION; Schema: scpos; Owner: postgres
--

CREATE FUNCTION scpos.gen_invoice_no(p_at timestamp with time zone DEFAULT now()) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE v_ym CHAR(6) := to_char(p_at,'YYYYMM'); v_next INTEGER;
BEGIN
  LOOP
    UPDATE scpos.invoice_counters SET last=last+1 WHERE ym=v_ym RETURNING last INTO v_next;
    IF FOUND THEN RETURN format('INV-%s-%04s', v_ym, v_next); END IF;
    BEGIN
      INSERT INTO scpos.invoice_counters(ym,last) VALUES (v_ym,1) RETURNING last INTO v_next;
      RETURN format('INV-%s-%04s', v_ym, v_next);
    EXCEPTION WHEN unique_violation THEN END;
  END LOOP;
END$$;


ALTER FUNCTION scpos.gen_invoice_no(p_at timestamp with time zone) OWNER TO postgres;

--
-- TOC entry 319 (class 1255 OID 16398)
-- Name: set_invoice_number(); Type: FUNCTION; Schema: scpos; Owner: postgres
--

CREATE FUNCTION scpos.set_invoice_number() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF NEW.invoice_number IS NULL OR NEW.invoice_number='' THEN
    NEW.invoice_number := scpos.gen_invoice_no(COALESCE(NEW.sale_time,NOW()));
  END IF;
  RETURN NEW;
END$$;


ALTER FUNCTION scpos.set_invoice_number() OWNER TO postgres;

--
-- TOC entry 328 (class 1255 OID 16399)
-- Name: tgr_return_items_stock(); Type: FUNCTION; Schema: scpos; Owner: postgres
--

CREATE FUNCTION scpos.tgr_return_items_stock() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF TG_OP='INSERT' THEN        PERFORM scpos.apply_stock(NEW.product_id, +NEW.quantity);
  ELSIF TG_OP='DELETE' THEN     PERFORM scpos.apply_stock(OLD.product_id, -OLD.quantity);
  ELSIF TG_OP='UPDATE' THEN
    IF NEW.product_id=OLD.product_id THEN
      PERFORM scpos.apply_stock(NEW.product_id, +(NEW.quantity-OLD.quantity));
    ELSE
      PERFORM scpos.apply_stock(OLD.product_id, -OLD.quantity);
      PERFORM scpos.apply_stock(NEW.product_id, +NEW.quantity);
    END IF;
  END IF; RETURN NULL;
END$$;


ALTER FUNCTION scpos.tgr_return_items_stock() OWNER TO postgres;

--
-- TOC entry 329 (class 1255 OID 16400)
-- Name: tgr_returns_totals(); Type: FUNCTION; Schema: scpos; Owner: postgres
--

CREATE FUNCTION scpos.tgr_returns_totals() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  UPDATE scpos.returns r
     SET total_refund = COALESCE((SELECT SUM(line_refund) FROM scpos.return_items WHERE return_id=r.return_id),0),
         updated_at   = NOW()
   WHERE r.return_id = COALESCE(NEW.return_id, OLD.return_id);
  RETURN NULL;
END$$;


ALTER FUNCTION scpos.tgr_returns_totals() OWNER TO postgres;

--
-- TOC entry 330 (class 1255 OID 33417)
-- Name: allocate_payment(bigint, character varying, numeric); Type: FUNCTION; Schema: ws; Owner: postgres
--

CREATE FUNCTION ws.allocate_payment(p_payment_id bigint, p_customer_no character varying, p_amount numeric) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_remaining_amount NUMERIC(10,2);
    v_bill RECORD;
    v_allocation_amount NUMERIC(10,2);
BEGIN
    v_remaining_amount := p_amount;
    
    -- Get unpaid bills in order (oldest first - FIFO)
    FOR v_bill IN 
        SELECT bill_id, balance 
        FROM ws.bills 
        WHERE customer_no = p_customer_no 
          AND balance > 0 
          AND bill_status NOT IN ('cancelled', 'void')
          AND is_deleted = false
        ORDER BY billing_period ASC, issue_date ASC
    LOOP
        EXIT WHEN v_remaining_amount <= 0;
        
        -- Calculate how much to allocate to this bill
        v_allocation_amount := LEAST(v_remaining_amount, v_bill.balance);
        
        -- Create allocation record
        INSERT INTO ws.payment_allocations (payment_id, bill_id, allocated_amount)
        VALUES (p_payment_id, v_bill.bill_id, v_allocation_amount);
        
        -- Update bill
        UPDATE ws.bills 
        SET amount_paid = amount_paid + v_allocation_amount,
            last_payment_date = CURRENT_DATE,
            first_payment_date = COALESCE(first_payment_date, CURRENT_DATE),
            fully_paid_date = CASE 
                WHEN (amount_paid + v_allocation_amount) >= total_amount THEN CURRENT_DATE 
                ELSE NULL 
            END,
            payment_status = CASE 
                WHEN (amount_paid + v_allocation_amount) >= total_amount THEN 'paid'
                WHEN (amount_paid + v_allocation_amount) > 0 THEN 'partial'
                ELSE 'unpaid'
            END
        WHERE bill_id = v_bill.bill_id;
        
        v_remaining_amount := v_remaining_amount - v_allocation_amount;
    END LOOP;
    
    -- If still money left, it becomes credit
    IF v_remaining_amount > 0 THEN
        UPDATE ws.customer_balances 
        SET credit_balance = credit_balance + v_remaining_amount
        WHERE customer_no = p_customer_no;
    END IF;
    
    -- Update customer balance
    UPDATE ws.customer_balances
    SET current_balance = (
            SELECT COALESCE(SUM(balance), 0)
            FROM ws.bills
            WHERE customer_no = p_customer_no
              AND is_deleted = false
        ),
        total_paid = total_paid + p_amount,
        last_payment_date = CURRENT_DATE,
        last_payment_amount = p_amount
    WHERE customer_no = p_customer_no;
    
END;
$$;


ALTER FUNCTION ws.allocate_payment(p_payment_id bigint, p_customer_no character varying, p_amount numeric) OWNER TO postgres;

--
-- TOC entry 4320 (class 0 OID 0)
-- Dependencies: 330
-- Name: FUNCTION allocate_payment(p_payment_id bigint, p_customer_no character varying, p_amount numeric); Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON FUNCTION ws.allocate_payment(p_payment_id bigint, p_customer_no character varying, p_amount numeric) IS 'Allocates payment to bills using FIFO (oldest first). Handles partial payments and credits.';


--
-- TOC entry 311 (class 1255 OID 33167)
-- Name: update_timestamp(); Type: FUNCTION; Schema: ws; Owner: postgres
--

CREATE FUNCTION ws.update_timestamp() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;


ALTER FUNCTION ws.update_timestamp() OWNER TO postgres;



--
-- TOC entry 257 (class 1259 OID 32825)
-- Name: customer_categories; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customer_categories (
    category_id integer NOT NULL,
    category_name character varying(50) NOT NULL,
    description text,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE public.customer_categories OWNER TO postgres;

--
-- TOC entry 256 (class 1259 OID 32824)
-- Name: customer_categories_category_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.customer_categories_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.customer_categories_category_id_seq OWNER TO postgres;

--
-- TOC entry 4321 (class 0 OID 0)
-- Dependencies: 256
-- Name: customer_categories_category_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.customer_categories_category_id_seq OWNED BY public.customer_categories.category_id;


--
-- TOC entry 211 (class 1259 OID 16401)
-- Name: audit_log; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.audit_log (
    audit_id bigint NOT NULL,
    table_name text NOT NULL,
    operation character(1) NOT NULL,
    row_pk text,
    changed_by integer,
    changed_at timestamp with time zone DEFAULT now() NOT NULL,
    old_data jsonb,
    new_data jsonb
);


ALTER TABLE scpos.audit_log OWNER TO postgres;

--
-- TOC entry 212 (class 1259 OID 16407)
-- Name: audit_log_audit_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.audit_log ALTER COLUMN audit_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.audit_log_audit_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

--
-- TOC entry 214 (class 1259 OID 16416)
-- Name: auth_audit_logs_log_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

CREATE SEQUENCE scpos.auth_audit_logs_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE scpos.auth_audit_logs_log_id_seq OWNER TO postgres;

--
-- TOC entry 4325 (class 0 OID 0)
-- Dependencies: 214
-- Name: auth_audit_logs_log_id_seq; Type: SEQUENCE OWNED BY; Schema: scpos; Owner: postgres
--

ALTER SEQUENCE scpos.auth_audit_logs_log_id_seq OWNED BY scpos.auth_audit_logs.log_id;


--
-- TOC entry 215 (class 1259 OID 16417)
-- Name: auth_roles; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.auth_roles (
    r_id integer NOT NULL,
    name character varying(50) NOT NULL,
    display_name character varying(100) NOT NULL,
    description text,
    is_active boolean DEFAULT true,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE scpos.auth_roles OWNER TO postgres;

--
-- TOC entry 216 (class 1259 OID 16425)
-- Name: auth_roles_r_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.auth_roles ALTER COLUMN r_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.auth_roles_r_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 219 (class 1259 OID 16436)
-- Name: brands; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.brands (
    brand_id bigint NOT NULL,
    brand_name character varying(120) NOT NULL,
    description text,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE scpos.brands OWNER TO postgres;

--
-- TOC entry 220 (class 1259 OID 16444)
-- Name: brands_brand_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.brands ALTER COLUMN brand_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.brands_brand_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 221 (class 1259 OID 16445)
-- Name: categories; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.categories (
    category_id bigint NOT NULL,
    category_name character varying(120) NOT NULL,
    description text,
    parent_id bigint,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE scpos.categories OWNER TO postgres;

--
-- TOC entry 222 (class 1259 OID 16453)
-- Name: categories_category_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.categories ALTER COLUMN category_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.categories_category_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 223 (class 1259 OID 16454)
-- Name: customers; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.customers (
    customer_id bigint NOT NULL,
    full_name character varying(200) NOT NULL,
    phone character varying(40),
    email character varying(160),
    address text,
    loyalty_points integer DEFAULT 0 NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT customers_loyalty_points_check CHECK ((loyalty_points >= 0))
);


ALTER TABLE scpos.customers OWNER TO postgres;

--
-- TOC entry 224 (class 1259 OID 16464)
-- Name: customers_customer_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.customers ALTER COLUMN customer_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.customers_customer_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 225 (class 1259 OID 16465)
-- Name: invoice_counters; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.invoice_counters (
    ym character(6) NOT NULL,
    last integer DEFAULT 0 NOT NULL
);


ALTER TABLE scpos.invoice_counters OWNER TO postgres;

--
-- TOC entry 226 (class 1259 OID 16469)
-- Name: products; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.products (
    product_id bigint NOT NULL,
    barcode character varying(64),
    sku character varying(64),
    product_name character varying(200) NOT NULL,
    description text,
    unit_of_measure character varying(32) DEFAULT 'pcs'::character varying NOT NULL,
    category_id bigint,
    brand_id bigint,
    tax_id bigint,
    cost_price numeric(12,2) DEFAULT 0 NOT NULL,
    retail_price numeric(12,2) NOT NULL,
    stock_quantity numeric(14,3) DEFAULT 0 NOT NULL,
    reorder_level numeric(14,3) DEFAULT 0 NOT NULL,
    expiry_date date,
    batch_number character varying(64),
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT products_cost_price_check CHECK ((cost_price >= (0)::numeric)),
    CONSTRAINT products_reorder_level_check CHECK ((reorder_level >= (0)::numeric)),
    CONSTRAINT products_retail_price_check CHECK ((retail_price >= (0)::numeric)),
    CONSTRAINT products_stock_quantity_check CHECK ((stock_quantity >= (0)::numeric))
);


ALTER TABLE scpos.products OWNER TO postgres;

--
-- TOC entry 227 (class 1259 OID 16485)
-- Name: products_product_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.products ALTER COLUMN product_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.products_product_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 228 (class 1259 OID 16486)
-- Name: purchase_items; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.purchase_items (
    purchase_item_id bigint NOT NULL,
    purchase_id bigint NOT NULL,
    product_id bigint NOT NULL,
    quantity numeric(14,3) NOT NULL,
    cost_price numeric(12,2) NOT NULL,
    subtotal numeric(14,2) GENERATED ALWAYS AS ((quantity * cost_price)) STORED,
    CONSTRAINT purchase_items_cost_price_check CHECK ((cost_price >= (0)::numeric)),
    CONSTRAINT purchase_items_quantity_check CHECK ((quantity > (0)::numeric))
);


ALTER TABLE scpos.purchase_items OWNER TO postgres;

--
-- TOC entry 229 (class 1259 OID 16492)
-- Name: purchase_items_purchase_item_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.purchase_items ALTER COLUMN purchase_item_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.purchase_items_purchase_item_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 230 (class 1259 OID 16493)
-- Name: purchases; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.purchases (
    purchase_id bigint NOT NULL,
    supplier_id bigint NOT NULL,
    invoice_number character varying(80),
    total_cost numeric(14,2) DEFAULT 0 NOT NULL,
    notes text,
    purchased_at timestamp with time zone DEFAULT now() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT purchases_total_cost_check CHECK ((total_cost >= (0)::numeric))
);


ALTER TABLE scpos.purchases OWNER TO postgres;

--
-- TOC entry 231 (class 1259 OID 16503)
-- Name: purchases_purchase_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.purchases ALTER COLUMN purchase_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.purchases_purchase_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 232 (class 1259 OID 16504)
-- Name: refunds; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.refunds (
    refund_id bigint NOT NULL,
    return_id bigint NOT NULL,
    method scpos.payment_method NOT NULL,
    amount numeric(14,2) NOT NULL,
    txn_ref character varying(120),
    note text,
    refunded_at timestamp with time zone DEFAULT now() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT refunds_amount_check CHECK ((amount > (0)::numeric))
);


ALTER TABLE scpos.refunds OWNER TO postgres;

--
-- TOC entry 233 (class 1259 OID 16513)
-- Name: refunds_refund_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.refunds ALTER COLUMN refund_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.refunds_refund_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 234 (class 1259 OID 16514)
-- Name: return_items; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.return_items (
    return_item_id bigint NOT NULL,
    return_id bigint NOT NULL,
    product_id bigint NOT NULL,
    quantity numeric(14,3) NOT NULL,
    unit_price numeric(12,2) NOT NULL,
    line_refund numeric(14,2) GENERATED ALWAYS AS ((quantity * unit_price)) STORED,
    CONSTRAINT return_items_quantity_check CHECK ((quantity > (0)::numeric)),
    CONSTRAINT return_items_unit_price_check CHECK ((unit_price >= (0)::numeric))
);


ALTER TABLE scpos.return_items OWNER TO postgres;

--
-- TOC entry 235 (class 1259 OID 16520)
-- Name: return_items_return_item_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.return_items ALTER COLUMN return_item_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.return_items_return_item_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 236 (class 1259 OID 16521)
-- Name: returns; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.returns (
    return_id bigint NOT NULL,
    sale_id bigint NOT NULL,
    cashier_id integer NOT NULL,
    reason text,
    total_refund numeric(14,2) DEFAULT 0 NOT NULL,
    return_time timestamp with time zone DEFAULT now() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT returns_total_refund_check CHECK ((total_refund >= (0)::numeric))
);


ALTER TABLE scpos.returns OWNER TO postgres;

--
-- TOC entry 237 (class 1259 OID 16531)
-- Name: returns_return_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.returns ALTER COLUMN return_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.returns_return_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 238 (class 1259 OID 16532)
-- Name: sales; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.sales (
    sale_id bigint NOT NULL,
    invoice_number character varying(80),
    customer_id bigint,
    cashier_id integer NOT NULL,
    total_amount numeric(14,2) NOT NULL,
    discount_amount numeric(14,2) DEFAULT 0 NOT NULL,
    tax_amount numeric(14,2) DEFAULT 0 NOT NULL,
    payment_method scpos.payment_method DEFAULT 'cash'::scpos.payment_method NOT NULL,
    paid_amount numeric(14,2) DEFAULT 0 NOT NULL,
    change_amount numeric(14,2) DEFAULT 0 NOT NULL,
    sale_time timestamp with time zone DEFAULT now() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT sales_change_amount_check CHECK ((change_amount >= (0)::numeric)),
    CONSTRAINT sales_discount_amount_check CHECK ((discount_amount >= (0)::numeric)),
    CONSTRAINT sales_paid_amount_check CHECK ((paid_amount >= (0)::numeric)),
    CONSTRAINT sales_tax_amount_check CHECK ((tax_amount >= (0)::numeric)),
    CONSTRAINT sales_total_amount_check CHECK ((total_amount >= (0)::numeric))
);


ALTER TABLE scpos.sales OWNER TO postgres;

--
-- TOC entry 239 (class 1259 OID 16548)
-- Name: sales_items; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.sales_items (
    sale_item_id bigint NOT NULL,
    sale_id bigint NOT NULL,
    product_id bigint NOT NULL,
    quantity numeric(14,3) NOT NULL,
    unit_price numeric(12,2) NOT NULL,
    discount numeric(12,2) DEFAULT 0 NOT NULL,
    subtotal numeric(14,2) GENERATED ALWAYS AS (((quantity * unit_price) - discount)) STORED,
    CONSTRAINT sales_items_discount_check CHECK ((discount >= (0)::numeric)),
    CONSTRAINT sales_items_quantity_check CHECK ((quantity > (0)::numeric)),
    CONSTRAINT sales_items_unit_price_check CHECK ((unit_price >= (0)::numeric))
);


ALTER TABLE scpos.sales_items OWNER TO postgres;

--
-- TOC entry 240 (class 1259 OID 16556)
-- Name: sales_items_sale_item_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.sales_items ALTER COLUMN sale_item_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.sales_items_sale_item_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 241 (class 1259 OID 16557)
-- Name: sales_sale_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.sales ALTER COLUMN sale_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.sales_sale_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 242 (class 1259 OID 16558)
-- Name: suppliers; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.suppliers (
    supplier_id bigint NOT NULL,
    supplier_name character varying(200) NOT NULL,
    contact_person character varying(150),
    phone character varying(40),
    email character varying(160),
    address text,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE scpos.suppliers OWNER TO postgres;

--
-- TOC entry 243 (class 1259 OID 16566)
-- Name: suppliers_supplier_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.suppliers ALTER COLUMN supplier_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.suppliers_supplier_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 244 (class 1259 OID 16567)
-- Name: taxes; Type: TABLE; Schema: scpos; Owner: postgres
--

CREATE TABLE scpos.taxes (
    tax_id bigint NOT NULL,
    tax_name character varying(120) NOT NULL,
    tax_rate numeric(5,2) NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT taxes_tax_rate_check CHECK (((tax_rate >= (0)::numeric) AND (tax_rate <= (100)::numeric)))
);


ALTER TABLE scpos.taxes OWNER TO postgres;

--
-- TOC entry 245 (class 1259 OID 16574)
-- Name: taxes_tax_id_seq; Type: SEQUENCE; Schema: scpos; Owner: postgres
--

ALTER TABLE scpos.taxes ALTER COLUMN tax_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME scpos.taxes_tax_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 246 (class 1259 OID 16575)
-- Name: v_current_stock; Type: VIEW; Schema: scpos; Owner: postgres
--

CREATE VIEW scpos.v_current_stock AS
 SELECT p.product_id,
    p.product_name,
    p.barcode,
    p.sku,
    p.stock_quantity,
    p.reorder_level,
    p.retail_price,
    c.category_name,
    b.brand_name,
    p.updated_at
   FROM ((scpos.products p
     LEFT JOIN scpos.categories c ON ((c.category_id = p.category_id)))
     LEFT JOIN scpos.brands b ON ((b.brand_id = p.brand_id)));


ALTER TABLE scpos.v_current_stock OWNER TO postgres;

--
-- TOC entry 247 (class 1259 OID 16580)
-- Name: v_top_sellers_30d; Type: VIEW; Schema: scpos; Owner: postgres
--

CREATE VIEW scpos.v_top_sellers_30d AS
 SELECT si.product_id,
    p.product_name,
    sum(si.quantity) AS qty_sold,
    sum(si.subtotal) AS revenue,
    min(s.sale_time) AS first_sale,
    max(s.sale_time) AS last_sale
   FROM ((scpos.sales_items si
     JOIN scpos.sales s ON ((s.sale_id = si.sale_id)))
     JOIN scpos.products p ON ((p.product_id = si.product_id)))
  WHERE (s.sale_time >= (now() - '30 days'::interval))
  GROUP BY si.product_id, p.product_name
  ORDER BY (sum(si.quantity)) DESC;


ALTER TABLE scpos.v_top_sellers_30d OWNER TO postgres;

--
-- TOC entry 254 (class 1259 OID 32795)
-- Name: auth_permissions; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.auth_permissions (
    p_id integer NOT NULL,
    name character varying(50) NOT NULL,
    description text,
    resource character varying(100),
    action character varying(50)
);


ALTER TABLE ws.auth_permissions OWNER TO postgres;

--
-- TOC entry 253 (class 1259 OID 32794)
-- Name: auth_permissions_p_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.auth_permissions_p_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.auth_permissions_p_id_seq OWNER TO postgres;

--
-- TOC entry 4360 (class 0 OID 0)
-- Dependencies: 253
-- Name: auth_permissions_p_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.auth_permissions_p_id_seq OWNED BY ws.auth_permissions.p_id;


--
-- TOC entry 255 (class 1259 OID 32805)
-- Name: auth_role_permissions; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.auth_role_permissions (
    role_id integer NOT NULL,
    permission_id integer NOT NULL
);


ALTER TABLE ws.auth_role_permissions OWNER TO postgres;

--
-- TOC entry 249 (class 1259 OID 24579)
-- Name: auth_roles; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.auth_roles (
    r_id integer NOT NULL,
    name character varying(50) NOT NULL,
    display_name character varying(100) NOT NULL,
    description text,
    is_active boolean DEFAULT true,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE ws.auth_roles OWNER TO postgres;

--
-- TOC entry 248 (class 1259 OID 24578)
-- Name: auth_roles_r_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.auth_roles_r_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.auth_roles_r_id_seq OWNER TO postgres;

--
-- TOC entry 4361 (class 0 OID 0)
-- Dependencies: 248
-- Name: auth_roles_r_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.auth_roles_r_id_seq OWNED BY ws.auth_roles.r_id;

--
-- TOC entry 250 (class 1259 OID 24612)
-- Name: auth_users_au_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.auth_users_au_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.auth_users_au_id_seq OWNER TO postgres;

--
-- TOC entry 4362 (class 0 OID 0)
-- Dependencies: 250
-- Name: auth_users_au_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.auth_users_au_id_seq OWNED BY ws.auth_users.au_id;


--
-- TOC entry 287 (class 1259 OID 33290)
-- Name: bill_details; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.bill_details (
    detail_id bigint NOT NULL,
    bill_id bigint NOT NULL,
    charge_type character varying(50) NOT NULL,
    description text NOT NULL,
    quantity numeric(10,2),
    unit_price numeric(10,2),
    amount numeric(10,2) NOT NULL,
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.bill_details OWNER TO postgres;

--
-- TOC entry 4363 (class 0 OID 0)
-- Dependencies: 287
-- Name: TABLE bill_details; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.bill_details IS 'Detailed breakdown of charges for each bill';


--
-- TOC entry 286 (class 1259 OID 33289)
-- Name: bill_details_detail_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.bill_details_detail_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.bill_details_detail_id_seq OWNER TO postgres;

--
-- TOC entry 4364 (class 0 OID 0)
-- Dependencies: 286
-- Name: bill_details_detail_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.bill_details_detail_id_seq OWNED BY ws.bill_details.detail_id;


--
-- TOC entry 283 (class 1259 OID 33193)
-- Name: billing_cycles; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.billing_cycles (
    cycle_id integer NOT NULL,
    cycle_month date NOT NULL,
    status character varying(20) DEFAULT 'open'::character varying NOT NULL,
    total_customers_billed integer DEFAULT 0,
    total_bills_generated integer DEFAULT 0,
    total_amount_billed numeric(12,2) DEFAULT 0.00,
    failed_bills integer DEFAULT 0,
    started_at timestamp with time zone,
    completed_at timestamp with time zone,
    generated_by_user_id integer,
    notes text,
    created_at timestamp with time zone DEFAULT now(),
    CONSTRAINT chk_cycle_status CHECK (((status)::text = ANY ((ARRAY['open'::character varying, 'processing'::character varying, 'completed'::character varying, 'closed'::character varying])::text[])))
);


ALTER TABLE ws.billing_cycles OWNER TO postgres;

--
-- TOC entry 4365 (class 0 OID 0)
-- Dependencies: 283
-- Name: TABLE billing_cycles; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.billing_cycles IS 'Track billing runs and prevent duplicate billing for same month';


--
-- TOC entry 282 (class 1259 OID 33192)
-- Name: billing_cycles_cycle_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.billing_cycles_cycle_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.billing_cycles_cycle_id_seq OWNER TO postgres;

--
-- TOC entry 4366 (class 0 OID 0)
-- Dependencies: 282
-- Name: billing_cycles_cycle_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.billing_cycles_cycle_id_seq OWNED BY ws.billing_cycles.cycle_id;


--
-- TOC entry 285 (class 1259 OID 33218)
-- Name: bills; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.bills (
    bill_id bigint NOT NULL,
    bill_no character varying(30) NOT NULL,
    customer_no character varying(20) NOT NULL,
    meter_no character varying(50) NOT NULL,
    consumption_id bigint,
    tariff_id integer NOT NULL,
    cycle_id integer,
    billing_period date NOT NULL,
    issue_date date DEFAULT CURRENT_DATE NOT NULL,
    due_date date NOT NULL,
    prev_reading numeric(10,2) NOT NULL,
    curr_reading numeric(10,2) NOT NULL,
    consumption numeric(10,2) NOT NULL,
    service_charge numeric(10,2) DEFAULT 0.00 NOT NULL,
    consumption_charge numeric(10,2) DEFAULT 0.00 NOT NULL,
    subtotal numeric(10,2) NOT NULL,
    arrears_brought_forward numeric(10,2) DEFAULT 0.00,
    penalties numeric(10,2) DEFAULT 0.00,
    other_charges numeric(10,2) DEFAULT 0.00,
    total_amount numeric(10,2) NOT NULL,
    amount_paid numeric(10,2) DEFAULT 0.00,
    balance numeric(10,2) GENERATED ALWAYS AS ((total_amount - amount_paid)) STORED,
    first_payment_date date,
    last_payment_date date,
    fully_paid_date date,
    payment_status character varying(20) DEFAULT 'unpaid'::character varying NOT NULL,
    bill_status character varying(20) DEFAULT 'issued'::character varying NOT NULL,
    is_deleted boolean DEFAULT false,
    deleted_at timestamp with time zone,
    deleted_by_user_id integer,
    deletion_reason text,
    generated_by_user_id integer,
    notes text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT chk_bill_status CHECK (((bill_status)::text = ANY ((ARRAY['draft'::character varying, 'issued'::character varying, 'sent'::character varying, 'cancelled'::character varying, 'void'::character varying])::text[]))),
    CONSTRAINT chk_payment_status CHECK (((payment_status)::text = ANY ((ARRAY['unpaid'::character varying, 'partial'::character varying, 'paid'::character varying, 'overdue'::character varying, 'cancelled'::character varying])::text[])))
);


ALTER TABLE ws.bills OWNER TO postgres;

--
-- TOC entry 4367 (class 0 OID 0)
-- Dependencies: 285
-- Name: TABLE bills; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.bills IS 'Main billing records for water consumption';


--
-- TOC entry 284 (class 1259 OID 33217)
-- Name: bills_bill_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.bills_bill_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.bills_bill_id_seq OWNER TO postgres;

--
-- TOC entry 4368 (class 0 OID 0)
-- Dependencies: 284
-- Name: bills_bill_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.bills_bill_id_seq OWNED BY ws.bills.bill_id;


--
-- TOC entry 281 (class 1259 OID 33179)
-- Name: charge_configurations; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.charge_configurations (
    config_id integer NOT NULL,
    charge_type character varying(50) NOT NULL,
    charge_name character varying(100) NOT NULL,
    calculation_method character varying(20) NOT NULL,
    fixed_amount numeric(10,2),
    percentage_rate numeric(5,2),
    applies_to character varying(20),
    trigger_condition character varying(100),
    is_active boolean DEFAULT true,
    effective_date date NOT NULL,
    expiry_date date,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT chk_calc_method CHECK (((calculation_method)::text = ANY ((ARRAY['fixed'::character varying, 'percentage'::character varying, 'tiered'::character varying])::text[])))
);


ALTER TABLE ws.charge_configurations OWNER TO postgres;

--
-- TOC entry 4369 (class 0 OID 0)
-- Dependencies: 281
-- Name: TABLE charge_configurations; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.charge_configurations IS 'Configuration for penalties, fees, and other charges';


--
-- TOC entry 280 (class 1259 OID 33178)
-- Name: charge_configurations_config_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.charge_configurations_config_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.charge_configurations_config_id_seq OWNER TO postgres;

--
-- TOC entry 4370 (class 0 OID 0)
-- Dependencies: 280
-- Name: charge_configurations_config_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.charge_configurations_config_id_seq OWNED BY ws.charge_configurations.config_id;


--
-- TOC entry 269 (class 1259 OID 32939)
-- Name: communities; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.communities (
    community_id integer NOT NULL,
    community_code character varying(10) NOT NULL,
    community_name character varying(100) NOT NULL,
    district_id integer NOT NULL,
    population bigint,
    population_year smallint,
    population_source character varying(255)
);


ALTER TABLE ws.communities OWNER TO postgres;

--
-- TOC entry 268 (class 1259 OID 32938)
-- Name: communities_community_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.communities_community_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.communities_community_id_seq OWNER TO postgres;

--
-- TOC entry 4371 (class 0 OID 0)
-- Dependencies: 268
-- Name: communities_community_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.communities_community_id_seq OWNED BY ws.communities.community_id;


--
-- TOC entry 279 (class 1259 OID 33100)
-- Name: consumption; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.consumption (
    consumption_id bigint NOT NULL,
    customer_no character varying(20) NOT NULL,
    meter_no character varying(30) NOT NULL,
    prev_reading numeric(10,2) NOT NULL,
    curr_reading numeric(10,2) NOT NULL,
    reading_month date NOT NULL,
    reading_source ws.reading_source_enum DEFAULT 'manual'::ws.reading_source_enum NOT NULL,
    screenshot_url character varying(255),
    reader_id bigint,
    bill_status boolean DEFAULT false,
    trash_status boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    verified_by_user_id integer,
    verified_at timestamp with time zone,
    consumption numeric(10,2) GENERATED ALWAYS AS ((curr_reading - prev_reading)) STORED,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE ws.consumption OWNER TO postgres;

--
-- TOC entry 278 (class 1259 OID 33099)
-- Name: consumption_consumption_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.consumption_consumption_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.consumption_consumption_id_seq OWNER TO postgres;

--
-- TOC entry 4372 (class 0 OID 0)
-- Dependencies: 278
-- Name: consumption_consumption_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.consumption_consumption_id_seq OWNED BY ws.consumption.consumption_id;


--
-- TOC entry 293 (class 1259 OID 33364)
-- Name: customer_balances; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.customer_balances (
    balance_id integer NOT NULL,
    customer_no character varying(20) NOT NULL,
    current_balance numeric(10,2) DEFAULT 0.00 NOT NULL,
    credit_balance numeric(10,2) DEFAULT 0.00 NOT NULL,
    total_billed numeric(10,2) DEFAULT 0.00 NOT NULL,
    total_paid numeric(10,2) DEFAULT 0.00 NOT NULL,
    last_bill_date date,
    last_bill_amount numeric(10,2),
    last_payment_date date,
    last_payment_amount numeric(10,2),
    is_in_arrears boolean GENERATED ALWAYS AS ((current_balance > (0)::numeric)) STORED,
    months_in_arrears integer DEFAULT 0,
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.customer_balances OWNER TO postgres;

--
-- TOC entry 4373 (class 0 OID 0)
-- Dependencies: 293
-- Name: TABLE customer_balances; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.customer_balances IS 'Running balance for each customer - optimized for quick lookups';


--
-- TOC entry 292 (class 1259 OID 33363)
-- Name: customer_balances_balance_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.customer_balances_balance_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.customer_balances_balance_id_seq OWNER TO postgres;

--
-- TOC entry 4374 (class 0 OID 0)
-- Dependencies: 292
-- Name: customer_balances_balance_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.customer_balances_balance_id_seq OWNED BY ws.customer_balances.balance_id;


--
-- TOC entry 259 (class 1259 OID 32837)
-- Name: customer_categories; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.customer_categories (
    category_id integer NOT NULL,
    category_name character varying(50) NOT NULL,
    description text,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE ws.customer_categories OWNER TO postgres;

--
-- TOC entry 258 (class 1259 OID 32836)
-- Name: customer_categories_category_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.customer_categories_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.customer_categories_category_id_seq OWNER TO postgres;

--
-- TOC entry 4375 (class 0 OID 0)
-- Dependencies: 258
-- Name: customer_categories_category_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.customer_categories_category_id_seq OWNED BY ws.customer_categories.category_id;


--
-- TOC entry 303 (class 1259 OID 41442)
-- Name: customer_notification_preferences; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.customer_notification_preferences (
    preference_id integer NOT NULL,
    customer_no character varying(20) NOT NULL,
    receive_sms boolean DEFAULT true,
    receive_email boolean DEFAULT true,
    auth_sms boolean DEFAULT true,
    auth_email boolean DEFAULT true,
    bills_sms boolean DEFAULT true,
    bills_email boolean DEFAULT true,
    bill_reminders_sms boolean DEFAULT true,
    bill_reminders_email boolean DEFAULT true,
    payments_sms boolean DEFAULT true,
    payments_email boolean DEFAULT true,
    disconnection_sms boolean DEFAULT true,
    disconnection_email boolean DEFAULT true,
    marketing_sms boolean DEFAULT false,
    marketing_email boolean DEFAULT false,
    preferred_phone character varying(20),
    preferred_email character varying(100),
    quiet_hours_enabled boolean DEFAULT false,
    quiet_hours_start time without time zone,
    quiet_hours_end time without time zone,
    preferred_language character varying(10) DEFAULT 'en'::character varying,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.customer_notification_preferences OWNER TO postgres;

--
-- TOC entry 4376 (class 0 OID 0)
-- Dependencies: 303
-- Name: TABLE customer_notification_preferences; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.customer_notification_preferences IS 'Customer notification channel and content preferences';


--
-- TOC entry 4377 (class 0 OID 0)
-- Dependencies: 303
-- Name: COLUMN customer_notification_preferences.quiet_hours_enabled; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.customer_notification_preferences.quiet_hours_enabled IS 'Do not send non-critical notifications during quiet hours';


--
-- TOC entry 302 (class 1259 OID 41441)
-- Name: customer_notification_preferences_preference_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.customer_notification_preferences_preference_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.customer_notification_preferences_preference_id_seq OWNER TO postgres;

--
-- TOC entry 4378 (class 0 OID 0)
-- Dependencies: 302
-- Name: customer_notification_preferences_preference_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.customer_notification_preferences_preference_id_seq OWNED BY ws.customer_notification_preferences.preference_id;


--
-- TOC entry 271 (class 1259 OID 32953)
-- Name: customers; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.customers (
    customer_id integer NOT NULL,
    account_no character varying(20) NOT NULL,
    surname character varying(100) NOT NULL,
    other_names character varying(150),
    customer_category_id integer NOT NULL,
    phone character varying(20),
    email character varying(100),
    house_no character varying(50),
    street_name character varying(100),
    community_id integer NOT NULL,
    status character varying(20) DEFAULT 'active'::character varying NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT customers_status_check CHECK (((status)::text = ANY ((ARRAY['active'::character varying, 'inactive'::character varying, 'disconnected'::character varying])::text[])))
);


ALTER TABLE ws.customers OWNER TO postgres;

--
-- TOC entry 270 (class 1259 OID 32952)
-- Name: customers_customer_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.customers_customer_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.customers_customer_id_seq OWNER TO postgres;

--
-- TOC entry 4379 (class 0 OID 0)
-- Dependencies: 270
-- Name: customers_customer_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.customers_customer_id_seq OWNED BY ws.customers.customer_id;


--
-- TOC entry 307 (class 1259 OID 41525)
-- Name: disconnection_history; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.disconnection_history (
    history_id bigint NOT NULL,
    disconnection_id bigint NOT NULL,
    action character varying(50) NOT NULL,
    old_status character varying(30),
    new_status character varying(30),
    action_notes text,
    system_notes text,
    performed_by_user_id integer,
    performed_by_name character varying(100),
    action_data jsonb,
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.disconnection_history OWNER TO postgres;

--
-- TOC entry 4380 (class 0 OID 0)
-- Dependencies: 307
-- Name: TABLE disconnection_history; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.disconnection_history IS 'Audit trail for all disconnection state changes';


--
-- TOC entry 306 (class 1259 OID 41524)
-- Name: disconnection_history_history_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.disconnection_history_history_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.disconnection_history_history_id_seq OWNER TO postgres;

--
-- TOC entry 4381 (class 0 OID 0)
-- Dependencies: 306
-- Name: disconnection_history_history_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.disconnection_history_history_id_seq OWNED BY ws.disconnection_history.history_id;


--
-- TOC entry 295 (class 1259 OID 33389)
-- Name: disconnection_notices; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.disconnection_notices (
    notice_id integer NOT NULL,
    customer_no character varying(20) NOT NULL,
    notice_type character varying(20) NOT NULL,
    notice_date date DEFAULT CURRENT_DATE NOT NULL,
    due_date date NOT NULL,
    amount_owed numeric(10,2) NOT NULL,
    status character varying(20) DEFAULT 'issued'::character varying NOT NULL,
    disconnected_date date,
    disconnected_by_user_id integer,
    notes text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT chk_notice_status CHECK (((status)::text = ANY ((ARRAY['issued'::character varying, 'paid'::character varying, 'cancelled'::character varying, 'executed'::character varying])::text[]))),
    CONSTRAINT chk_notice_type CHECK (((notice_type)::text = ANY ((ARRAY['warning'::character varying, 'final'::character varying, 'executed'::character varying])::text[])))
);


ALTER TABLE ws.disconnection_notices OWNER TO postgres;

--
-- TOC entry 4382 (class 0 OID 0)
-- Dependencies: 295
-- Name: TABLE disconnection_notices; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.disconnection_notices IS 'Track disconnection warnings and execution for non-payment';


--
-- TOC entry 294 (class 1259 OID 33388)
-- Name: disconnection_notices_notice_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.disconnection_notices_notice_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.disconnection_notices_notice_id_seq OWNER TO postgres;

--
-- TOC entry 4383 (class 0 OID 0)
-- Dependencies: 294
-- Name: disconnection_notices_notice_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.disconnection_notices_notice_id_seq OWNED BY ws.disconnection_notices.notice_id;


--
-- TOC entry 305 (class 1259 OID 41475)
-- Name: disconnections; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.disconnections (
    disconnection_id bigint NOT NULL,
    disconnection_no character varying(30) NOT NULL,
    customer_no character varying(20) NOT NULL,
    meter_no character varying(50) NOT NULL,
    disconnection_method character varying(20) NOT NULL,
    reason character varying(50) NOT NULL,
    reason_details text,
    amount_owed numeric(10,2) DEFAULT 0.00 NOT NULL,
    bills_unpaid integer DEFAULT 0 NOT NULL,
    oldest_unpaid_bill_date date,
    requires_approval boolean DEFAULT true,
    requested_by_user_id integer,
    approved_by_user_id integer,
    approval_status character varying(20) DEFAULT 'pending'::character varying,
    approved_at timestamp with time zone,
    rejected_at timestamp with time zone,
    rejection_reason text,
    warning_sent boolean DEFAULT false,
    warning_sent_at timestamp with time zone,
    final_notice_sent boolean DEFAULT false,
    final_notice_sent_at timestamp with time zone,
    scheduled_disconnection_date date,
    scheduled_disconnection_time time without time zone,
    actual_disconnection_date date,
    actual_disconnection_time time without time zone,
    valve_command_sent boolean DEFAULT false,
    valve_command_sent_at timestamp with time zone,
    valve_command_payload jsonb,
    valve_status_confirmed boolean DEFAULT false,
    valve_status_confirmed_at timestamp with time zone,
    valve_response jsonb,
    valve_error_message text,
    field_officer_user_id integer,
    field_officer_name character varying(100),
    field_officer_phone character varying(20),
    field_officer_assigned_at timestamp with time zone,
    field_officer_dispatched_at timestamp with time zone,
    physical_disconnection_completed boolean DEFAULT false,
    physical_disconnection_completed_at timestamp with time zone,
    physical_disconnection_method character varying(50),
    physical_disconnection_photo_url character varying(255),
    physical_disconnection_gps_lat numeric(10,8),
    physical_disconnection_gps_lng numeric(11,8),
    physical_disconnection_notes text,
    reconnection_fee numeric(10,2) DEFAULT 0.00,
    reconnection_payment_received boolean DEFAULT false,
    reconnection_payment_id bigint,
    reconnection_payment_date date,
    reconnection_scheduled_date date,
    reconnection_scheduled_time time without time zone,
    reconnected_at timestamp with time zone,
    reconnection_method character varying(20),
    reconnection_valve_command_sent boolean DEFAULT false,
    reconnection_valve_command_sent_at timestamp with time zone,
    reconnection_valve_confirmed boolean DEFAULT false,
    reconnection_valve_confirmed_at timestamp with time zone,
    reconnection_valve_response jsonb,
    reconnection_field_officer_user_id integer,
    reconnection_field_officer_name character varying(100),
    reconnection_completed boolean DEFAULT false,
    reconnection_completed_at timestamp with time zone,
    reconnection_photo_url character varying(255),
    reconnection_gps_lat numeric(10,8),
    reconnection_gps_lng numeric(11,8),
    reconnection_notes text,
    status character varying(30) DEFAULT 'pending'::character varying NOT NULL,
    disconnected_by_user_id integer,
    reconnected_by_user_id integer,
    cancelled_by_user_id integer,
    cancelled_at timestamp with time zone,
    cancellation_reason text,
    internal_notes text,
    customer_notes text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.disconnections OWNER TO postgres;

--
-- TOC entry 4384 (class 0 OID 0)
-- Dependencies: 305
-- Name: TABLE disconnections; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.disconnections IS 'Complete disconnection/reconnection lifecycle management';


--
-- TOC entry 4385 (class 0 OID 0)
-- Dependencies: 305
-- Name: COLUMN disconnections.disconnection_method; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.disconnections.disconnection_method IS 'remote=IoT valve control, manual=physical field work';


--
-- TOC entry 4386 (class 0 OID 0)
-- Dependencies: 305
-- Name: COLUMN disconnections.valve_response; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.disconnections.valve_response IS 'JSON response from smart meter API';


--
-- TOC entry 4387 (class 0 OID 0)
-- Dependencies: 305
-- Name: COLUMN disconnections.physical_disconnection_photo_url; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.disconnections.physical_disconnection_photo_url IS 'Photo evidence of physical disconnection';


--
-- TOC entry 304 (class 1259 OID 41474)
-- Name: disconnections_disconnection_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.disconnections_disconnection_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.disconnections_disconnection_id_seq OWNER TO postgres;

--
-- TOC entry 4388 (class 0 OID 0)
-- Dependencies: 304
-- Name: disconnections_disconnection_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.disconnections_disconnection_id_seq OWNED BY ws.disconnections.disconnection_id;


--
-- TOC entry 267 (class 1259 OID 32925)
-- Name: districts; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.districts (
    district_id integer NOT NULL,
    district_code character varying(10) NOT NULL,
    district_name character varying(100) NOT NULL,
    region_id integer NOT NULL,
    population bigint,
    population_year smallint,
    population_source character varying(255)
);


ALTER TABLE ws.districts OWNER TO postgres;

--
-- TOC entry 266 (class 1259 OID 32924)
-- Name: districts_district_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.districts_district_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.districts_district_id_seq OWNER TO postgres;

--
-- TOC entry 4389 (class 0 OID 0)
-- Dependencies: 266
-- Name: districts_district_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.districts_district_id_seq OWNED BY ws.districts.district_id;


--
-- TOC entry 275 (class 1259 OID 33003)
-- Name: meter_assignments; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.meter_assignments (
    assignment_id integer NOT NULL,
    meter_id integer NOT NULL,
    customer_id integer NOT NULL,
    assigned_date date DEFAULT CURRENT_DATE NOT NULL,
    removed_date date,
    location_details text,
    is_active boolean DEFAULT true NOT NULL,
    removed_by_user_id integer,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE ws.meter_assignments OWNER TO postgres;

--
-- TOC entry 274 (class 1259 OID 33002)
-- Name: meter_assignments_assignment_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.meter_assignments_assignment_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.meter_assignments_assignment_id_seq OWNER TO postgres;

--
-- TOC entry 4390 (class 0 OID 0)
-- Dependencies: 274
-- Name: meter_assignments_assignment_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.meter_assignments_assignment_id_seq OWNED BY ws.meter_assignments.assignment_id;


--
-- TOC entry 277 (class 1259 OID 33032)
-- Name: meter_reports; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.meter_reports (
    report_id bigint NOT NULL,
    meter_no character varying(30) NOT NULL,
    reading_value double precision NOT NULL,
    read_time timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    signal_strength integer,
    valve_state smallint,
    pressure double precision,
    flow double precision,
    battery_low boolean DEFAULT false,
    tamper_status boolean DEFAULT false,
    export_status ws.export_status_enum DEFAULT 'pending'::ws.export_status_enum NOT NULL,
    flag_remark character varying(100) DEFAULT 'Pending Export'::character varying,
    raw_payload jsonb,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE ws.meter_reports OWNER TO postgres;

--
-- TOC entry 276 (class 1259 OID 33031)
-- Name: meter_reports_report_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.meter_reports_report_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.meter_reports_report_id_seq OWNER TO postgres;

--
-- TOC entry 4391 (class 0 OID 0)
-- Dependencies: 276
-- Name: meter_reports_report_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.meter_reports_report_id_seq OWNED BY ws.meter_reports.report_id;


--
-- TOC entry 273 (class 1259 OID 32986)
-- Name: meters; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.meters (
    meter_id integer NOT NULL,
    meter_no character varying(50) NOT NULL,
    imei_no character varying(20),
    reading_mode character varying(20) NOT NULL,
    technology ws.reading_source_enum DEFAULT 'manual'::ws.reading_source_enum NOT NULL,
    gps_latitude numeric(10,7),
    gps_longitude numeric(10,7),
    status character varying(20) DEFAULT 'available'::character varying NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT meters_reading_mode_check CHECK (((reading_mode)::text = ANY ((ARRAY['manual'::character varying, 'automatic'::character varying])::text[]))),
    CONSTRAINT meters_status_check CHECK (((status)::text = ANY ((ARRAY['available'::character varying, 'installed'::character varying, 'faulty'::character varying, 'retired'::character varying])::text[])))
);


ALTER TABLE ws.meters OWNER TO postgres;

--
-- TOC entry 272 (class 1259 OID 32985)
-- Name: meters_meter_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.meters_meter_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.meters_meter_id_seq OWNER TO postgres;

--
-- TOC entry 4392 (class 0 OID 0)
-- Dependencies: 272
-- Name: meters_meter_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.meters_meter_id_seq OWNED BY ws.meters.meter_id;


--
-- TOC entry 309 (class 1259 OID 41543)
-- Name: notification_config; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.notification_config (
    config_id integer NOT NULL,
    config_key character varying(50) NOT NULL,
    config_value text NOT NULL,
    config_type character varying(20) NOT NULL,
    description text,
    is_system boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.notification_config OWNER TO postgres;

--
-- TOC entry 4393 (class 0 OID 0)
-- Dependencies: 309
-- Name: TABLE notification_config; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.notification_config IS 'Global notification system configuration';


--
-- TOC entry 308 (class 1259 OID 41542)
-- Name: notification_config_config_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.notification_config_config_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.notification_config_config_id_seq OWNER TO postgres;

--
-- TOC entry 4394 (class 0 OID 0)
-- Dependencies: 308
-- Name: notification_config_config_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.notification_config_config_id_seq OWNED BY ws.notification_config.config_id;


--
-- TOC entry 301 (class 1259 OID 41419)
-- Name: notification_logs; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.notification_logs (
    log_id bigint NOT NULL,
    queue_id bigint,
    customer_no character varying(20),
    recipient character varying(100) NOT NULL,
    channel character varying(10) NOT NULL,
    notification_type character varying(50) NOT NULL,
    event_type character varying(50) NOT NULL,
    subject text,
    message text NOT NULL,
    status character varying(20) NOT NULL,
    provider character varying(50),
    provider_response jsonb,
    provider_message_id character varying(100),
    sms_cost numeric(10,4),
    sms_units integer,
    sms_network character varying(50),
    email_provider character varying(50),
    email_opened boolean DEFAULT false,
    email_opened_at timestamp with time zone,
    email_clicked boolean DEFAULT false,
    email_clicked_at timestamp with time zone,
    error_code character varying(50),
    error_message text,
    sent_at timestamp with time zone DEFAULT now(),
    delivered_at timestamp with time zone,
    failed_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.notification_logs OWNER TO postgres;

--
-- TOC entry 4395 (class 0 OID 0)
-- Dependencies: 301
-- Name: TABLE notification_logs; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.notification_logs IS 'Complete audit trail of all sent notifications';


--
-- TOC entry 4396 (class 0 OID 0)
-- Dependencies: 301
-- Name: COLUMN notification_logs.provider_response; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.notification_logs.provider_response IS 'Full JSON response from SMS/Email provider';


--
-- TOC entry 4397 (class 0 OID 0)
-- Dependencies: 301
-- Name: COLUMN notification_logs.sms_cost; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.notification_logs.sms_cost IS 'Cost per SMS from provider';


--
-- TOC entry 300 (class 1259 OID 41418)
-- Name: notification_logs_log_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.notification_logs_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.notification_logs_log_id_seq OWNER TO postgres;

--
-- TOC entry 4398 (class 0 OID 0)
-- Dependencies: 300
-- Name: notification_logs_log_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.notification_logs_log_id_seq OWNED BY ws.notification_logs.log_id;


--
-- TOC entry 299 (class 1259 OID 41392)
-- Name: notification_queue; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.notification_queue (
    queue_id bigint NOT NULL,
    customer_no character varying(20),
    recipient_name character varying(100),
    recipient_phone character varying(20),
    recipient_email character varying(100),
    notification_type character varying(50) NOT NULL,
    event_type character varying(50) NOT NULL,
    template_code character varying(50) NOT NULL,
    reference_type character varying(50),
    reference_id bigint,
    send_sms boolean DEFAULT false,
    send_email boolean DEFAULT false,
    template_data jsonb NOT NULL,
    priority integer DEFAULT 5,
    status character varying(20) DEFAULT 'pending'::character varying,
    scheduled_at timestamp with time zone DEFAULT now(),
    attempts integer DEFAULT 0,
    max_attempts integer DEFAULT 3,
    last_attempt_at timestamp with time zone,
    next_retry_at timestamp with time zone,
    error_message text,
    processed_at timestamp with time zone,
    created_by_user_id integer,
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.notification_queue OWNER TO postgres;

--
-- TOC entry 4399 (class 0 OID 0)
-- Dependencies: 299
-- Name: TABLE notification_queue; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.notification_queue IS 'Queue for pending notifications awaiting processing';


--
-- TOC entry 4400 (class 0 OID 0)
-- Dependencies: 299
-- Name: COLUMN notification_queue.template_data; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.notification_queue.template_data IS 'JSON object with variables to replace in template';


--
-- TOC entry 4401 (class 0 OID 0)
-- Dependencies: 299
-- Name: COLUMN notification_queue.scheduled_at; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.notification_queue.scheduled_at IS 'When notification should be sent - supports future scheduling';


--
-- TOC entry 298 (class 1259 OID 41391)
-- Name: notification_queue_queue_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.notification_queue_queue_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.notification_queue_queue_id_seq OWNER TO postgres;

--
-- TOC entry 4402 (class 0 OID 0)
-- Dependencies: 298
-- Name: notification_queue_queue_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.notification_queue_queue_id_seq OWNED BY ws.notification_queue.queue_id;


--
-- TOC entry 297 (class 1259 OID 41371)
-- Name: notification_templates; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.notification_templates (
    template_id integer NOT NULL,
    template_code character varying(50) NOT NULL,
    template_name character varying(100) NOT NULL,
    template_category character varying(50) NOT NULL,
    sms_enabled boolean DEFAULT true,
    sms_template text,
    email_enabled boolean DEFAULT true,
    email_subject text,
    email_body text,
    available_variables jsonb,
    example_data jsonb,
    is_system_template boolean DEFAULT false,
    is_active boolean DEFAULT true,
    priority integer DEFAULT 5,
    created_by_user_id integer,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE ws.notification_templates OWNER TO postgres;

--
-- TOC entry 4403 (class 0 OID 0)
-- Dependencies: 297
-- Name: TABLE notification_templates; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.notification_templates IS 'Reusable message templates for notifications';


--
-- TOC entry 4404 (class 0 OID 0)
-- Dependencies: 297
-- Name: COLUMN notification_templates.template_code; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.notification_templates.template_code IS 'Unique identifier like bill_generated, payment_received';


--
-- TOC entry 4405 (class 0 OID 0)
-- Dependencies: 297
-- Name: COLUMN notification_templates.available_variables; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON COLUMN ws.notification_templates.available_variables IS 'JSON array of variables available in template';


--
-- TOC entry 296 (class 1259 OID 41370)
-- Name: notification_templates_template_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.notification_templates_template_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.notification_templates_template_id_seq OWNER TO postgres;

--
-- TOC entry 4406 (class 0 OID 0)
-- Dependencies: 296
-- Name: notification_templates_template_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.notification_templates_template_id_seq OWNED BY ws.notification_templates.template_id;


--
-- TOC entry 291 (class 1259 OID 33341)
-- Name: payment_allocations; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.payment_allocations (
    allocation_id bigint NOT NULL,
    payment_id bigint NOT NULL,
    bill_id bigint NOT NULL,
    allocated_amount numeric(10,2) NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    CONSTRAINT chk_allocated_amount_positive CHECK ((allocated_amount > (0)::numeric))
);


ALTER TABLE ws.payment_allocations OWNER TO postgres;

--
-- TOC entry 4407 (class 0 OID 0)
-- Dependencies: 291
-- Name: TABLE payment_allocations; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.payment_allocations IS 'Links payments to specific bills - supports partial and multi-bill payments';


--
-- TOC entry 290 (class 1259 OID 33340)
-- Name: payment_allocations_allocation_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.payment_allocations_allocation_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.payment_allocations_allocation_id_seq OWNER TO postgres;

--
-- TOC entry 4408 (class 0 OID 0)
-- Dependencies: 290
-- Name: payment_allocations_allocation_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.payment_allocations_allocation_id_seq OWNED BY ws.payment_allocations.allocation_id;


--
-- TOC entry 289 (class 1259 OID 33307)
-- Name: payments; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.payments (
    payment_id bigint NOT NULL,
    payment_no character varying(30) NOT NULL,
    customer_no character varying(20) NOT NULL,
    payment_date date DEFAULT CURRENT_DATE NOT NULL,
    amount numeric(10,2) NOT NULL,
    payment_method character varying(30) NOT NULL,
    reference_no character varying(100),
    momo_transaction_id character varying(100),
    momo_phone_number character varying(20),
    momo_network character varying(20),
    momo_status character varying(30),
    momo_provider_reference character varying(100),
    momo_callback_data jsonb,
    bank_name character varying(100),
    bank_account_number character varying(50),
    bank_transaction_date date,
    received_by_user_id integer,
    notes text,
    payment_status character varying(20) DEFAULT 'confirmed'::character varying,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT chk_payment_method CHECK (((payment_method)::text = ANY ((ARRAY['cash'::character varying, 'mobile_money'::character varying, 'bank_transfer'::character varying, 'card'::character varying, 'cheque'::character varying, 'other'::character varying])::text[]))),
    CONSTRAINT chk_payment_status CHECK (((payment_status)::text = ANY ((ARRAY['pending'::character varying, 'confirmed'::character varying, 'reversed'::character varying])::text[])))
);


ALTER TABLE ws.payments OWNER TO postgres;

--
-- TOC entry 4409 (class 0 OID 0)
-- Dependencies: 289
-- Name: TABLE payments; Type: COMMENT; Schema: ws; Owner: postgres
--

COMMENT ON TABLE ws.payments IS 'Payment transactions from customers';


--
-- TOC entry 288 (class 1259 OID 33306)
-- Name: payments_payment_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.payments_payment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.payments_payment_id_seq OWNER TO postgres;

--
-- TOC entry 4410 (class 0 OID 0)
-- Dependencies: 288
-- Name: payments_payment_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.payments_payment_id_seq OWNED BY ws.payments.payment_id;


--
-- TOC entry 265 (class 1259 OID 32907)
-- Name: regions; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.regions (
    region_id integer NOT NULL,
    region_code character varying(10) NOT NULL,
    region_name character varying(100) NOT NULL,
    population bigint,
    population_year smallint,
    population_source character varying(255)
);


ALTER TABLE ws.regions OWNER TO postgres;

--
-- TOC entry 264 (class 1259 OID 32906)
-- Name: regions_region_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.regions_region_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.regions_region_id_seq OWNER TO postgres;

--
-- TOC entry 4411 (class 0 OID 0)
-- Dependencies: 264
-- Name: regions_region_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.regions_region_id_seq OWNED BY ws.regions.region_id;


--
-- TOC entry 263 (class 1259 OID 32875)
-- Name: tariff_blocks; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.tariff_blocks (
    block_id integer NOT NULL,
    tariff_id integer NOT NULL,
    min_volume integer NOT NULL,
    max_volume integer,
    unit_price numeric(10,2) NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE ws.tariff_blocks OWNER TO postgres;

--
-- TOC entry 262 (class 1259 OID 32874)
-- Name: tariff_blocks_block_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.tariff_blocks_block_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.tariff_blocks_block_id_seq OWNER TO postgres;

--
-- TOC entry 4412 (class 0 OID 0)
-- Dependencies: 262
-- Name: tariff_blocks_block_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.tariff_blocks_block_id_seq OWNED BY ws.tariff_blocks.block_id;


--
-- TOC entry 261 (class 1259 OID 32849)
-- Name: tariffs; Type: TABLE; Schema: ws; Owner: postgres
--

CREATE TABLE ws.tariffs (
    tariff_id integer NOT NULL,
    category_id integer NOT NULL,
    tariff_name character varying(100) NOT NULL,
    unit_price numeric(10,2),
    service_charge numeric(10,2) DEFAULT 0.00,
    effective_date date NOT NULL,
    expiry_date date,
    status character varying(20) DEFAULT 'active'::character varying,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT tariffs_status_check CHECK (((status)::text = ANY ((ARRAY['active'::character varying, 'inactive'::character varying])::text[])))
);


ALTER TABLE ws.tariffs OWNER TO postgres;

--
-- TOC entry 260 (class 1259 OID 32848)
-- Name: tariffs_tariff_id_seq; Type: SEQUENCE; Schema: ws; Owner: postgres
--

CREATE SEQUENCE ws.tariffs_tariff_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ws.tariffs_tariff_id_seq OWNER TO postgres;

--
-- TOC entry 4413 (class 0 OID 0)
-- Dependencies: 260
-- Name: tariffs_tariff_id_seq; Type: SEQUENCE OWNED BY; Schema: ws; Owner: postgres
--

ALTER SEQUENCE ws.tariffs_tariff_id_seq OWNED BY ws.tariffs.tariff_id;


--
-- TOC entry 3573 (class 2604 OID 32828)
-- Name: customer_categories category_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.customer_categories ALTER COLUMN category_id SET DEFAULT nextval('public.customer_categories_category_id_seq'::regclass);


--
-- TOC entry 3571 (class 2604 OID 32798)
-- Name: auth_permissions p_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_permissions ALTER COLUMN p_id SET DEFAULT nextval('ws.auth_permissions_p_id_seq'::regclass);


--
-- TOC entry 3559 (class 2604 OID 24582)
-- Name: auth_roles r_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_roles ALTER COLUMN r_id SET DEFAULT nextval('ws.auth_roles_r_id_seq'::regclass);


--
-- TOC entry 3648 (class 2604 OID 33293)
-- Name: bill_details detail_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bill_details ALTER COLUMN detail_id SET DEFAULT nextval('ws.bill_details_detail_id_seq'::regclass);


--
-- TOC entry 3624 (class 2604 OID 33196)
-- Name: billing_cycles cycle_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.billing_cycles ALTER COLUMN cycle_id SET DEFAULT nextval('ws.billing_cycles_cycle_id_seq'::regclass);


--
-- TOC entry 3632 (class 2604 OID 33221)
-- Name: bills bill_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills ALTER COLUMN bill_id SET DEFAULT nextval('ws.bills_bill_id_seq'::regclass);


--
-- TOC entry 3619 (class 2604 OID 33182)
-- Name: charge_configurations config_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.charge_configurations ALTER COLUMN config_id SET DEFAULT nextval('ws.charge_configurations_config_id_seq'::regclass);


--
-- TOC entry 3588 (class 2604 OID 32942)
-- Name: communities community_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.communities ALTER COLUMN community_id SET DEFAULT nextval('ws.communities_community_id_seq'::regclass);


--
-- TOC entry 3612 (class 2604 OID 33103)
-- Name: consumption consumption_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.consumption ALTER COLUMN consumption_id SET DEFAULT nextval('ws.consumption_consumption_id_seq'::regclass);


--
-- TOC entry 3660 (class 2604 OID 33367)
-- Name: customer_balances balance_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_balances ALTER COLUMN balance_id SET DEFAULT nextval('ws.customer_balances_balance_id_seq'::regclass);


--
-- TOC entry 3574 (class 2604 OID 32840)
-- Name: customer_categories category_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_categories ALTER COLUMN category_id SET DEFAULT nextval('ws.customer_categories_category_id_seq'::regclass);


--
-- TOC entry 3697 (class 2604 OID 41445)
-- Name: customer_notification_preferences preference_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_notification_preferences ALTER COLUMN preference_id SET DEFAULT nextval('ws.customer_notification_preferences_preference_id_seq'::regclass);


--
-- TOC entry 3589 (class 2604 OID 32956)
-- Name: customers customer_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customers ALTER COLUMN customer_id SET DEFAULT nextval('ws.customers_customer_id_seq'::regclass);


--
-- TOC entry 3734 (class 2604 OID 41528)
-- Name: disconnection_history history_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnection_history ALTER COLUMN history_id SET DEFAULT nextval('ws.disconnection_history_history_id_seq'::regclass);


--
-- TOC entry 3668 (class 2604 OID 33392)
-- Name: disconnection_notices notice_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnection_notices ALTER COLUMN notice_id SET DEFAULT nextval('ws.disconnection_notices_notice_id_seq'::regclass);


--
-- TOC entry 3717 (class 2604 OID 41478)
-- Name: disconnections disconnection_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnections ALTER COLUMN disconnection_id SET DEFAULT nextval('ws.disconnections_disconnection_id_seq'::regclass);


--
-- TOC entry 3587 (class 2604 OID 32928)
-- Name: districts district_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.districts ALTER COLUMN district_id SET DEFAULT nextval('ws.districts_district_id_seq'::regclass);


--
-- TOC entry 3601 (class 2604 OID 33006)
-- Name: meter_assignments assignment_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meter_assignments ALTER COLUMN assignment_id SET DEFAULT nextval('ws.meter_assignments_assignment_id_seq'::regclass);


--
-- TOC entry 3605 (class 2604 OID 33035)
-- Name: meter_reports report_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meter_reports ALTER COLUMN report_id SET DEFAULT nextval('ws.meter_reports_report_id_seq'::regclass);


--
-- TOC entry 3594 (class 2604 OID 32989)
-- Name: meters meter_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meters ALTER COLUMN meter_id SET DEFAULT nextval('ws.meters_meter_id_seq'::regclass);


--
-- TOC entry 3736 (class 2604 OID 41546)
-- Name: notification_config config_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_config ALTER COLUMN config_id SET DEFAULT nextval('ws.notification_config_config_id_seq'::regclass);


--
-- TOC entry 3692 (class 2604 OID 41422)
-- Name: notification_logs log_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_logs ALTER COLUMN log_id SET DEFAULT nextval('ws.notification_logs_log_id_seq'::regclass);


--
-- TOC entry 3687 (class 2604 OID 41395)
-- Name: notification_queue queue_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_queue ALTER COLUMN queue_id SET DEFAULT nextval('ws.notification_queue_queue_id_seq'::regclass);


--
-- TOC entry 3675 (class 2604 OID 41374)
-- Name: notification_templates template_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_templates ALTER COLUMN template_id SET DEFAULT nextval('ws.notification_templates_template_id_seq'::regclass);


--
-- TOC entry 3657 (class 2604 OID 33344)
-- Name: payment_allocations allocation_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payment_allocations ALTER COLUMN allocation_id SET DEFAULT nextval('ws.payment_allocations_allocation_id_seq'::regclass);


--
-- TOC entry 3650 (class 2604 OID 33310)
-- Name: payments payment_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payments ALTER COLUMN payment_id SET DEFAULT nextval('ws.payments_payment_id_seq'::regclass);


--
-- TOC entry 3586 (class 2604 OID 32910)
-- Name: regions region_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.regions ALTER COLUMN region_id SET DEFAULT nextval('ws.regions_region_id_seq'::regclass);


--
-- TOC entry 3583 (class 2604 OID 32878)
-- Name: tariff_blocks block_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.tariff_blocks ALTER COLUMN block_id SET DEFAULT nextval('ws.tariff_blocks_block_id_seq'::regclass);


--
-- TOC entry 3577 (class 2604 OID 32852)
-- Name: tariffs tariff_id; Type: DEFAULT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.tariffs ALTER COLUMN tariff_id SET DEFAULT nextval('ws.tariffs_tariff_id_seq'::regclass);


--
-- TOC entry 4255 (class 0 OID 32825)
-- Dependencies: 257
-- Data for Name: customer_categories; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.customer_categories (category_id, category_name, description, created_at) FROM stdin;
\.


--
-- TOC entry 4211 (class 0 OID 16401)
-- Dependencies: 211
-- Data for Name: audit_log; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.audit_log (audit_id, table_name, operation, row_pk, changed_by, changed_at, old_data, new_data) FROM stdin;
\.


--
-- TOC entry 4215 (class 0 OID 16417)
-- Dependencies: 215
-- Data for Name: auth_roles; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.auth_roles (r_id, name, display_name, description, is_active, created_at, updated_at) FROM stdin;
1	super_admin	Super Administrator	Complete system control and user management	t	2025-08-31 21:12:33.757938	2025-08-31 21:12:33.757938
2	manager	Manager	Operations management, reports, and staff oversight	t	2025-08-31 21:12:33.757938	2025-08-31 21:12:33.757938
3	cashier	Cashier	Process sales transactions and customer service	t	2025-08-31 21:12:33.757938	2025-08-31 21:12:33.757938
4	inventory_clerk	Inventory Clerk	Stock management and product maintenance	t	2025-08-31 21:12:33.757938	2025-08-31 21:12:33.757938
5	accountant	Accountant	Financial reports and sales analysis	t	2025-08-31 21:12:33.757938	2025-08-31 21:12:33.757938
\.


--
-- TOC entry 4219 (class 0 OID 16436)
-- Dependencies: 219
-- Data for Name: brands; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.brands (brand_id, brand_name, description, is_active, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4221 (class 0 OID 16445)
-- Dependencies: 221
-- Data for Name: categories; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.categories (category_id, category_name, description, parent_id, is_active, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4223 (class 0 OID 16454)
-- Dependencies: 223
-- Data for Name: customers; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.customers (customer_id, full_name, phone, email, address, loyalty_points, is_active, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4225 (class 0 OID 16465)
-- Dependencies: 225
-- Data for Name: invoice_counters; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.invoice_counters (ym, last) FROM stdin;
\.


--
-- TOC entry 4226 (class 0 OID 16469)
-- Dependencies: 226
-- Data for Name: products; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.products (product_id, barcode, sku, product_name, description, unit_of_measure, category_id, brand_id, tax_id, cost_price, retail_price, stock_quantity, reorder_level, expiry_date, batch_number, is_active, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4228 (class 0 OID 16486)
-- Dependencies: 228
-- Data for Name: purchase_items; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.purchase_items (purchase_item_id, purchase_id, product_id, quantity, cost_price) FROM stdin;
\.


--
-- TOC entry 4230 (class 0 OID 16493)
-- Dependencies: 230
-- Data for Name: purchases; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.purchases (purchase_id, supplier_id, invoice_number, total_cost, notes, purchased_at, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4232 (class 0 OID 16504)
-- Dependencies: 232
-- Data for Name: refunds; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.refunds (refund_id, return_id, method, amount, txn_ref, note, refunded_at, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4234 (class 0 OID 16514)
-- Dependencies: 234
-- Data for Name: return_items; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.return_items (return_item_id, return_id, product_id, quantity, unit_price) FROM stdin;
\.


--
-- TOC entry 4236 (class 0 OID 16521)
-- Dependencies: 236
-- Data for Name: returns; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.returns (return_id, sale_id, cashier_id, reason, total_refund, return_time, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4238 (class 0 OID 16532)
-- Dependencies: 238
-- Data for Name: sales; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.sales (sale_id, invoice_number, customer_id, cashier_id, total_amount, discount_amount, tax_amount, payment_method, paid_amount, change_amount, sale_time, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4239 (class 0 OID 16548)
-- Dependencies: 239
-- Data for Name: sales_items; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.sales_items (sale_item_id, sale_id, product_id, quantity, unit_price, discount) FROM stdin;
\.


--
-- TOC entry 4242 (class 0 OID 16558)
-- Dependencies: 242
-- Data for Name: suppliers; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.suppliers (supplier_id, supplier_name, contact_person, phone, email, address, is_active, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4244 (class 0 OID 16567)
-- Dependencies: 244
-- Data for Name: taxes; Type: TABLE DATA; Schema: scpos; Owner: postgres
--

COPY scpos.taxes (tax_id, tax_name, tax_rate, is_active, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4252 (class 0 OID 32795)
-- Dependencies: 254
-- Data for Name: auth_permissions; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.auth_permissions (p_id, name, description, resource, action) FROM stdin;
\.


--
-- TOC entry 4253 (class 0 OID 32805)
-- Dependencies: 255
-- Data for Name: auth_role_permissions; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.auth_role_permissions (role_id, permission_id) FROM stdin;
\.


--
-- TOC entry 4247 (class 0 OID 24579)
-- Dependencies: 249
-- Data for Name: auth_roles; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.auth_roles (r_id, name, display_name, description, is_active, created_at, updated_at) FROM stdin;
1	super_admin	Super Administrator	Full system access and control	t	2025-10-04 08:02:23.690875	2025-10-04 08:02:23.690875
2	admin	Administrator	Can manage users and system settings	t	2025-10-04 08:02:23.690875	2025-10-04 08:02:23.690875
3	manager	Manager	Can manage customers and view reports	t	2025-10-04 08:02:23.690875	2025-10-04 08:02:23.690875
4	accountant	Accountant	Can manage bills, payments and view reports	t	2025-10-04 08:02:23.690875	2025-10-04 08:02:23.690875
5	technician	Technician	Can manage meters and readings	t	2025-10-04 08:02:23.690875	2025-10-04 08:02:23.690875
6	customer	Customer	Water service customer with limited access	t	2025-10-04 08:02:23.690875	2025-10-04 08:02:23.690875
7	guest	Guest	Read-only access to public information	t	2025-10-04 08:02:23.690875	2025-10-04 08:02:23.690875
8	accounts_clerk	Accounts Clerk	Views customer data and receives payments only	t	2025-10-04 08:48:02.04859	2025-10-04 08:48:02.04859
\.


--
-- TOC entry 4285 (class 0 OID 33290)
-- Dependencies: 287
-- Data for Name: bill_details; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.bill_details (detail_id, bill_id, charge_type, description, quantity, unit_price, amount, created_at) FROM stdin;
1	1	service_charge	Monthly Service Charge	1.00	10.00	10.00	2025-10-25 15:54:03.756325+00
2	1	block_1	0-11 m³ @ GHS 0.40/m³	4.00	0.40	1.60	2025-10-25 15:54:03.756325+00
3	2	service_charge	Monthly Service Charge	1.00	10.00	10.00	2025-10-25 15:54:03.756325+00
4	2	block_1	0-11 m³ @ GHS 0.40/m³	3.64	0.40	1.46	2025-10-25 15:54:03.756325+00
\.


--
-- TOC entry 4281 (class 0 OID 33193)
-- Dependencies: 283
-- Data for Name: billing_cycles; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.billing_cycles (cycle_id, cycle_month, status, total_customers_billed, total_bills_generated, total_amount_billed, failed_bills, started_at, completed_at, generated_by_user_id, notes, created_at) FROM stdin;
2	2025-10-01	open	2	2	23.06	0	2025-10-25 15:54:03.756325+00	2025-10-25 15:54:03.756325+00	3	\N	2025-10-25 15:47:24.140175+00
\.


--
-- TOC entry 4283 (class 0 OID 33218)
-- Dependencies: 285
-- Data for Name: bills; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.bills (bill_id, bill_no, customer_no, meter_no, consumption_id, tariff_id, cycle_id, billing_period, issue_date, due_date, prev_reading, curr_reading, consumption, service_charge, consumption_charge, subtotal, arrears_brought_forward, penalties, other_charges, total_amount, amount_paid, first_payment_date, last_payment_date, fully_paid_date, payment_status, bill_status, is_deleted, deleted_at, deleted_by_user_id, deletion_reason, generated_by_user_id, notes, created_at, updated_at) FROM stdin;
1	BILL-2025-000001	ACC002	0024240103	2	1	2	2025-10-01	2025-10-25	2025-11-24	0.00	4.00	4.00	10.00	1.60	11.60	0.00	0.00	0.00	11.60	11.60	2025-10-25	2025-10-25	2025-10-25	paid	issued	f	\N	\N	\N	3	\N	2025-10-25 15:54:03.756325+00	2025-10-25 18:15:57.929778+00
2	BILL-2025-000002	ACC003	0024240657	1	1	2	2025-10-01	2025-10-25	2025-11-24	0.00	3.64	3.64	10.00	1.46	11.46	0.00	0.00	0.00	11.46	11.46	2025-10-25	2025-10-25	2025-10-25	paid	issued	f	\N	\N	\N	3	\N	2025-10-25 15:54:03.756325+00	2025-10-25 18:19:16.845197+00
\.


--
-- TOC entry 4279 (class 0 OID 33179)
-- Dependencies: 281
-- Data for Name: charge_configurations; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.charge_configurations (config_id, charge_type, charge_name, calculation_method, fixed_amount, percentage_rate, applies_to, trigger_condition, is_active, effective_date, expiry_date, created_at, updated_at) FROM stdin;
1	late_payment_penalty	Late Payment Penalty	percentage	\N	2.00	all	overdue_days > 30	t	2025-01-01	\N	2025-10-25 13:58:59.660088+00	2025-10-25 13:58:59.660088+00
2	reconnection_fee	Reconnection Fee	fixed	\N	50.00	all	reconnection_request	t	2025-01-01	\N	2025-10-25 13:58:59.660088+00	2025-10-25 13:58:59.660088+00
3	meter_maintenance	Meter Maintenance Fee	fixed	\N	20.00	all	annual_maintenance	t	2025-01-01	\N	2025-10-25 13:58:59.660088+00	2025-10-25 13:58:59.660088+00
4	meter_rent	Monthly Meter Rent	fixed	5.00	\N	all	\N	f	2025-11-01	\N	2025-10-26 09:03:34.63493+00	2025-10-26 09:10:22.004496+00
\.


--
-- TOC entry 4267 (class 0 OID 32939)
-- Dependencies: 269
-- Data for Name: communities; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.communities (community_id, community_code, community_name, district_id, population, population_year, population_source) FROM stdin;
25	OSU	Osu	16	45000	2021	GSS Estimate
26	LABADI	La/Labadi	16	38500	2021	GSS Estimate
27	JAMESTOWN	James Town	16	25000	2021	GSS Estimate
28	TEMA-C1	Community 1 Tema	17	35000	2021	GSS Estimate
29	TEMA-C25	Community 25 Tema	17	28000	2021	GSS Estimate
30	ASHAIMAN	Ashaiman	17	150000	2021	GSS Estimate
31	AMASAMAN	Amasaman	18	45000	2021	GSS Estimate
32	MEDIE	Medie	18	32000	2021	GSS Estimate
33	POKUASE	Pokuase	18	28000	2021	GSS Estimate
34	ADUM	Adum	19	55000	2021	GSS Estimate
35	ASOKWA	Asokwa	19	48000	2021	GSS Estimate
36	TAFO	Tafo	19	62000	2021	GSS Estimate
37	OBUASI-TWN	Obuasi Town	20	85000	2021	GSS Estimate
38	BIDIESO	Bidieso	20	12000	2021	GSS Estimate
39	ANTOBOASE	Antoboase	20	8500	2021	GSS Estimate
40	EJISU	Ejisu	21	35000	2021	GSS Estimate
41	JUABEN	Juaben	21	28000	2021	GSS Estimate
42	BESEASE	Besease	21	15000	2021	GSS Estimate
43	SEKONDI	Sekondi	22	180000	2021	GSS Estimate
44	TAKORADI	Takoradi	22	200000	2021	GSS Estimate
45	EFFIAKUMA	Effia-Kuma	22	95000	2021	GSS Estimate
46	TARKWA	Tarkwa	23	55000	2021	GSS Estimate
47	NSUAEM	Nsuaem	23	18000	2021	GSS Estimate
48	AKOON	Akoon	23	12000	2021	GSS Estimate
49	PRESTEA	Prestea	24	45000	2021	GSS Estimate
50	HUNI-VAL	Huni Valley	24	35000	2021	GSS Estimate
51	BOGOSO	Bogoso	24	38000	2021	GSS Estimate
52	TAMALE-CBD	Tamale Central	25	85000	2021	GSS Estimate
53	SAGNARIGU	Sagnarigu	25	65000	2021	GSS Estimate
54	CHOGGU	Choggu	25	48000	2021	GSS Estimate
55	YENDI-TWN	Yendi Town	26	55000	2021	GSS Estimate
56	ZABZUGU	Zabzugu	26	28000	2021	GSS Estimate
57	SUNSON	Sunson	26	15000	2021	GSS Estimate
58	SAVELUGU	Savelugu	27	42000	2021	GSS Estimate
59	PONG-TML	Pong-Tamale	27	35000	2021	GSS Estimate
60	DIARE	Diare	27	22000	2021	GSS Estimate
61	KOFORIDUA	Koforidua	28	120000	2021	GSS Estimate
62	EFFIDUASE	Effiduase	28	35000	2021	GSS Estimate
63	OYOKO	Oyoko	28	25000	2021	GSS Estimate
64	AKIM-ODA	Akim Oda	29	85000	2021	GSS Estimate
65	ACHIASE	Achiase	29	28000	2021	GSS Estimate
66	APERADE	Aperade	29	18000	2021	GSS Estimate
67	NSAWAM	Nsawam	30	65000	2021	GSS Estimate
68	ADOAGYIRI	Adoagyiri	30	42000	2021	GSS Estimate
69	POKROM	Pokrom	30	28000	2021	GSS Estimate
70	WA-CENTRAL	Wa Central	31	65000	2021	GSS Estimate
71	KPERISI	Kperisi	31	18000	2021	GSS Estimate
72	BUSA	Busa	31	12000	2021	GSS Estimate
73	LAWRA	Lawra	32	45000	2021	GSS Estimate
74	EREMON	Eremon	32	22000	2021	GSS Estimate
75	KO	Ko	32	15000	2021	GSS Estimate
76	JIRAPA	Jirapa	33	38000	2021	GSS Estimate
77	HAN	Han	33	20000	2021	GSS Estimate
78	TUOPARE	Tuopare	33	18000	2021	GSS Estimate
79	BOLGA-CBD	Bolgatanga Central	34	75000	2021	GSS Estimate
80	SUMBRUNGU	Sumbrungu	34	28000	2021	GSS Estimate
81	ZUARUNGU	Zuarungu	34	22000	2021	GSS Estimate
82	BAWKU-CBD	Bawku Central	35	55000	2021	GSS Estimate
83	PUSIGA	Pusiga	35	20000	2021	GSS Estimate
84	ZEBILLA	Zebilla	35	18000	2021	GSS Estimate
85	NAVRONGO	Navrongo	36	62000	2021	GSS Estimate
86	PAGA	Paga	36	25000	2021	GSS Estimate
87	KAYORO	Kayoro	36	18000	2021	GSS Estimate
\.


--
-- TOC entry 4277 (class 0 OID 33100)
-- Dependencies: 279
-- Data for Name: consumption; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.consumption (consumption_id, customer_no, meter_no, prev_reading, curr_reading, reading_month, reading_source, screenshot_url, reader_id, bill_status, trash_status, created_at, verified_by_user_id, verified_at, updated_at) FROM stdin;
2	ACC002	0024240103	0.00	4.00	2025-10-01	manual	https://example.com/screenshots/reading123.jpg	3	t	f	2025-10-25 10:08:28.750933	3	2025-10-25 11:44:42.179247+00	2025-10-25 15:54:03.756325
1	ACC003	0024240657	0.00	3.64	2025-10-01	ultrasonic	\N	3	t	f	2025-10-25 10:08:28.750933	3	2025-10-25 11:47:23.53914+00	2025-10-25 15:54:03.756325
\.


--
-- TOC entry 4291 (class 0 OID 33364)
-- Dependencies: 293
-- Data for Name: customer_balances; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.customer_balances (balance_id, customer_no, current_balance, credit_balance, total_billed, total_paid, last_bill_date, last_bill_amount, last_payment_date, last_payment_amount, months_in_arrears, updated_at) FROM stdin;
1	ACC003	0.00	588.54	11.46	600.00	2025-10-25	11.46	2025-10-25	100.00	0	2025-10-25 18:20:06.228886+00
2	ACC002	0.00	438.90	11.60	450.50	2025-10-25	11.60	2025-10-25	200.00	0	2025-10-25 18:21:28.97285+00
\.


--
-- TOC entry 4257 (class 0 OID 32837)
-- Dependencies: 259
-- Data for Name: customer_categories; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.customer_categories (category_id, category_name, description, created_at, updated_at) FROM stdin;
2	commercial	Commercial businesses	2025-10-05 14:56:57.943392	2025-10-05 14:56:57.943392
3	industrial	Industrial customers	2025-10-05 14:57:21.112185	2025-10-05 14:57:21.112185
1	domestic	Domestic and residential water users updated	2025-10-05 14:50:50.786616	2025-10-05 16:16:54.193462
\.


--
-- TOC entry 4301 (class 0 OID 41442)
-- Dependencies: 303
-- Data for Name: customer_notification_preferences; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.customer_notification_preferences (preference_id, customer_no, receive_sms, receive_email, auth_sms, auth_email, bills_sms, bills_email, bill_reminders_sms, bill_reminders_email, payments_sms, payments_email, disconnection_sms, disconnection_email, marketing_sms, marketing_email, preferred_phone, preferred_email, quiet_hours_enabled, quiet_hours_start, quiet_hours_end, preferred_language, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4269 (class 0 OID 32953)
-- Dependencies: 271
-- Data for Name: customers; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.customers (customer_id, account_no, surname, other_names, customer_category_id, phone, email, house_no, street_name, community_id, status, created_at, updated_at) FROM stdin;
2	ACC003	Addo	\N	1	\N	\N	\N	\N	87	active	2025-10-06 21:21:42.411301+00	2025-10-06 21:21:42.411301+00
3	ACC002	Mensah	Raymond	1	+233244123456	ray@email.com	H123	Independence Avenue	87	active	2025-10-25 09:52:04.068993+00	2025-10-25 09:52:04.068993+00
\.


--
-- TOC entry 4305 (class 0 OID 41525)
-- Dependencies: 307
-- Data for Name: disconnection_history; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.disconnection_history (history_id, disconnection_id, action, old_status, new_status, action_notes, system_notes, performed_by_user_id, performed_by_name, action_data, created_at) FROM stdin;
\.


--
-- TOC entry 4293 (class 0 OID 33389)
-- Dependencies: 295
-- Data for Name: disconnection_notices; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.disconnection_notices (notice_id, customer_no, notice_type, notice_date, due_date, amount_owed, status, disconnected_date, disconnected_by_user_id, notes, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4303 (class 0 OID 41475)
-- Dependencies: 305
-- Data for Name: disconnections; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.disconnections (disconnection_id, disconnection_no, customer_no, meter_no, disconnection_method, reason, reason_details, amount_owed, bills_unpaid, oldest_unpaid_bill_date, requires_approval, requested_by_user_id, approved_by_user_id, approval_status, approved_at, rejected_at, rejection_reason, warning_sent, warning_sent_at, final_notice_sent, final_notice_sent_at, scheduled_disconnection_date, scheduled_disconnection_time, actual_disconnection_date, actual_disconnection_time, valve_command_sent, valve_command_sent_at, valve_command_payload, valve_status_confirmed, valve_status_confirmed_at, valve_response, valve_error_message, field_officer_user_id, field_officer_name, field_officer_phone, field_officer_assigned_at, field_officer_dispatched_at, physical_disconnection_completed, physical_disconnection_completed_at, physical_disconnection_method, physical_disconnection_photo_url, physical_disconnection_gps_lat, physical_disconnection_gps_lng, physical_disconnection_notes, reconnection_fee, reconnection_payment_received, reconnection_payment_id, reconnection_payment_date, reconnection_scheduled_date, reconnection_scheduled_time, reconnected_at, reconnection_method, reconnection_valve_command_sent, reconnection_valve_command_sent_at, reconnection_valve_confirmed, reconnection_valve_confirmed_at, reconnection_valve_response, reconnection_field_officer_user_id, reconnection_field_officer_name, reconnection_completed, reconnection_completed_at, reconnection_photo_url, reconnection_gps_lat, reconnection_gps_lng, reconnection_notes, status, disconnected_by_user_id, reconnected_by_user_id, cancelled_by_user_id, cancelled_at, cancellation_reason, internal_notes, customer_notes, created_at, updated_at) FROM stdin;
\.


--
-- TOC entry 4265 (class 0 OID 32925)
-- Dependencies: 267
-- Data for Name: districts; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.districts (district_id, district_code, district_name, region_id, population, population_year, population_source) FROM stdin;
16	AMA	Accra Metropolitan	6	2291352	2021	GSS 2021 Census
17	TEMA	Tema Metropolitan	6	402637	2021	GSS 2021 Census
18	GA-WEST	Ga West Municipal	6	262742	2021	GSS 2021 Census
19	KMA	Kumasi Metropolitan	7	2035064	2021	GSS 2021 Census
20	OBUASI	Obuasi Municipal	7	175043	2021	GSS 2021 Census
21	EJISU	Ejisu Municipal	7	143762	2021	GSS 2021 Census
22	STMA	Sekondi-Takoradi Metro	8	559548	2021	GSS 2021 Census
23	TARKWA	Tarkwa-Nsuaem Municipal	8	98532	2021	GSS 2021 Census
24	PRESTEA	Prestea Huni Valley	8	159815	2021	GSS 2021 Census
25	TAMALE	Tamale Metropolitan	9	371351	2021	GSS 2021 Census
26	YENDI	Yendi Municipal	9	117780	2021	GSS 2021 Census
27	SAVELUGU	Savelugu Municipal	9	139863	2021	GSS 2021 Census
28	KOFORIDUA	New-Juaben Municipal	10	202465	2021	GSS 2021 Census
29	AKIM-ODA	Birim Central Municipal	10	144869	2021	GSS 2021 Census
30	NSAWAM	Nsawam Adoagyiri	10	165881	2021	GSS 2021 Census
31	WA	Wa Municipal	11	102446	2021	GSS 2021 Census
32	LAWRA	Lawra Municipal	11	104125	2021	GSS 2021 Census
33	JIRAPA	Jirapa Municipal	11	88402	2021	GSS 2021 Census
34	BOLGA	Bolgatanga Municipal	12	131550	2021	GSS 2021 Census
35	BAWKU	Bawku Municipal	12	98538	2021	GSS 2021 Census
36	NAVRONGO	Kassena-Nankana Muni	12	109944	2021	GSS 2021 Census
\.


--
-- TOC entry 4273 (class 0 OID 33003)
-- Dependencies: 275
-- Data for Name: meter_assignments; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.meter_assignments (assignment_id, meter_id, customer_id, assigned_date, removed_date, location_details, is_active, removed_by_user_id, updated_at) FROM stdin;
1	3	2	2025-10-07	2025-10-08	Kitchen wall near sink	f	3	2025-10-08 20:26:23.076715
2	6	2	2025-10-07	2025-10-01	Kitchen wall, near sink	f	3	2025-10-08 21:03:04.124421
3	8	2	2025-10-25	\N	Behind Kitchen window	t	\N	2025-10-25 09:45:06.618886
4	7	3	2025-10-25	\N	ultrasonic dummy meter number	t	\N	2025-10-25 09:56:29.595365
\.


--
-- TOC entry 4275 (class 0 OID 33032)
-- Dependencies: 277
-- Data for Name: meter_reports; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.meter_reports (report_id, meter_no, reading_value, read_time, signal_strength, valve_state, pressure, flow, battery_low, tamper_status, export_status, flag_remark, raw_payload, created_at) FROM stdin;
1	MTR002	1250.5	2025-10-24 23:48:15	\N	\N	\N	\N	f	f	pending	Pending Export	\N	2025-10-24 23:48:15.792274
2	MTR001	1252.3	2025-10-24 15:00:00	85	1	3.5	2.3	f	f	pending	Pending Export	{"device_id": "IOT123", "firmware_version": "2.1.0"}	2025-10-24 23:49:30.358935
4	0024240657	3.642	2025-10-24 07:11:21	-51	0	0	0	f	f	exported	Exported to consumption on 2025-10-25 10:08:28	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761289881", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.642", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761289881", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.642", "signalStrength": "-51"}], "totalCount": "1"}]	2025-10-25 00:29:48.442296
3	0024240103	0	2025-07-01 06:04:22	-65	0	0	0	f	f	exported	Exported to consumption on 2025-10-25 10:08:28	[{"code": "0", "data": [{"IMEI": "869070072133120", "meterNo": "00000024240103", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1751349862", "tamperStatus": "0", "waterPressure": "0", "currentReading": "0", "signalStrength": "-65"}], "totalCount": "1"}]	2025-10-25 00:20:24.663537
5	0024240657	3.668	2025-10-26 07:11:21	-51	0	0	0	f	f	pending	Pending Export	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761462681", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.668", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761462681", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.668", "signalStrength": "-51"}], "totalCount": "1"}]	2025-10-26 07:15:05.873411
6	0024240657	3.72	2025-10-27 07:11:21	-51	0	0	0	f	f	pending	Pending Export	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761549081", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.72", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761549081", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.72", "signalStrength": "-51"}], "totalCount": "1"}]	2025-10-27 07:15:04.759455
7	0024240657	3.765	2025-10-28 07:11:21	-51	0	0	0	f	f	pending	Pending Export	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761635481", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.765", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761635481", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.765", "signalStrength": "-51"}], "totalCount": "1"}]	2025-10-28 19:00:05.246121
8	0024240657	3.84	2025-10-30 07:11:27	-51	0	0	0	f	f	pending	Pending Export	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761808287", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.84", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1761808287", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.84", "signalStrength": "-51"}], "totalCount": "1"}]	2025-10-30 08:00:06.167743
9	0024240657	3.841	2025-11-03 07:11:53	-51	0	0	0	f	f	pending	Pending Export	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1762153913", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.841", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1762153913", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.841", "signalStrength": "-51"}], "totalCount": "1"}]	2025-11-03 19:15:05.193323
10	0024240657	3.907	2025-11-04 07:11:53	-51	0	0	0	f	f	pending	Pending Export	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1762240313", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.907", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1762240313", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.907", "signalStrength": "-51"}], "totalCount": "1"}]	2025-11-04 18:45:09.247836
11	0024240657	3.916	2025-11-05 07:11:32	-51	0	0	0	f	f	pending	Pending Export	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1762326692", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.916", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1762326692", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.916", "signalStrength": "-51"}], "totalCount": "1"}]	2025-11-05 08:00:05.403802
12	0024240657	3.965	2025-11-06 07:11:53	-51	0	0	0	f	f	pending	Pending Export	[{"code": "0", "data": [{"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "101", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1762413113", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.965", "signalStrength": "-51"}, {"IMEI": "869070072006847", "meterNo": "00000024240657", "userCode": "001", "batteryLow": "0", "valveState": "0", "currentFlow": "0", "sysReadTime": "1762413113", "tamperStatus": "0", "waterPressure": "0", "currentReading": "3.965", "signalStrength": "-51"}], "totalCount": "1"}]	2025-11-06 07:15:04.4736
\.


--
-- TOC entry 4271 (class 0 OID 32986)
-- Dependencies: 273
-- Data for Name: meters; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.meters (meter_id, meter_no, imei_no, reading_mode, technology, gps_latitude, gps_longitude, status, created_at, updated_at) FROM stdin;
4	MTR001-UPDATED	987654321098765	automatic	electromagnetic	5.6100000	-0.1900000	faulty	2025-10-07 19:59:31.531231+00	2025-10-07 21:46:44.63587+00
2	MTR001	\N	automatic	ultrasonic	\N	\N	installed	2025-10-07 19:53:54.046877+00	2025-10-07 21:47:09.341258+00
3	MTR002	123456789012345	automatic	ultrasonic	5.6037170	-0.1869640	installed	2025-10-07 19:59:19.254489+00	2025-10-07 22:20:50.18233+00
6	TEST-DELETE-MTR	\N	manual	manual	\N	\N	installed	2025-10-07 22:03:23.484236+00	2025-10-07 22:23:47.915658+00
8	0024240657	\N	automatic	ultrasonic	\N	\N	installed	2025-10-25 00:29:29.358545+00	2025-10-25 09:45:06.618886+00
7	0024240103	\N	automatic	ultrasonic	\N	\N	installed	2025-10-25 00:20:02.656701+00	2025-10-25 09:56:29.595365+00
\.


--
-- TOC entry 4307 (class 0 OID 41543)
-- Dependencies: 309
-- Data for Name: notification_config; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.notification_config (config_id, config_key, config_value, config_type, description, is_system, created_at, updated_at) FROM stdin;
2	sms_api_url		string	Arkesel API endpoint URL	t	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
3	sms_api_key		string	Arkesel API key (encrypted)	t	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
4	sms_sender_id	FlowBeam	string	SMS sender name	t	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
8	notification_retry_attempts	3	integer	Max retry attempts for failed notifications	t	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
9	notification_retry_delay	300	integer	Delay between retries in seconds	t	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
10	enable_notification_queue	true	boolean	Enable notification queue processing	t	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
11	enable_quiet_hours	true	boolean	Respect customer quiet hours	t	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
12	reconnection_fee	50.00	string	Standard reconnection fee in GHS	f	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
5	email_provider	postmark	string	Email provider type	t	2025-10-26 19:58:31.194733+00	2025-10-27 22:13:57.308516+00
14	postmark_api_url	https://api.postmarkapp.com	string	Postmark API base endpoint	t	2025-10-27 22:13:57.308516+00	2025-10-27 22:13:57.308516+00
15	postmark_message_stream	outbound	string	Postmark message stream: outbound (transactional) or broadcast (marketing)	t	2025-10-27 22:13:57.308516+00	2025-10-27 22:13:57.308516+00
16	postmark_track_opens	true	boolean	Track email opens	t	2025-10-27 22:13:57.308516+00	2025-10-27 22:13:57.308516+00
17	postmark_track_links	HtmlAndText	string	Track link clicks: None, HtmlAndText, HtmlOnly, TextOnly	t	2025-10-27 22:13:57.308516+00	2025-10-27 22:13:57.308516+00
18	email_footer_text	FlowBeam Water Services\nPhone: +233 XX XXX XXXX\nEmail: support@flowbeam.app\nAddress: Accra, Ghana	string	Default email footer text	f	2025-10-27 22:13:57.308516+00	2025-10-27 22:13:57.308516+00
19	email_logo_url	https://flowbeam.app/logo.png	string	Company logo URL for emails	f	2025-10-27 22:13:57.308516+00	2025-10-27 22:13:57.308516+00
22	email_tag	flowbeam-notifications	string	Tag for categorizing emails in Postmark	f	2025-10-27 22:13:57.308516+00	2025-10-27 22:13:57.308516+00
13	postmark_server_token	43da632f-da16-4486-83c7-ff159369c706	string	Postmark Server API Token (store encrypted in production)	t	2025-10-27 22:13:57.308516+00	2025-10-27 22:32:04.358176+00
21	email_reply_to	support@flowbeam.app	string	Reply-to email address	f	2025-10-27 22:13:57.308516+00	2025-10-27 22:34:19.213398+00
20	email_support_email	support@flowbeam.app	string	Support email address	f	2025-10-27 22:13:57.308516+00	2025-10-27 22:34:19.213398+00
6	email_from_address	mailuser@flowbeam.app	string	Default from email address	t	2025-10-26 19:58:31.194733+00	2025-11-08 14:42:13.737669+00
7	email_from_name	FlowBeam Water	string	Default from name	t	2025-10-26 19:58:31.194733+00	2025-11-08 14:42:13.737669+00
1	sms_provider	arkesel	string	SMS gateway provider	t	2025-10-26 19:58:31.194733+00	2025-11-08 14:42:13.737669+00
33	sms_enabled	true	boolean	Enable SMS notifications	t	2025-11-08 14:42:13.737669+00	2025-11-08 14:42:13.737669+00
34	arkesel_api_key	akVuaGFkeVVlTWNKVGZhR3hHcE4	string	Arkesel API key	t	2025-11-08 14:42:13.737669+00	2025-11-08 14:42:13.737669+00
35	arkesel_sender_id	CWSA	string	Arkesel sender ID/name	t	2025-11-08 14:42:13.737669+00	2025-11-08 14:42:13.737669+00
36	arkesel_api_url	https://sms.arkesel.com/api/v2/sms/send	string	Arkesel API endpoint	t	2025-11-08 14:42:13.737669+00	2025-11-08 14:42:13.737669+00
37	arkesel_balance_url	https://sms.arkesel.com/api/v2/clients/balance-details	string	Arkesel balance check endpoint	t	2025-11-08 14:42:13.737669+00	2025-11-08 14:42:13.737669+00
38	sms_retry_attempts	3	integer	Max retry attempts for failed SMS	t	2025-11-08 14:42:13.737669+00	2025-11-08 14:42:13.737669+00
39	sms_retry_delay	300	integer	Delay between retries in seconds	t	2025-11-08 14:42:13.737669+00	2025-11-08 14:42:13.737669+00
40	sms_callback_url		string	Webhook URL for delivery reports	f	2025-11-08 14:42:13.737669+00	2025-11-08 14:42:13.737669+00
\.


--
-- TOC entry 4299 (class 0 OID 41419)
-- Dependencies: 301
-- Data for Name: notification_logs; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.notification_logs (log_id, queue_id, customer_no, recipient, channel, notification_type, event_type, subject, message, status, provider, provider_response, provider_message_id, sms_cost, sms_units, sms_network, email_provider, email_opened, email_opened_at, email_clicked, email_clicked_at, error_code, error_message, sent_at, delivered_at, failed_at, created_at) FROM stdin;
\.


--
-- TOC entry 4297 (class 0 OID 41392)
-- Dependencies: 299
-- Data for Name: notification_queue; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.notification_queue (queue_id, customer_no, recipient_name, recipient_phone, recipient_email, notification_type, event_type, template_code, reference_type, reference_id, send_sms, send_email, template_data, priority, status, scheduled_at, attempts, max_attempts, last_attempt_at, next_retry_at, error_message, processed_at, created_by_user_id, created_at) FROM stdin;
\.


--
-- TOC entry 4295 (class 0 OID 41371)
-- Dependencies: 297
-- Data for Name: notification_templates; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.notification_templates (template_id, template_code, template_name, template_category, sms_enabled, sms_template, email_enabled, email_subject, email_body, available_variables, example_data, is_system_template, is_active, priority, created_by_user_id, created_at, updated_at) FROM stdin;
1	welcome_new_user	Welcome New User	authentication	t	Welcome to FlowBeam Water, {{customer_name}}! Your account {{account_no}} is now active. Download our app: {{app_link}}	t	Welcome to FlowBeam Water Services	Dear {{customer_name}},\\n\\nWelcome to FlowBeam Water! Your account {{account_no}} has been successfully created.\\n\\nYou can now:\\n- View your bills online\\n- Make payments\\n- Track your consumption\\n\\nDownload our app: {{app_link}}\\n\\nBest regards,\\nFlowBeam Water Team	["customer_name", "account_no", "app_link"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
2	password_reset	Password Reset	authentication	t	FlowBeam: Your password reset code is {{reset_code}}. Valid for 15 minutes. Do not share this code.	t	Password Reset Request	Dear {{customer_name}},\\n\\nYou requested a password reset for your FlowBeam account.\\n\\nReset Code: {{reset_code}}\\n\\nThis code is valid for 15 minutes.\\n\\nIf you did not request this, please ignore this message.\\n\\nBest regards,\\nFlowBeam Water Team	["customer_name", "reset_code"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
3	bill_generated	New Bill Generated	billing	t	FlowBeam: Your {{billing_month}} water bill of GHS {{total_amount}} is ready. Due: {{due_date}}. View: {{bill_link}}	t	Your Water Bill for {{billing_month}}	Dear {{customer_name}},\\n\\nYour water bill for {{billing_month}} is now available.\\n\\nBill No: {{bill_no}}\\nAmount: GHS {{total_amount}}\\nDue Date: {{due_date}}\\n\\nConsumption: {{consumption}} m³\\n\\nView and pay online: {{bill_link}}\\n\\nThank you,\\nFlowBeam Water	["customer_name", "billing_month", "bill_no", "total_amount", "due_date", "consumption", "bill_link"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
4	bill_due_reminder	Bill Due Reminder	billing	t	FlowBeam Reminder: Your bill of GHS {{amount}} is due on {{due_date}}. Pay now to avoid penalties. {{payment_link}}	t	Reminder: Bill Due Soon	Dear {{customer_name}},\\n\\nThis is a friendly reminder that your bill is due soon.\\n\\nBill No: {{bill_no}}\\nAmount: GHS {{amount}}\\nDue Date: {{due_date}}\\n\\nPay online: {{payment_link}}\\n\\nThank you,\\nFlowBeam Water	["customer_name", "bill_no", "amount", "due_date", "payment_link"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
5	bill_overdue	Bill Overdue	billing	t	FlowBeam ALERT: Your bill of GHS {{amount}} is overdue. Pay now to avoid disconnection. {{payment_link}}	t	OVERDUE: Immediate Payment Required	Dear {{customer_name}},\\n\\nYour bill is now OVERDUE.\\n\\nBill No: {{bill_no}}\\nAmount Owed: GHS {{amount}}\\nDays Overdue: {{days_overdue}}\\n\\nPlease pay immediately to avoid service disconnection.\\n\\nPay online: {{payment_link}}\\n\\nFlowBeam Water	["customer_name", "bill_no", "amount", "days_overdue", "payment_link"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
6	payment_received	Payment Received	payment	t	FlowBeam: Payment of GHS {{amount}} received. Receipt: {{receipt_no}}. Thank you!	t	Payment Confirmation - Receipt {{receipt_no}}	Dear {{customer_name}},\\n\\nWe confirm receipt of your payment.\\n\\nReceipt No: {{receipt_no}}\\nAmount Paid: GHS {{amount}}\\nPayment Date: {{payment_date}}\\nPayment Method: {{payment_method}}\\n\\nYour new balance: GHS {{new_balance}}\\n\\nThank you for your payment!\\n\\nFlowBeam Water	["customer_name", "receipt_no", "amount", "payment_date", "payment_method", "new_balance"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
7	disconnection_warning	Disconnection Warning	disconnection	t	FlowBeam WARNING: Your service may be disconnected due to GHS {{amount_owed}} unpaid for {{days_overdue}} days. Pay by {{deadline}} to avoid disconnection.	t	IMPORTANT: Risk of Service Disconnection	Dear {{customer_name}},\\n\\nThis is an official warning that your water service is at risk of disconnection.\\n\\nAmount Owed: GHS {{amount_owed}}\\nUnpaid Bills: {{bills_count}}\\nDays Overdue: {{days_overdue}}\\n\\nDeadline to Pay: {{deadline}}\\n\\nPay immediately: {{payment_link}}\\n\\nFailure to pay will result in service disconnection.\\n\\nFlowBeam Water	["customer_name", "amount_owed", "bills_count", "days_overdue", "deadline", "payment_link"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
8	disconnection_final_notice	Final Disconnection Notice	disconnection	t	FlowBeam FINAL NOTICE: Your service will be DISCONNECTED on {{disconnection_date}} if GHS {{amount_owed}} is not paid. Act NOW!	t	FINAL NOTICE: Service Disconnection Scheduled	Dear {{customer_name}},\\n\\nFINAL NOTICE OF DISCONNECTION\\n\\nYour water service will be DISCONNECTED on {{disconnection_date}} at {{disconnection_time}}.\\n\\nAmount Owed: GHS {{amount_owed}}\\nUnpaid Bills: {{bills_count}}\\n\\nThis is your last opportunity to avoid disconnection.\\n\\nPay NOW: {{payment_link}}\\n\\nFlowBeam Water	["customer_name", "disconnection_date", "disconnection_time", "amount_owed", "bills_count", "payment_link"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
9	disconnection_executed	Service Disconnected	disconnection	t	FlowBeam: Your service has been disconnected due to non-payment of GHS {{amount_owed}}. Pay {{total_to_reconnect}} (including reconnection fee) to restore service.	t	Service Disconnection Notice	Dear {{customer_name}},\\n\\nYour water service has been disconnected.\\n\\nReason: {{reason}}\\nAmount Owed: GHS {{amount_owed}}\\nReconnection Fee: GHS {{reconnection_fee}}\\nTotal to Reconnect: GHS {{total_to_reconnect}}\\n\\nTo restore service, please pay the full amount.\\n\\nPay online: {{payment_link}}\\n\\nFlowBeam Water	["customer_name", "reason", "amount_owed", "reconnection_fee", "total_to_reconnect", "payment_link"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
10	reconnection_scheduled	Reconnection Scheduled	disconnection	t	FlowBeam: Payment received! Your service reconnection is scheduled for {{reconnection_date}} at {{reconnection_time}}.	t	Service Reconnection Scheduled	Dear {{customer_name}},\\n\\nGood news! Your payment has been received and your water service reconnection is scheduled.\\n\\nReconnection Date: {{reconnection_date}}\\nReconnection Time: {{reconnection_time}}\\n\\nThank you for settling your account.\\n\\nFlowBeam Water	["customer_name", "reconnection_date", "reconnection_time"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
11	reconnection_completed	Service Reconnected	disconnection	t	FlowBeam: Your water service has been successfully reconnected. Thank you for your payment!	t	Service Reconnection Completed	Dear {{customer_name}},\\n\\nYour water service has been successfully reconnected.\\n\\nReconnection Date: {{reconnection_date}}\\nReconnection Time: {{reconnection_time}}\\n\\nWelcome back! We encourage you to pay bills on time to avoid future disconnections.\\n\\nFlowBeam Water	["customer_name", "reconnection_date", "reconnection_time"]	\N	t	t	5	\N	2025-10-26 19:58:31.194733+00	2025-10-26 19:58:31.194733+00
\.


--
-- TOC entry 4289 (class 0 OID 33341)
-- Dependencies: 291
-- Data for Name: payment_allocations; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.payment_allocations (allocation_id, payment_id, bill_id, allocated_amount, created_at) FROM stdin;
1	1	1	11.60	2025-10-25 18:15:57.929778+00
2	2	2	11.46	2025-10-25 18:19:16.845197+00
\.


--
-- TOC entry 4287 (class 0 OID 33307)
-- Dependencies: 289
-- Data for Name: payments; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.payments (payment_id, payment_no, customer_no, payment_date, amount, payment_method, reference_no, momo_transaction_id, momo_phone_number, momo_network, momo_status, momo_provider_reference, momo_callback_data, bank_name, bank_account_number, bank_transaction_date, received_by_user_id, notes, payment_status, created_at, updated_at) FROM stdin;
1	PAY-2025-000001	ACC002	2025-10-25	250.50	cash	CASH-001	\N	\N	\N	\N	\N	\N	\N	\N	\N	3	Payment for October bill	confirmed	2025-10-25 18:15:57.929778+00	2025-10-25 18:15:57.929778+00
2	PAY-2025-000002	ACC003	2025-10-25	500.00	mobile_money	MOMO123456	MTN202510250001	0241234567	MTN	successful	REF789XYZ	\N	\N	\N	\N	3	MTN Mobile Money payment	confirmed	2025-10-25 18:19:16.845197+00	2025-10-25 18:19:16.845197+00
3	PAY-2025-000003	ACC003	2025-10-25	100.00	mobile_money	MOMO1234566	MTN202510250002	0241234567	MTN	successful	REF789XYZY	\N	\N	\N	\N	3	MTN Mobile Money payment	confirmed	2025-10-25 18:20:06.228886+00	2025-10-25 18:20:06.228886+00
4	PAY-2025-000004	ACC002	2025-10-25	200.00	bank_transfer	TRF20251025001	\N	\N	\N	\N	\N	\N	GCB Bank	1234567890	2025-10-25	3	Bank transfer payment	confirmed	2025-10-25 18:21:28.97285+00	2025-10-25 18:21:28.97285+00
\.


--
-- TOC entry 4263 (class 0 OID 32907)
-- Dependencies: 265
-- Data for Name: regions; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.regions (region_id, region_code, region_name, population, population_year, population_source) FROM stdin;
6	GA	Greater Accra Region	5455692	2021	Ghana Statistical Service 2021 Census
7	AS	Ashanti Region	5792187	2021	Ghana Statistical Service 2021 Census
8	WR	Western Region	2060585	2021	Ghana Statistical Service 2021 Census
9	NR	Northern Region	2479461	2021	Ghana Statistical Service 2021 Census
10	ER	Eastern Region	2916350	2021	Ghana Statistical Service 2021 Census
11	UW	Upper West Region	897279	2021	Ghana Statistical Service 2021 Census
12	UE	Upper East Region	1301217	2021	Ghana Statistical Service 2021 Census
\.


--
-- TOC entry 4261 (class 0 OID 32875)
-- Dependencies: 263
-- Data for Name: tariff_blocks; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.tariff_blocks (block_id, tariff_id, min_volume, max_volume, unit_price, created_at, updated_at) FROM stdin;
2	1	16	25	0.55	2025-10-09 07:00:00.08683	2025-10-24 07:21:19.543394
1	1	0	11	0.40	2025-10-09 06:58:57.731575	2025-10-24 07:22:24.845807
\.


--
-- TOC entry 4259 (class 0 OID 32849)
-- Dependencies: 261
-- Data for Name: tariffs; Type: TABLE DATA; Schema: ws; Owner: postgres
--

COPY ws.tariffs (tariff_id, category_id, tariff_name, unit_price, service_charge, effective_date, expiry_date, status, created_at, updated_at) FROM stdin;
2	2	Commercial Tiered Rate 2025	\N	10.00	2025-11-01	2026-10-31	active	2025-10-08 21:32:25.120277	2025-10-08 22:55:05.746764
1	1	Domestic Standard Rate 2025 (Revised)	0.60	10.00	2025-10-01	2026-09-30	active	2025-10-08 21:30:17.247362	2025-10-25 15:53:43.203453
\.



--
-- TOC entry 3843 (class 2606 OID 32835)
-- Name: customer_categories customer_categories_category_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.customer_categories
    ADD CONSTRAINT customer_categories_category_name_key UNIQUE (category_name);


--
-- TOC entry 3845 (class 2606 OID 32833)
-- Name: customer_categories customer_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.customer_categories
    ADD CONSTRAINT customer_categories_pkey PRIMARY KEY (category_id);


--
-- TOC entry 3741 (class 2606 OID 16587)
-- Name: audit_log audit_log_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.audit_log
    ADD CONSTRAINT audit_log_pkey PRIMARY KEY (audit_id);


--
-- TOC entry 3752 (class 2606 OID 16591)
-- Name: auth_roles auth_roles_name_key; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.auth_roles
    ADD CONSTRAINT auth_roles_name_key UNIQUE (name);


--
-- TOC entry 3754 (class 2606 OID 16593)
-- Name: auth_roles auth_roles_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.auth_roles
    ADD CONSTRAINT auth_roles_pkey PRIMARY KEY (r_id);


--
-- TOC entry 3763 (class 2606 OID 16601)
-- Name: brands brands_brand_name_key; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.brands
    ADD CONSTRAINT brands_brand_name_key UNIQUE (brand_name);


--
-- TOC entry 3765 (class 2606 OID 16603)
-- Name: brands brands_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.brands
    ADD CONSTRAINT brands_pkey PRIMARY KEY (brand_id);


--
-- TOC entry 3767 (class 2606 OID 16605)
-- Name: categories categories_category_name_key; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.categories
    ADD CONSTRAINT categories_category_name_key UNIQUE (category_name);


--
-- TOC entry 3769 (class 2606 OID 16607)
-- Name: categories categories_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.categories
    ADD CONSTRAINT categories_pkey PRIMARY KEY (category_id);


--
-- TOC entry 3771 (class 2606 OID 16609)
-- Name: customers customers_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (customer_id);


--
-- TOC entry 3774 (class 2606 OID 16611)
-- Name: invoice_counters invoice_counters_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.invoice_counters
    ADD CONSTRAINT invoice_counters_pkey PRIMARY KEY (ym);


--
-- TOC entry 3781 (class 2606 OID 16613)
-- Name: products products_barcode_key; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.products
    ADD CONSTRAINT products_barcode_key UNIQUE (barcode);


--
-- TOC entry 3783 (class 2606 OID 16615)
-- Name: products products_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.products
    ADD CONSTRAINT products_pkey PRIMARY KEY (product_id);


--
-- TOC entry 3785 (class 2606 OID 16617)
-- Name: products products_sku_key; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.products
    ADD CONSTRAINT products_sku_key UNIQUE (sku);


--
-- TOC entry 3789 (class 2606 OID 16619)
-- Name: purchase_items purchase_items_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.purchase_items
    ADD CONSTRAINT purchase_items_pkey PRIMARY KEY (purchase_item_id);


--
-- TOC entry 3793 (class 2606 OID 16621)
-- Name: purchases purchases_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.purchases
    ADD CONSTRAINT purchases_pkey PRIMARY KEY (purchase_id);


--
-- TOC entry 3795 (class 2606 OID 16623)
-- Name: refunds refunds_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.refunds
    ADD CONSTRAINT refunds_pkey PRIMARY KEY (refund_id);


--
-- TOC entry 3798 (class 2606 OID 16625)
-- Name: return_items return_items_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.return_items
    ADD CONSTRAINT return_items_pkey PRIMARY KEY (return_item_id);


--
-- TOC entry 3800 (class 2606 OID 16627)
-- Name: returns returns_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.returns
    ADD CONSTRAINT returns_pkey PRIMARY KEY (return_id);


--
-- TOC entry 3806 (class 2606 OID 16629)
-- Name: sales sales_invoice_number_key; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.sales
    ADD CONSTRAINT sales_invoice_number_key UNIQUE (invoice_number);


--
-- TOC entry 3812 (class 2606 OID 16631)
-- Name: sales_items sales_items_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.sales_items
    ADD CONSTRAINT sales_items_pkey PRIMARY KEY (sale_item_id);


--
-- TOC entry 3808 (class 2606 OID 16633)
-- Name: sales sales_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.sales
    ADD CONSTRAINT sales_pkey PRIMARY KEY (sale_id);


--
-- TOC entry 3815 (class 2606 OID 16635)
-- Name: suppliers suppliers_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.suppliers
    ADD CONSTRAINT suppliers_pkey PRIMARY KEY (supplier_id);


--
-- TOC entry 3817 (class 2606 OID 16637)
-- Name: taxes taxes_pkey; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.taxes
    ADD CONSTRAINT taxes_pkey PRIMARY KEY (tax_id);


--
-- TOC entry 3819 (class 2606 OID 16639)
-- Name: taxes taxes_tax_name_key; Type: CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.taxes
    ADD CONSTRAINT taxes_tax_name_key UNIQUE (tax_name);


--
-- TOC entry 3837 (class 2606 OID 32804)
-- Name: auth_permissions auth_permissions_name_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_permissions
    ADD CONSTRAINT auth_permissions_name_key UNIQUE (name);


--
-- TOC entry 3839 (class 2606 OID 32802)
-- Name: auth_permissions auth_permissions_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_permissions
    ADD CONSTRAINT auth_permissions_pkey PRIMARY KEY (p_id);


--
-- TOC entry 3841 (class 2606 OID 32809)
-- Name: auth_role_permissions auth_role_permissions_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_role_permissions
    ADD CONSTRAINT auth_role_permissions_pkey PRIMARY KEY (role_id, permission_id);


--
-- TOC entry 3821 (class 2606 OID 24591)
-- Name: auth_roles auth_roles_name_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_roles
    ADD CONSTRAINT auth_roles_name_key UNIQUE (name);


--
-- TOC entry 3823 (class 2606 OID 24589)
-- Name: auth_roles auth_roles_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_roles
    ADD CONSTRAINT auth_roles_pkey PRIMARY KEY (r_id);


--
-- TOC entry 3913 (class 2606 OID 33298)
-- Name: bill_details bill_details_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bill_details
    ADD CONSTRAINT bill_details_pkey PRIMARY KEY (detail_id);


--
-- TOC entry 3893 (class 2606 OID 33209)
-- Name: billing_cycles billing_cycles_cycle_month_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.billing_cycles
    ADD CONSTRAINT billing_cycles_cycle_month_key UNIQUE (cycle_month);


--
-- TOC entry 3895 (class 2606 OID 33207)
-- Name: billing_cycles billing_cycles_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.billing_cycles
    ADD CONSTRAINT billing_cycles_pkey PRIMARY KEY (cycle_id);


--
-- TOC entry 3899 (class 2606 OID 33242)
-- Name: bills bills_bill_no_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_bill_no_key UNIQUE (bill_no);


--
-- TOC entry 3901 (class 2606 OID 33240)
-- Name: bills bills_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_pkey PRIMARY KEY (bill_id);


--
-- TOC entry 3889 (class 2606 OID 33188)
-- Name: charge_configurations charge_configurations_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.charge_configurations
    ADD CONSTRAINT charge_configurations_pkey PRIMARY KEY (config_id);


--
-- TOC entry 3863 (class 2606 OID 32946)
-- Name: communities communities_community_code_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.communities
    ADD CONSTRAINT communities_community_code_key UNIQUE (community_code);


--
-- TOC entry 3865 (class 2606 OID 32944)
-- Name: communities communities_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.communities
    ADD CONSTRAINT communities_pkey PRIMARY KEY (community_id);


--
-- TOC entry 3885 (class 2606 OID 33110)
-- Name: consumption consumption_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.consumption
    ADD CONSTRAINT consumption_pkey PRIMARY KEY (consumption_id);


--
-- TOC entry 3933 (class 2606 OID 33378)
-- Name: customer_balances customer_balances_customer_no_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_balances
    ADD CONSTRAINT customer_balances_customer_no_key UNIQUE (customer_no);


--
-- TOC entry 3935 (class 2606 OID 33376)
-- Name: customer_balances customer_balances_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_balances
    ADD CONSTRAINT customer_balances_pkey PRIMARY KEY (balance_id);


--
-- TOC entry 3847 (class 2606 OID 32847)
-- Name: customer_categories customer_categories_category_name_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_categories
    ADD CONSTRAINT customer_categories_category_name_key UNIQUE (category_name);


--
-- TOC entry 3849 (class 2606 OID 32845)
-- Name: customer_categories customer_categories_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_categories
    ADD CONSTRAINT customer_categories_pkey PRIMARY KEY (category_id);


--
-- TOC entry 3966 (class 2606 OID 41467)
-- Name: customer_notification_preferences customer_notification_preferences_customer_no_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_notification_preferences
    ADD CONSTRAINT customer_notification_preferences_customer_no_key UNIQUE (customer_no);


--
-- TOC entry 3968 (class 2606 OID 41465)
-- Name: customer_notification_preferences customer_notification_preferences_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_notification_preferences
    ADD CONSTRAINT customer_notification_preferences_pkey PRIMARY KEY (preference_id);


--
-- TOC entry 3867 (class 2606 OID 32966)
-- Name: customers customers_account_no_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customers
    ADD CONSTRAINT customers_account_no_key UNIQUE (account_no);


--
-- TOC entry 3869 (class 2606 OID 32964)
-- Name: customers customers_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (customer_id);


--
-- TOC entry 3982 (class 2606 OID 41533)
-- Name: disconnection_history disconnection_history_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnection_history
    ADD CONSTRAINT disconnection_history_pkey PRIMARY KEY (history_id);


--
-- TOC entry 3940 (class 2606 OID 33402)
-- Name: disconnection_notices disconnection_notices_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnection_notices
    ADD CONSTRAINT disconnection_notices_pkey PRIMARY KEY (notice_id);


--
-- TOC entry 3971 (class 2606 OID 41501)
-- Name: disconnections disconnections_disconnection_no_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnections
    ADD CONSTRAINT disconnections_disconnection_no_key UNIQUE (disconnection_no);


--
-- TOC entry 3973 (class 2606 OID 41499)
-- Name: disconnections disconnections_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnections
    ADD CONSTRAINT disconnections_pkey PRIMARY KEY (disconnection_id);


--
-- TOC entry 3859 (class 2606 OID 32932)
-- Name: districts districts_district_code_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.districts
    ADD CONSTRAINT districts_district_code_key UNIQUE (district_code);


--
-- TOC entry 3861 (class 2606 OID 32930)
-- Name: districts districts_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.districts
    ADD CONSTRAINT districts_pkey PRIMARY KEY (district_id);


--
-- TOC entry 3881 (class 2606 OID 33012)
-- Name: meter_assignments meter_assignments_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meter_assignments
    ADD CONSTRAINT meter_assignments_pkey PRIMARY KEY (assignment_id);


--
-- TOC entry 3883 (class 2606 OID 33045)
-- Name: meter_reports meter_reports_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meter_reports
    ADD CONSTRAINT meter_reports_pkey PRIMARY KEY (report_id);


--
-- TOC entry 3875 (class 2606 OID 33001)
-- Name: meters meters_imei_no_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meters
    ADD CONSTRAINT meters_imei_no_key UNIQUE (imei_no);


--
-- TOC entry 3877 (class 2606 OID 32999)
-- Name: meters meters_meter_no_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meters
    ADD CONSTRAINT meters_meter_no_key UNIQUE (meter_no);


--
-- TOC entry 3879 (class 2606 OID 32997)
-- Name: meters meters_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meters
    ADD CONSTRAINT meters_pkey PRIMARY KEY (meter_id);


--
-- TOC entry 3987 (class 2606 OID 41555)
-- Name: notification_config notification_config_config_key_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_config
    ADD CONSTRAINT notification_config_config_key_key UNIQUE (config_key);


--
-- TOC entry 3989 (class 2606 OID 41553)
-- Name: notification_config notification_config_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_config
    ADD CONSTRAINT notification_config_pkey PRIMARY KEY (config_id);


--
-- TOC entry 3964 (class 2606 OID 41430)
-- Name: notification_logs notification_logs_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_logs
    ADD CONSTRAINT notification_logs_pkey PRIMARY KEY (log_id);


--
-- TOC entry 3957 (class 2606 OID 41407)
-- Name: notification_queue notification_queue_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_queue
    ADD CONSTRAINT notification_queue_pkey PRIMARY KEY (queue_id);


--
-- TOC entry 3948 (class 2606 OID 41385)
-- Name: notification_templates notification_templates_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_templates
    ADD CONSTRAINT notification_templates_pkey PRIMARY KEY (template_id);


--
-- TOC entry 3950 (class 2606 OID 41387)
-- Name: notification_templates notification_templates_template_code_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_templates
    ADD CONSTRAINT notification_templates_template_code_key UNIQUE (template_code);


--
-- TOC entry 3929 (class 2606 OID 33348)
-- Name: payment_allocations payment_allocations_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payment_allocations
    ADD CONSTRAINT payment_allocations_pkey PRIMARY KEY (allocation_id);


--
-- TOC entry 3923 (class 2606 OID 33322)
-- Name: payments payments_payment_no_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payments
    ADD CONSTRAINT payments_payment_no_key UNIQUE (payment_no);


--
-- TOC entry 3925 (class 2606 OID 33320)
-- Name: payments payments_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payments
    ADD CONSTRAINT payments_pkey PRIMARY KEY (payment_id);


--
-- TOC entry 3855 (class 2606 OID 32912)
-- Name: regions regions_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.regions
    ADD CONSTRAINT regions_pkey PRIMARY KEY (region_id);


--
-- TOC entry 3857 (class 2606 OID 32914)
-- Name: regions regions_region_code_key; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.regions
    ADD CONSTRAINT regions_region_code_key UNIQUE (region_code);


--
-- TOC entry 3853 (class 2606 OID 32882)
-- Name: tariff_blocks tariff_blocks_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.tariff_blocks
    ADD CONSTRAINT tariff_blocks_pkey PRIMARY KEY (block_id);


--
-- TOC entry 3851 (class 2606 OID 32859)
-- Name: tariffs tariffs_pkey; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.tariffs
    ADD CONSTRAINT tariffs_pkey PRIMARY KEY (tariff_id);


--
-- TOC entry 3911 (class 2606 OID 33244)
-- Name: bills unique_customer_period; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT unique_customer_period UNIQUE (customer_no, billing_period);


--
-- TOC entry 3931 (class 2606 OID 33350)
-- Name: payment_allocations unique_payment_bill; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payment_allocations
    ADD CONSTRAINT unique_payment_bill UNIQUE (payment_id, bill_id);


--
-- TOC entry 3887 (class 2606 OID 33112)
-- Name: consumption uq_meter_month; Type: CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.consumption
    ADD CONSTRAINT uq_meter_month UNIQUE (meter_no, reading_month);


--
-- TOC entry 3772 (class 1259 OID 16648)
-- Name: idx_customers_name; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_customers_name ON scpos.customers USING btree (full_name);


--
-- TOC entry 3775 (class 1259 OID 16649)
-- Name: idx_products_barcode; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_products_barcode ON scpos.products USING btree (barcode);


--
-- TOC entry 3776 (class 1259 OID 16650)
-- Name: idx_products_brand_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_products_brand_id ON scpos.products USING btree (brand_id);


--
-- TOC entry 3777 (class 1259 OID 16651)
-- Name: idx_products_category_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_products_category_id ON scpos.products USING btree (category_id);


--
-- TOC entry 3778 (class 1259 OID 16652)
-- Name: idx_products_sku; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_products_sku ON scpos.products USING btree (sku);


--
-- TOC entry 3779 (class 1259 OID 16653)
-- Name: idx_products_tax_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_products_tax_id ON scpos.products USING btree (tax_id);


--
-- TOC entry 3786 (class 1259 OID 16654)
-- Name: idx_purchase_items_product_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_purchase_items_product_id ON scpos.purchase_items USING btree (product_id);


--
-- TOC entry 3787 (class 1259 OID 16655)
-- Name: idx_purchase_items_purchase_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_purchase_items_purchase_id ON scpos.purchase_items USING btree (purchase_id);


--
-- TOC entry 3790 (class 1259 OID 16656)
-- Name: idx_purchases_purchased_at; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_purchases_purchased_at ON scpos.purchases USING btree (purchased_at);


--
-- TOC entry 3791 (class 1259 OID 16657)
-- Name: idx_purchases_supplier_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_purchases_supplier_id ON scpos.purchases USING btree (supplier_id);


--
-- TOC entry 3796 (class 1259 OID 16658)
-- Name: idx_return_items_return_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_return_items_return_id ON scpos.return_items USING btree (return_id);


--
-- TOC entry 3801 (class 1259 OID 16659)
-- Name: idx_sales_cashier_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_sales_cashier_id ON scpos.sales USING btree (cashier_id);


--
-- TOC entry 3802 (class 1259 OID 16660)
-- Name: idx_sales_customer_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_sales_customer_id ON scpos.sales USING btree (customer_id);


--
-- TOC entry 3803 (class 1259 OID 16661)
-- Name: idx_sales_invoice_number; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_sales_invoice_number ON scpos.sales USING btree (invoice_number);


--
-- TOC entry 3809 (class 1259 OID 16662)
-- Name: idx_sales_items_product_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_sales_items_product_id ON scpos.sales_items USING btree (product_id);


--
-- TOC entry 3810 (class 1259 OID 16663)
-- Name: idx_sales_items_sale_id; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_sales_items_sale_id ON scpos.sales_items USING btree (sale_id);


--
-- TOC entry 3804 (class 1259 OID 16664)
-- Name: idx_sales_time; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_sales_time ON scpos.sales USING btree (sale_time);


--
-- TOC entry 3813 (class 1259 OID 16665)
-- Name: idx_suppliers_name; Type: INDEX; Schema: scpos; Owner: postgres
--

CREATE INDEX idx_suppliers_name ON scpos.suppliers USING btree (supplier_name);


--
-- TOC entry 3914 (class 1259 OID 33304)
-- Name: idx_bill_details_bill; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bill_details_bill ON ws.bill_details USING btree (bill_id);


--
-- TOC entry 3915 (class 1259 OID 33305)
-- Name: idx_bill_details_type; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bill_details_type ON ws.bill_details USING btree (charge_type);


--
-- TOC entry 3896 (class 1259 OID 33215)
-- Name: idx_billing_cycles_month; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_billing_cycles_month ON ws.billing_cycles USING btree (cycle_month);


--
-- TOC entry 3897 (class 1259 OID 33216)
-- Name: idx_billing_cycles_status; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_billing_cycles_status ON ws.billing_cycles USING btree (status);


--
-- TOC entry 3902 (class 1259 OID 33284)
-- Name: idx_bills_bill_status; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bills_bill_status ON ws.bills USING btree (bill_status);


--
-- TOC entry 3903 (class 1259 OID 33286)
-- Name: idx_bills_consumption; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bills_consumption ON ws.bills USING btree (consumption_id);


--
-- TOC entry 3904 (class 1259 OID 33280)
-- Name: idx_bills_customer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bills_customer ON ws.bills USING btree (customer_no);


--
-- TOC entry 3905 (class 1259 OID 33285)
-- Name: idx_bills_due_date; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bills_due_date ON ws.bills USING btree (due_date);


--
-- TOC entry 3906 (class 1259 OID 33281)
-- Name: idx_bills_meter; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bills_meter ON ws.bills USING btree (meter_no);


--
-- TOC entry 3907 (class 1259 OID 33287)
-- Name: idx_bills_not_deleted; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bills_not_deleted ON ws.bills USING btree (is_deleted) WHERE (is_deleted = false);


--
-- TOC entry 3908 (class 1259 OID 33283)
-- Name: idx_bills_payment_status; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bills_payment_status ON ws.bills USING btree (payment_status);


--
-- TOC entry 3909 (class 1259 OID 33282)
-- Name: idx_bills_period; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_bills_period ON ws.bills USING btree (billing_period);


--
-- TOC entry 3890 (class 1259 OID 33190)
-- Name: idx_charge_configs_active; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_charge_configs_active ON ws.charge_configurations USING btree (is_active);


--
-- TOC entry 3891 (class 1259 OID 33189)
-- Name: idx_charge_configs_type; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_charge_configs_type ON ws.charge_configurations USING btree (charge_type);


--
-- TOC entry 3936 (class 1259 OID 33385)
-- Name: idx_customer_balances_arrears; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_customer_balances_arrears ON ws.customer_balances USING btree (current_balance) WHERE (current_balance > (0)::numeric);


--
-- TOC entry 3937 (class 1259 OID 33386)
-- Name: idx_customer_balances_credit; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_customer_balances_credit ON ws.customer_balances USING btree (credit_balance) WHERE (credit_balance > (0)::numeric);


--
-- TOC entry 3938 (class 1259 OID 33384)
-- Name: idx_customer_balances_customer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_customer_balances_customer ON ws.customer_balances USING btree (customer_no);


--
-- TOC entry 3969 (class 1259 OID 41473)
-- Name: idx_customer_preferences_customer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_customer_preferences_customer ON ws.customer_notification_preferences USING btree (customer_no);


--
-- TOC entry 3870 (class 1259 OID 32980)
-- Name: idx_customers_category_id; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_customers_category_id ON ws.customers USING btree (customer_category_id);


--
-- TOC entry 3871 (class 1259 OID 32979)
-- Name: idx_customers_community_id; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_customers_community_id ON ws.customers USING btree (community_id);


--
-- TOC entry 3872 (class 1259 OID 32981)
-- Name: idx_customers_phone; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_customers_phone ON ws.customers USING btree (phone);


--
-- TOC entry 3873 (class 1259 OID 32982)
-- Name: idx_customers_surname; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_customers_surname ON ws.customers USING btree (surname);


--
-- TOC entry 3983 (class 1259 OID 41540)
-- Name: idx_disconnection_history_action; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnection_history_action ON ws.disconnection_history USING btree (action);


--
-- TOC entry 3984 (class 1259 OID 41541)
-- Name: idx_disconnection_history_created; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnection_history_created ON ws.disconnection_history USING btree (created_at);


--
-- TOC entry 3985 (class 1259 OID 41539)
-- Name: idx_disconnection_history_disconnection; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnection_history_disconnection ON ws.disconnection_history USING btree (disconnection_id);


--
-- TOC entry 3941 (class 1259 OID 33413)
-- Name: idx_disconnection_notices_customer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnection_notices_customer ON ws.disconnection_notices USING btree (customer_no);


--
-- TOC entry 3942 (class 1259 OID 33415)
-- Name: idx_disconnection_notices_due; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnection_notices_due ON ws.disconnection_notices USING btree (due_date);


--
-- TOC entry 3943 (class 1259 OID 33414)
-- Name: idx_disconnection_notices_status; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnection_notices_status ON ws.disconnection_notices USING btree (status);


--
-- TOC entry 3974 (class 1259 OID 41520)
-- Name: idx_disconnections_approval; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnections_approval ON ws.disconnections USING btree (approval_status) WHERE ((approval_status)::text = 'pending'::text);


--
-- TOC entry 3975 (class 1259 OID 41517)
-- Name: idx_disconnections_customer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnections_customer ON ws.disconnections USING btree (customer_no);


--
-- TOC entry 3976 (class 1259 OID 41523)
-- Name: idx_disconnections_field_officer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnections_field_officer ON ws.disconnections USING btree (field_officer_user_id) WHERE (field_officer_user_id IS NOT NULL);


--
-- TOC entry 3977 (class 1259 OID 41518)
-- Name: idx_disconnections_meter; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnections_meter ON ws.disconnections USING btree (meter_no);


--
-- TOC entry 3978 (class 1259 OID 41522)
-- Name: idx_disconnections_method; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnections_method ON ws.disconnections USING btree (disconnection_method);


--
-- TOC entry 3979 (class 1259 OID 41521)
-- Name: idx_disconnections_scheduled_date; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnections_scheduled_date ON ws.disconnections USING btree (scheduled_disconnection_date);


--
-- TOC entry 3980 (class 1259 OID 41519)
-- Name: idx_disconnections_status; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_disconnections_status ON ws.disconnections USING btree (status);


--
-- TOC entry 3958 (class 1259 OID 41438)
-- Name: idx_notification_logs_channel; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_logs_channel ON ws.notification_logs USING btree (channel);


--
-- TOC entry 3959 (class 1259 OID 41436)
-- Name: idx_notification_logs_customer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_logs_customer ON ws.notification_logs USING btree (customer_no);


--
-- TOC entry 3960 (class 1259 OID 41439)
-- Name: idx_notification_logs_sent_at; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_logs_sent_at ON ws.notification_logs USING btree (sent_at);


--
-- TOC entry 3961 (class 1259 OID 41437)
-- Name: idx_notification_logs_status; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_logs_status ON ws.notification_logs USING btree (status);


--
-- TOC entry 3962 (class 1259 OID 41440)
-- Name: idx_notification_logs_type; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_logs_type ON ws.notification_logs USING btree (notification_type, event_type);


--
-- TOC entry 3951 (class 1259 OID 41415)
-- Name: idx_notification_queue_customer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_queue_customer ON ws.notification_queue USING btree (customer_no);


--
-- TOC entry 3952 (class 1259 OID 41416)
-- Name: idx_notification_queue_priority; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_queue_priority ON ws.notification_queue USING btree (priority);


--
-- TOC entry 3953 (class 1259 OID 41414)
-- Name: idx_notification_queue_scheduled; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_queue_scheduled ON ws.notification_queue USING btree (scheduled_at) WHERE ((status)::text = 'pending'::text);


--
-- TOC entry 3954 (class 1259 OID 41413)
-- Name: idx_notification_queue_status; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_queue_status ON ws.notification_queue USING btree (status);


--
-- TOC entry 3955 (class 1259 OID 41417)
-- Name: idx_notification_queue_type; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_queue_type ON ws.notification_queue USING btree (notification_type, event_type);


--
-- TOC entry 3944 (class 1259 OID 41390)
-- Name: idx_notification_templates_active; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_templates_active ON ws.notification_templates USING btree (is_active);


--
-- TOC entry 3945 (class 1259 OID 41389)
-- Name: idx_notification_templates_category; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_templates_category ON ws.notification_templates USING btree (template_category);


--
-- TOC entry 3946 (class 1259 OID 41388)
-- Name: idx_notification_templates_code; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_notification_templates_code ON ws.notification_templates USING btree (template_code);


--
-- TOC entry 3926 (class 1259 OID 33362)
-- Name: idx_payment_allocations_bill; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_payment_allocations_bill ON ws.payment_allocations USING btree (bill_id);


--
-- TOC entry 3927 (class 1259 OID 33361)
-- Name: idx_payment_allocations_payment; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_payment_allocations_payment ON ws.payment_allocations USING btree (payment_id);


--
-- TOC entry 3916 (class 1259 OID 33333)
-- Name: idx_payments_customer; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_payments_customer ON ws.payments USING btree (customer_no);


--
-- TOC entry 3917 (class 1259 OID 33334)
-- Name: idx_payments_date; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_payments_date ON ws.payments USING btree (payment_date);


--
-- TOC entry 3918 (class 1259 OID 33335)
-- Name: idx_payments_method; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_payments_method ON ws.payments USING btree (payment_method);


--
-- TOC entry 3919 (class 1259 OID 33338)
-- Name: idx_payments_momo_phone; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_payments_momo_phone ON ws.payments USING btree (momo_phone_number);


--
-- TOC entry 3920 (class 1259 OID 33337)
-- Name: idx_payments_momo_txn; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_payments_momo_txn ON ws.payments USING btree (momo_transaction_id);


--
-- TOC entry 3921 (class 1259 OID 33336)
-- Name: idx_payments_status; Type: INDEX; Schema: ws; Owner: postgres
--

CREATE INDEX idx_payments_status ON ws.payments USING btree (payment_status);


--
-- TOC entry 4051 (class 2620 OID 16666)
-- Name: return_items trg_return_items_stock; Type: TRIGGER; Schema: scpos; Owner: postgres
--

CREATE TRIGGER trg_return_items_stock AFTER INSERT OR DELETE OR UPDATE ON scpos.return_items FOR EACH ROW EXECUTE FUNCTION scpos.tgr_return_items_stock();


--
-- TOC entry 4050 (class 2620 OID 16667)
-- Name: return_items trg_return_items_totals; Type: TRIGGER; Schema: scpos; Owner: postgres
--

CREATE TRIGGER trg_return_items_totals AFTER INSERT OR DELETE OR UPDATE ON scpos.return_items FOR EACH ROW EXECUTE FUNCTION scpos.tgr_returns_totals();


--
-- TOC entry 4052 (class 2620 OID 16668)
-- Name: sales trg_sales_set_invoice; Type: TRIGGER; Schema: scpos; Owner: postgres
--

CREATE TRIGGER trg_sales_set_invoice BEFORE INSERT ON scpos.sales FOR EACH ROW EXECUTE FUNCTION scpos.set_invoice_number();


--
-- TOC entry 4067 (class 2620 OID 41562)
-- Name: disconnections set_disconnection_no; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER set_disconnection_no BEFORE INSERT ON ws.disconnections FOR EACH ROW EXECUTE FUNCTION public.generate_disconnection_no();


--
-- TOC entry 4061 (class 2620 OID 33288)
-- Name: bills trg_bills_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_bills_updated BEFORE UPDATE ON ws.bills FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4060 (class 2620 OID 33191)
-- Name: charge_configurations trg_charge_configurations_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_charge_configurations_updated BEFORE UPDATE ON ws.charge_configurations FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4059 (class 2620 OID 33175)
-- Name: consumption trg_consumption_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_consumption_updated BEFORE UPDATE ON ws.consumption FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4063 (class 2620 OID 33387)
-- Name: customer_balances trg_customer_balances_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_customer_balances_updated BEFORE UPDATE ON ws.customer_balances FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4053 (class 2620 OID 33173)
-- Name: customer_categories trg_customer_categories_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_customer_categories_updated BEFORE UPDATE ON ws.customer_categories FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4056 (class 2620 OID 33171)
-- Name: customers trg_customers_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_customers_updated BEFORE UPDATE ON ws.customers FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4064 (class 2620 OID 33416)
-- Name: disconnection_notices trg_disconnection_notices_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_disconnection_notices_updated BEFORE UPDATE ON ws.disconnection_notices FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4058 (class 2620 OID 33174)
-- Name: meter_assignments trg_meter_assignments_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_meter_assignments_updated BEFORE UPDATE ON ws.meter_assignments FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4057 (class 2620 OID 33172)
-- Name: meters trg_meters_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_meters_updated BEFORE UPDATE ON ws.meters FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4062 (class 2620 OID 33339)
-- Name: payments trg_payments_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_payments_updated BEFORE UPDATE ON ws.payments FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4055 (class 2620 OID 33177)
-- Name: tariff_blocks trg_tariff_blocks_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_tariff_blocks_updated BEFORE UPDATE ON ws.tariff_blocks FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4054 (class 2620 OID 33176)
-- Name: tariffs trg_tariffs_updated; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER trg_tariffs_updated BEFORE UPDATE ON ws.tariffs FOR EACH ROW EXECUTE FUNCTION ws.update_timestamp();


--
-- TOC entry 4066 (class 2620 OID 41558)
-- Name: customer_notification_preferences update_customer_preferences_updated_at; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER update_customer_preferences_updated_at BEFORE UPDATE ON ws.customer_notification_preferences FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- TOC entry 4068 (class 2620 OID 41559)
-- Name: disconnections update_disconnections_updated_at; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER update_disconnections_updated_at BEFORE UPDATE ON ws.disconnections FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- TOC entry 4069 (class 2620 OID 41560)
-- Name: notification_config update_notification_config_updated_at; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER update_notification_config_updated_at BEFORE UPDATE ON ws.notification_config FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- TOC entry 4065 (class 2620 OID 41557)
-- Name: notification_templates update_notification_templates_updated_at; Type: TRIGGER; Schema: ws; Owner: postgres
--

CREATE TRIGGER update_notification_templates_updated_at BEFORE UPDATE ON ws.notification_templates FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- TOC entry 3990 (class 2606 OID 16669)
-- Name: audit_log audit_log_changed_by_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.audit_log
    ADD CONSTRAINT audit_log_changed_by_fkey FOREIGN KEY (changed_by) REFERENCES scpos.auth_users(au_id);


--
-- TOC entry 3993 (class 2606 OID 16684)
-- Name: categories categories_parent_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.categories
    ADD CONSTRAINT categories_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES scpos.categories(category_id) ON DELETE SET NULL;


--
-- TOC entry 3994 (class 2606 OID 16689)
-- Name: products products_brand_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.products
    ADD CONSTRAINT products_brand_id_fkey FOREIGN KEY (brand_id) REFERENCES scpos.brands(brand_id) ON DELETE SET NULL;


--
-- TOC entry 3995 (class 2606 OID 16694)
-- Name: products products_category_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.products
    ADD CONSTRAINT products_category_id_fkey FOREIGN KEY (category_id) REFERENCES scpos.categories(category_id) ON DELETE RESTRICT;


--
-- TOC entry 3996 (class 2606 OID 16699)
-- Name: products products_tax_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.products
    ADD CONSTRAINT products_tax_id_fkey FOREIGN KEY (tax_id) REFERENCES scpos.taxes(tax_id) ON DELETE SET NULL;


--
-- TOC entry 3997 (class 2606 OID 16704)
-- Name: purchase_items purchase_items_product_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.purchase_items
    ADD CONSTRAINT purchase_items_product_id_fkey FOREIGN KEY (product_id) REFERENCES scpos.products(product_id) ON DELETE RESTRICT;


--
-- TOC entry 3998 (class 2606 OID 16709)
-- Name: purchase_items purchase_items_purchase_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.purchase_items
    ADD CONSTRAINT purchase_items_purchase_id_fkey FOREIGN KEY (purchase_id) REFERENCES scpos.purchases(purchase_id) ON DELETE CASCADE;


--
-- TOC entry 3999 (class 2606 OID 16714)
-- Name: purchases purchases_supplier_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.purchases
    ADD CONSTRAINT purchases_supplier_id_fkey FOREIGN KEY (supplier_id) REFERENCES scpos.suppliers(supplier_id) ON DELETE RESTRICT;


--
-- TOC entry 4000 (class 2606 OID 16719)
-- Name: refunds refunds_return_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.refunds
    ADD CONSTRAINT refunds_return_id_fkey FOREIGN KEY (return_id) REFERENCES scpos.returns(return_id) ON DELETE CASCADE;


--
-- TOC entry 4001 (class 2606 OID 16724)
-- Name: return_items return_items_product_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.return_items
    ADD CONSTRAINT return_items_product_id_fkey FOREIGN KEY (product_id) REFERENCES scpos.products(product_id) ON DELETE RESTRICT;


--
-- TOC entry 4002 (class 2606 OID 16729)
-- Name: return_items return_items_return_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.return_items
    ADD CONSTRAINT return_items_return_id_fkey FOREIGN KEY (return_id) REFERENCES scpos.returns(return_id) ON DELETE CASCADE;


--
-- TOC entry 4003 (class 2606 OID 16734)
-- Name: returns returns_cashier_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.returns
    ADD CONSTRAINT returns_cashier_id_fkey FOREIGN KEY (cashier_id) REFERENCES scpos.auth_users(au_id) ON DELETE RESTRICT;


--
-- TOC entry 4004 (class 2606 OID 16739)
-- Name: returns returns_sale_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.returns
    ADD CONSTRAINT returns_sale_id_fkey FOREIGN KEY (sale_id) REFERENCES scpos.sales(sale_id) ON DELETE RESTRICT;


--
-- TOC entry 4005 (class 2606 OID 16744)
-- Name: sales sales_cashier_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.sales
    ADD CONSTRAINT sales_cashier_id_fkey FOREIGN KEY (cashier_id) REFERENCES scpos.auth_users(au_id) ON DELETE RESTRICT;


--
-- TOC entry 4006 (class 2606 OID 16749)
-- Name: sales sales_customer_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.sales
    ADD CONSTRAINT sales_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES scpos.customers(customer_id) ON DELETE SET NULL;


--
-- TOC entry 4007 (class 2606 OID 16754)
-- Name: sales_items sales_items_product_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.sales_items
    ADD CONSTRAINT sales_items_product_id_fkey FOREIGN KEY (product_id) REFERENCES scpos.products(product_id) ON DELETE RESTRICT;


--
-- TOC entry 4008 (class 2606 OID 16759)
-- Name: sales_items sales_items_sale_id_fkey; Type: FK CONSTRAINT; Schema: scpos; Owner: postgres
--

ALTER TABLE ONLY scpos.sales_items
    ADD CONSTRAINT sales_items_sale_id_fkey FOREIGN KEY (sale_id) REFERENCES scpos.sales(sale_id) ON DELETE CASCADE;


--
-- TOC entry 4011 (class 2606 OID 32815)
-- Name: auth_role_permissions auth_role_permissions_permission_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_role_permissions
    ADD CONSTRAINT auth_role_permissions_permission_id_fkey FOREIGN KEY (permission_id) REFERENCES ws.auth_permissions(p_id) ON DELETE CASCADE;


--
-- TOC entry 4012 (class 2606 OID 32810)
-- Name: auth_role_permissions auth_role_permissions_role_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.auth_role_permissions
    ADD CONSTRAINT auth_role_permissions_role_id_fkey FOREIGN KEY (role_id) REFERENCES ws.auth_roles(r_id) ON DELETE CASCADE;


--
-- TOC entry 4035 (class 2606 OID 33299)
-- Name: bill_details bill_details_bill_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bill_details
    ADD CONSTRAINT bill_details_bill_id_fkey FOREIGN KEY (bill_id) REFERENCES ws.bills(bill_id) ON DELETE CASCADE;


--
-- TOC entry 4027 (class 2606 OID 33210)
-- Name: billing_cycles billing_cycles_generated_by_user_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.billing_cycles
    ADD CONSTRAINT billing_cycles_generated_by_user_id_fkey FOREIGN KEY (generated_by_user_id) REFERENCES ws.auth_users(au_id);


--
-- TOC entry 4028 (class 2606 OID 33255)
-- Name: bills bills_consumption_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_consumption_id_fkey FOREIGN KEY (consumption_id) REFERENCES ws.consumption(consumption_id) ON DELETE RESTRICT;


--
-- TOC entry 4029 (class 2606 OID 33245)
-- Name: bills bills_customer_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_customer_no_fkey FOREIGN KEY (customer_no) REFERENCES ws.customers(account_no) ON DELETE RESTRICT;


--
-- TOC entry 4030 (class 2606 OID 33265)
-- Name: bills bills_cycle_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_cycle_id_fkey FOREIGN KEY (cycle_id) REFERENCES ws.billing_cycles(cycle_id);


--
-- TOC entry 4031 (class 2606 OID 33270)
-- Name: bills bills_deleted_by_user_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_deleted_by_user_id_fkey FOREIGN KEY (deleted_by_user_id) REFERENCES ws.auth_users(au_id);


--
-- TOC entry 4032 (class 2606 OID 33275)
-- Name: bills bills_generated_by_user_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_generated_by_user_id_fkey FOREIGN KEY (generated_by_user_id) REFERENCES ws.auth_users(au_id);


--
-- TOC entry 4033 (class 2606 OID 33250)
-- Name: bills bills_meter_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_meter_no_fkey FOREIGN KEY (meter_no) REFERENCES ws.meters(meter_no) ON DELETE RESTRICT;


--
-- TOC entry 4034 (class 2606 OID 33260)
-- Name: bills bills_tariff_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.bills
    ADD CONSTRAINT bills_tariff_id_fkey FOREIGN KEY (tariff_id) REFERENCES ws.tariffs(tariff_id) ON DELETE RESTRICT;


--
-- TOC entry 4016 (class 2606 OID 32947)
-- Name: communities communities_district_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.communities
    ADD CONSTRAINT communities_district_id_fkey FOREIGN KEY (district_id) REFERENCES ws.districts(district_id) ON DELETE CASCADE;


--
-- TOC entry 4023 (class 2606 OID 33113)
-- Name: consumption consumption_customer_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.consumption
    ADD CONSTRAINT consumption_customer_no_fkey FOREIGN KEY (customer_no) REFERENCES ws.customers(account_no) ON DELETE CASCADE;


--
-- TOC entry 4024 (class 2606 OID 33118)
-- Name: consumption consumption_meter_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.consumption
    ADD CONSTRAINT consumption_meter_no_fkey FOREIGN KEY (meter_no) REFERENCES ws.meters(meter_no) ON DELETE CASCADE;


--
-- TOC entry 4025 (class 2606 OID 33123)
-- Name: consumption consumption_reader_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.consumption
    ADD CONSTRAINT consumption_reader_id_fkey FOREIGN KEY (reader_id) REFERENCES ws.auth_users(au_id);


--
-- TOC entry 4026 (class 2606 OID 33141)
-- Name: consumption consumption_verified_by_user_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.consumption
    ADD CONSTRAINT consumption_verified_by_user_id_fkey FOREIGN KEY (verified_by_user_id) REFERENCES ws.auth_users(au_id);


--
-- TOC entry 4040 (class 2606 OID 33379)
-- Name: customer_balances customer_balances_customer_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_balances
    ADD CONSTRAINT customer_balances_customer_no_fkey FOREIGN KEY (customer_no) REFERENCES ws.customers(account_no) ON DELETE CASCADE;


--
-- TOC entry 4045 (class 2606 OID 41468)
-- Name: customer_notification_preferences customer_notification_preferences_customer_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customer_notification_preferences
    ADD CONSTRAINT customer_notification_preferences_customer_no_fkey FOREIGN KEY (customer_no) REFERENCES ws.customers(account_no) ON DELETE CASCADE;


--
-- TOC entry 4017 (class 2606 OID 32972)
-- Name: customers customers_community_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customers
    ADD CONSTRAINT customers_community_id_fkey FOREIGN KEY (community_id) REFERENCES ws.communities(community_id) ON DELETE RESTRICT;


--
-- TOC entry 4018 (class 2606 OID 32967)
-- Name: customers customers_customer_category_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.customers
    ADD CONSTRAINT customers_customer_category_id_fkey FOREIGN KEY (customer_category_id) REFERENCES ws.customer_categories(category_id) ON DELETE RESTRICT;


--
-- TOC entry 4049 (class 2606 OID 41534)
-- Name: disconnection_history disconnection_history_disconnection_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnection_history
    ADD CONSTRAINT disconnection_history_disconnection_id_fkey FOREIGN KEY (disconnection_id) REFERENCES ws.disconnections(disconnection_id) ON DELETE CASCADE;


--
-- TOC entry 4041 (class 2606 OID 33403)
-- Name: disconnection_notices disconnection_notices_customer_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnection_notices
    ADD CONSTRAINT disconnection_notices_customer_no_fkey FOREIGN KEY (customer_no) REFERENCES ws.customers(account_no) ON DELETE CASCADE;


--
-- TOC entry 4042 (class 2606 OID 33408)
-- Name: disconnection_notices disconnection_notices_disconnected_by_user_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnection_notices
    ADD CONSTRAINT disconnection_notices_disconnected_by_user_id_fkey FOREIGN KEY (disconnected_by_user_id) REFERENCES ws.auth_users(au_id);


--
-- TOC entry 4046 (class 2606 OID 41502)
-- Name: disconnections disconnections_customer_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnections
    ADD CONSTRAINT disconnections_customer_no_fkey FOREIGN KEY (customer_no) REFERENCES ws.customers(account_no) ON DELETE RESTRICT;


--
-- TOC entry 4047 (class 2606 OID 41507)
-- Name: disconnections disconnections_meter_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnections
    ADD CONSTRAINT disconnections_meter_no_fkey FOREIGN KEY (meter_no) REFERENCES ws.meters(meter_no) ON DELETE RESTRICT;


--
-- TOC entry 4048 (class 2606 OID 41512)
-- Name: disconnections disconnections_reconnection_payment_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.disconnections
    ADD CONSTRAINT disconnections_reconnection_payment_id_fkey FOREIGN KEY (reconnection_payment_id) REFERENCES ws.payments(payment_id) ON DELETE SET NULL;


--
-- TOC entry 4015 (class 2606 OID 32933)
-- Name: districts districts_region_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.districts
    ADD CONSTRAINT districts_region_id_fkey FOREIGN KEY (region_id) REFERENCES ws.regions(region_id) ON DELETE CASCADE;


--
-- TOC entry 4019 (class 2606 OID 33018)
-- Name: meter_assignments meter_assignments_customer_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meter_assignments
    ADD CONSTRAINT meter_assignments_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES ws.customers(customer_id) ON DELETE CASCADE;


--
-- TOC entry 4020 (class 2606 OID 33013)
-- Name: meter_assignments meter_assignments_meter_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meter_assignments
    ADD CONSTRAINT meter_assignments_meter_id_fkey FOREIGN KEY (meter_id) REFERENCES ws.meters(meter_id) ON DELETE CASCADE;


--
-- TOC entry 4021 (class 2606 OID 33136)
-- Name: meter_assignments meter_assignments_removed_by_user_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meter_assignments
    ADD CONSTRAINT meter_assignments_removed_by_user_id_fkey FOREIGN KEY (removed_by_user_id) REFERENCES ws.auth_users(au_id);


--
-- TOC entry 4022 (class 2606 OID 33046)
-- Name: meter_reports meter_reports_meter_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.meter_reports
    ADD CONSTRAINT meter_reports_meter_no_fkey FOREIGN KEY (meter_no) REFERENCES ws.meters(meter_no) ON DELETE CASCADE;


--
-- TOC entry 4044 (class 2606 OID 41431)
-- Name: notification_logs notification_logs_queue_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_logs
    ADD CONSTRAINT notification_logs_queue_id_fkey FOREIGN KEY (queue_id) REFERENCES ws.notification_queue(queue_id) ON DELETE SET NULL;


--
-- TOC entry 4043 (class 2606 OID 41408)
-- Name: notification_queue notification_queue_customer_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.notification_queue
    ADD CONSTRAINT notification_queue_customer_no_fkey FOREIGN KEY (customer_no) REFERENCES ws.customers(account_no) ON DELETE CASCADE;


--
-- TOC entry 4038 (class 2606 OID 33356)
-- Name: payment_allocations payment_allocations_bill_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payment_allocations
    ADD CONSTRAINT payment_allocations_bill_id_fkey FOREIGN KEY (bill_id) REFERENCES ws.bills(bill_id) ON DELETE CASCADE;


--
-- TOC entry 4039 (class 2606 OID 33351)
-- Name: payment_allocations payment_allocations_payment_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payment_allocations
    ADD CONSTRAINT payment_allocations_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES ws.payments(payment_id) ON DELETE CASCADE;


--
-- TOC entry 4036 (class 2606 OID 33323)
-- Name: payments payments_customer_no_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payments
    ADD CONSTRAINT payments_customer_no_fkey FOREIGN KEY (customer_no) REFERENCES ws.customers(account_no) ON DELETE RESTRICT;


--
-- TOC entry 4037 (class 2606 OID 33328)
-- Name: payments payments_received_by_user_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.payments
    ADD CONSTRAINT payments_received_by_user_id_fkey FOREIGN KEY (received_by_user_id) REFERENCES ws.auth_users(au_id);


--
-- TOC entry 4014 (class 2606 OID 32883)
-- Name: tariff_blocks tariff_blocks_tariff_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.tariff_blocks
    ADD CONSTRAINT tariff_blocks_tariff_id_fkey FOREIGN KEY (tariff_id) REFERENCES ws.tariffs(tariff_id) ON DELETE CASCADE;


--
-- TOC entry 4013 (class 2606 OID 33146)
-- Name: tariffs tariffs_category_id_fkey; Type: FK CONSTRAINT; Schema: ws; Owner: postgres
--

ALTER TABLE ONLY ws.tariffs
    ADD CONSTRAINT tariffs_category_id_fkey FOREIGN KEY (category_id) REFERENCES ws.customer_categories(category_id) ON DELETE CASCADE;


--
-- TOC entry 4316 (class 0 OID 0)
-- Dependencies: 318
-- Name: FUNCTION gen_invoice_no(p_at timestamp with time zone); Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON FUNCTION scpos.gen_invoice_no(p_at timestamp with time zone) TO gstwms_admin;


--
-- TOC entry 4317 (class 0 OID 0)
-- Dependencies: 319
-- Name: FUNCTION set_invoice_number(); Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON FUNCTION scpos.set_invoice_number() TO gstwms_admin;


--
-- TOC entry 4318 (class 0 OID 0)
-- Dependencies: 328
-- Name: FUNCTION tgr_return_items_stock(); Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON FUNCTION scpos.tgr_return_items_stock() TO gstwms_admin;


--
-- TOC entry 4319 (class 0 OID 0)
-- Dependencies: 329
-- Name: FUNCTION tgr_returns_totals(); Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON FUNCTION scpos.tgr_returns_totals() TO gstwms_admin;


--
-- TOC entry 4322 (class 0 OID 0)
-- Dependencies: 211
-- Name: TABLE audit_log; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.audit_log TO gstwms_admin;


--
-- TOC entry 4323 (class 0 OID 0)
-- Dependencies: 212
-- Name: SEQUENCE audit_log_audit_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.audit_log_audit_id_seq TO gstwms_admin;


--
-- TOC entry 4324 (class 0 OID 0)
-- Dependencies: 213
-- Name: TABLE auth_audit_logs; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.auth_audit_logs TO gstwms_admin;


--
-- TOC entry 4326 (class 0 OID 0)
-- Dependencies: 214
-- Name: SEQUENCE auth_audit_logs_log_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.auth_audit_logs_log_id_seq TO gstwms_admin;


--
-- TOC entry 4327 (class 0 OID 0)
-- Dependencies: 215
-- Name: TABLE auth_roles; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.auth_roles TO gstwms_admin;


--
-- TOC entry 4328 (class 0 OID 0)
-- Dependencies: 216
-- Name: SEQUENCE auth_roles_r_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.auth_roles_r_id_seq TO gstwms_admin;


--
-- TOC entry 4329 (class 0 OID 0)
-- Dependencies: 217
-- Name: TABLE auth_users; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.auth_users TO gstwms_admin;


--
-- TOC entry 4330 (class 0 OID 0)
-- Dependencies: 218
-- Name: SEQUENCE auth_users_au_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.auth_users_au_id_seq TO gstwms_admin;


--
-- TOC entry 4331 (class 0 OID 0)
-- Dependencies: 219
-- Name: TABLE brands; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.brands TO gstwms_admin;


--
-- TOC entry 4332 (class 0 OID 0)
-- Dependencies: 220
-- Name: SEQUENCE brands_brand_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.brands_brand_id_seq TO gstwms_admin;


--
-- TOC entry 4333 (class 0 OID 0)
-- Dependencies: 221
-- Name: TABLE categories; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.categories TO gstwms_admin;


--
-- TOC entry 4334 (class 0 OID 0)
-- Dependencies: 222
-- Name: SEQUENCE categories_category_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.categories_category_id_seq TO gstwms_admin;


--
-- TOC entry 4335 (class 0 OID 0)
-- Dependencies: 223
-- Name: TABLE customers; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.customers TO gstwms_admin;


--
-- TOC entry 4336 (class 0 OID 0)
-- Dependencies: 224
-- Name: SEQUENCE customers_customer_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.customers_customer_id_seq TO gstwms_admin;


--
-- TOC entry 4337 (class 0 OID 0)
-- Dependencies: 225
-- Name: TABLE invoice_counters; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.invoice_counters TO gstwms_admin;


--
-- TOC entry 4338 (class 0 OID 0)
-- Dependencies: 226
-- Name: TABLE products; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.products TO gstwms_admin;


--
-- TOC entry 4339 (class 0 OID 0)
-- Dependencies: 227
-- Name: SEQUENCE products_product_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.products_product_id_seq TO gstwms_admin;


--
-- TOC entry 4340 (class 0 OID 0)
-- Dependencies: 228
-- Name: TABLE purchase_items; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.purchase_items TO gstwms_admin;


--
-- TOC entry 4341 (class 0 OID 0)
-- Dependencies: 229
-- Name: SEQUENCE purchase_items_purchase_item_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.purchase_items_purchase_item_id_seq TO gstwms_admin;


--
-- TOC entry 4342 (class 0 OID 0)
-- Dependencies: 230
-- Name: TABLE purchases; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.purchases TO gstwms_admin;


--
-- TOC entry 4343 (class 0 OID 0)
-- Dependencies: 231
-- Name: SEQUENCE purchases_purchase_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.purchases_purchase_id_seq TO gstwms_admin;


--
-- TOC entry 4344 (class 0 OID 0)
-- Dependencies: 232
-- Name: TABLE refunds; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.refunds TO gstwms_admin;


--
-- TOC entry 4345 (class 0 OID 0)
-- Dependencies: 233
-- Name: SEQUENCE refunds_refund_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.refunds_refund_id_seq TO gstwms_admin;


--
-- TOC entry 4346 (class 0 OID 0)
-- Dependencies: 234
-- Name: TABLE return_items; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.return_items TO gstwms_admin;


--
-- TOC entry 4347 (class 0 OID 0)
-- Dependencies: 235
-- Name: SEQUENCE return_items_return_item_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.return_items_return_item_id_seq TO gstwms_admin;


--
-- TOC entry 4348 (class 0 OID 0)
-- Dependencies: 236
-- Name: TABLE returns; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.returns TO gstwms_admin;


--
-- TOC entry 4349 (class 0 OID 0)
-- Dependencies: 237
-- Name: SEQUENCE returns_return_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.returns_return_id_seq TO gstwms_admin;


--
-- TOC entry 4350 (class 0 OID 0)
-- Dependencies: 238
-- Name: TABLE sales; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.sales TO gstwms_admin;


--
-- TOC entry 4351 (class 0 OID 0)
-- Dependencies: 239
-- Name: TABLE sales_items; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.sales_items TO gstwms_admin;


--
-- TOC entry 4352 (class 0 OID 0)
-- Dependencies: 240
-- Name: SEQUENCE sales_items_sale_item_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.sales_items_sale_item_id_seq TO gstwms_admin;


--
-- TOC entry 4353 (class 0 OID 0)
-- Dependencies: 241
-- Name: SEQUENCE sales_sale_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.sales_sale_id_seq TO gstwms_admin;


--
-- TOC entry 4354 (class 0 OID 0)
-- Dependencies: 242
-- Name: TABLE suppliers; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.suppliers TO gstwms_admin;


--
-- TOC entry 4355 (class 0 OID 0)
-- Dependencies: 243
-- Name: SEQUENCE suppliers_supplier_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.suppliers_supplier_id_seq TO gstwms_admin;


--
-- TOC entry 4356 (class 0 OID 0)
-- Dependencies: 244
-- Name: TABLE taxes; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.taxes TO gstwms_admin;


--
-- TOC entry 4357 (class 0 OID 0)
-- Dependencies: 245
-- Name: SEQUENCE taxes_tax_id_seq; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON SEQUENCE scpos.taxes_tax_id_seq TO gstwms_admin;


--
-- TOC entry 4358 (class 0 OID 0)
-- Dependencies: 246
-- Name: TABLE v_current_stock; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.v_current_stock TO gstwms_admin;


--
-- TOC entry 4359 (class 0 OID 0)
-- Dependencies: 247
-- Name: TABLE v_top_sellers_30d; Type: ACL; Schema: scpos; Owner: postgres
--

GRANT ALL ON TABLE scpos.v_top_sellers_30d TO gstwms_admin;


--
-- TOC entry 2297 (class 826 OID 16767)
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: scpos; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA scpos GRANT ALL ON SEQUENCES  TO gstwms_admin;


--
-- TOC entry 2298 (class 826 OID 16768)
-- Name: DEFAULT PRIVILEGES FOR FUNCTIONS; Type: DEFAULT ACL; Schema: scpos; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA scpos GRANT ALL ON FUNCTIONS  TO gstwms_admin;


--
-- TOC entry 2296 (class 826 OID 16766)
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: scpos; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA scpos GRANT ALL ON TABLES  TO gstwms_admin;