schema.sql

-- Core schema for SE HUB demo

CREATE TABLE accounts (
  account_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  agency_code VARCHAR2(10) NOT NULL,
  account_number VARCHAR2(30) NOT NULL,
  customer_name VARCHAR2(200),
  created_at DATE DEFAULT SYSDATE
);

CREATE TABLE toll_transactions (
  txn_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  txn_ts DATE NOT NULL,
  plaza_id VARCHAR2(20),
  lane_id VARCHAR2(20),
  tag_id VARCHAR2(40),
  class_code NUMBER,
  amount NUMBER(10,2) NOT NULL,
  account_number VARCHAR2(30),
  agency_code VARCHAR2(10),
  status VARCHAR2(20) DEFAULT 'NEW',
  error_msg VARCHAR2(4000)
);

CREATE TABLE settlements (
  settle_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  settle_date DATE NOT NULL,
  agency_code VARCHAR2(10) NOT NULL,
  total_amount NUMBER(14,2) NOT NULL,
  total_txn NUMBER NOT NULL,
  created_at DATE DEFAULT SYSDATE
);

CREATE TABLE error_log (
  err_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ref_id NUMBER,
  ref_type VARCHAR2(30),
  err_ts DATE DEFAULT SYSDATE,
  err_code VARCHAR2(50),
  err_msg VARCHAR2(4000)
);

CREATE INDEX idx_txn_ts ON toll_transactions (txn_ts);
CREATE INDEX idx_txn_status ON toll_transactions (status);