CREATE TABLE IF NOT EXISTS schema_migrations (
  id TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS customers (
  id TEXT PRIMARY KEY,
  customer_number TEXT,
  name TEXT NOT NULL,
  phone TEXT,
  address TEXT,
  category TEXT,
  credit_limit NUMERIC(18,4) DEFAULT 0,
  balance NUMERIC(18,4) DEFAULT 0,
  allowed_discount NUMERIC(18,4) DEFAULT 0,
  sales_rep_id TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS suppliers (
  id TEXT PRIMARY KEY,
  supplier_number TEXT,
  name TEXT NOT NULL,
  phone TEXT,
  address TEXT,
  payment_terms INTEGER,
  tax_number TEXT,
  account_id TEXT,
  account_code TEXT,
  balance NUMERIC(18,4) DEFAULT 0,
  inactive BOOLEAN DEFAULT FALSE,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS materials (
  id TEXT PRIMARY KEY,
  item_number TEXT,
  name TEXT NOT NULL,
  barcode TEXT,
  sale_price NUMERIC(18,4) DEFAULT 0,
  purchase_price NUMERIC(18,4) DEFAULT 0,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoices (
  id TEXT PRIMARY KEY,
  invoice_number TEXT NOT NULL,
  customer_id TEXT,
  customer_name TEXT,
  invoice_date DATE,
  currency_code TEXT,
  grand_total NUMERIC(18,4) DEFAULT 0,
  amount_due NUMERIC(18,4) DEFAULT 0,
  status TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS purchase_orders (
  id TEXT PRIMARY KEY,
  order_number TEXT NOT NULL,
  supplier_id TEXT,
  supplier_name TEXT,
  order_date DATE,
  currency_code TEXT,
  base_currency_code TEXT,
  grand_total NUMERIC(18,4) DEFAULT 0,
  status TEXT,
  posting_status TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS data_json_store (
  filename TEXT PRIMARY KEY,
  payload JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_invoices_invoice_number ON invoices (invoice_number);
CREATE INDEX IF NOT EXISTS idx_invoices_invoice_date ON invoices (invoice_date DESC);
CREATE INDEX IF NOT EXISTS idx_invoices_customer_name ON invoices (customer_name);

CREATE INDEX IF NOT EXISTS idx_purchase_orders_order_number ON purchase_orders (order_number);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_order_date ON purchase_orders (order_date DESC);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_supplier_name ON purchase_orders (supplier_name);

CREATE INDEX IF NOT EXISTS idx_customers_name ON customers (name);
CREATE INDEX IF NOT EXISTS idx_suppliers_name ON suppliers (name);
CREATE INDEX IF NOT EXISTS idx_materials_name ON materials (name);