Schema for goamines

CREATE TABLE permits (
  id INTEGER PRIMARY KEY,
  permit_no TEXT,
  software_era TEXT,
  ore_stream TEXT,
  category TEXT,                 -- transit / export / sale / import
  is_superseded INTEGER DEFAULT 0,
  permit_type TEXT, issue_date TEXT, validity_date TEXT, application_date TEXT,
  status TEXT, financial_year TEXT,
  org_code TEXT, org_name TEXT,
  mineral_type TEXT, ore_type TEXT, ore_category TEXT,
  grade_slab TEXT, exact_grade TEXT,
  permit_qty REAL, used_qty REAL, balance_qty REAL,
  source_location_raw TEXT, dest_location_raw TEXT, transport_mode TEXT,
  state TEXT, country TEXT, district TEXT, tehsil TEXT,
  vessel_name TEXT, buyer_name TEXT, trader_name TEXT, trader_code TEXT,
  export_sale_permit_no TEXT, export_permit_date TEXT, challan_no TEXT,
  imp_exp_type TEXT, imp_exp_address TEXT, imp_exp_party TEXT,
  source_file TEXT, source_sheet TEXT
);
CREATE TABLE trips (
  id INTEGER PRIMARY KEY,
  permit_no TEXT,
  ore_stream TEXT,
  vehicle_or_barge TEXT,
  source_location_raw TEXT, dest_location_raw TEXT,
  start_dt TEXT, end_dt TEXT,
  qty_source REAL, qty_dest REAL, qty_delta REAL,
  source_file TEXT, source_sheet TEXT
);
CREATE TABLE closing_stock (
  id INTEGER PRIMARY KEY,
  as_of_date TEXT,
  ore_stream TEXT,
  location_raw TEXT,
  balance_mt REAL,
  stock_type TEXT,
  source_file TEXT
);
CREATE TABLE locations (
  canonical_name TEXT PRIMARY KEY,
  location_type TEXT,
  state TEXT, country TEXT,
  lat REAL, lon REAL,
  geocode_source TEXT,          -- manual / nominatim / NULL
  geocode_note TEXT,            -- OSM display_name or manual note (auditable)
  notes TEXT
);
CREATE TABLE location_aliases (
  raw_name TEXT PRIMARY KEY,
  canonical_name TEXT REFERENCES locations(canonical_name)
);
CREATE VIEW v_trip_canon AS
SELECT t.*, sa.canonical_name AS src_canon, da.canonical_name AS dst_canon
FROM trips t
LEFT JOIN location_aliases sa ON sa.raw_name = t.source_location_raw
LEFT JOIN location_aliases da ON da.raw_name = t.dest_location_raw;
CREATE VIEW v_routes AS
SELECT src_canon AS source, dst_canon AS destination, ore_stream,
       COUNT(*) AS trips,
       ROUND(SUM(qty_dest),1) AS tonnage,
       ROUND(SUM(qty_source),1) AS tonnage_src,
       ROUND(SUM(qty_dest) - SUM(qty_source),1) AS delta,
       MIN(start_dt) AS first_trip, MAX(start_dt) AS last_trip
FROM v_trip_canon
WHERE src_canon IS NOT NULL AND dst_canon IS NOT NULL
GROUP BY 1,2,3;
CREATE VIEW v_location_flows AS
WITH flow AS (
  SELECT dst_canon loc, ore_stream, qty_dest q, 1 is_in FROM v_trip_canon WHERE dst_canon IS NOT NULL
  UNION ALL
  SELECT src_canon loc, ore_stream, qty_source q, 0 is_in FROM v_trip_canon WHERE src_canon IS NOT NULL
)
SELECT loc AS location, ore_stream,
  SUM(is_in) AS trips_in,
  ROUND(SUM(CASE WHEN is_in=1 THEN q END),1) AS tonnage_in,
  SUM(1-is_in) AS trips_out,
  ROUND(SUM(CASE WHEN is_in=0 THEN q END),1) AS tonnage_out,
  ROUND(SUM(CASE WHEN is_in=1 THEN q ELSE -q END),1) AS net_tonnage
FROM flow GROUP BY 1,2;
CREATE VIEW v_location_map AS
SELECT l.canonical_name AS location, l.location_type, l.state, l.country,
       l.lat AS latitude, l.lon AS longitude, l.geocode_source,
       (SELECT ROUND(SUM(qty_dest),0)   FROM v_trip_canon WHERE dst_canon=l.canonical_name) AS tonnage_in,
       (SELECT ROUND(SUM(qty_source),0) FROM v_trip_canon WHERE src_canon=l.canonical_name) AS tonnage_out
FROM locations l WHERE l.lat IS NOT NULL;
CREATE VIEW v_permit_trips AS
SELECT p.permit_no, p.software_era, p.ore_stream, p.category, p.permit_type,
       p.issue_date, p.org_name, p.mineral_type, p.grade_slab, p.permit_qty,
       p.source_location_raw, p.dest_location_raw,
       t.trips, ROUND(t.tonnage,1) AS tonnage_moved
FROM permits p
LEFT JOIN (SELECT permit_no, COUNT(*) trips, SUM(qty_dest) tonnage FROM trips GROUP BY 1) t
       ON t.permit_no = p.permit_no
WHERE p.is_superseded = 0;
CREATE VIEW v_monthly AS
SELECT substr(start_dt,1,7) AS month, ore_stream,
       COUNT(*) AS trips, ROUND(SUM(qty_dest),1) AS tonnage
FROM trips WHERE start_dt IS NOT NULL GROUP BY 1,2;
CREATE VIEW v_closing_stock AS
SELECT cs.as_of_date, cs.ore_stream,
       COALESCE(la.canonical_name, cs.location_raw) AS location, cs.stock_type,
       ROUND(SUM(cs.balance_mt),1) AS balance_mt
FROM closing_stock cs LEFT JOIN location_aliases la ON la.raw_name = cs.location_raw
GROUP BY 1,2,3,4;
CREATE INDEX ix_permits_no ON permits(permit_no);
CREATE INDEX ix_trips_permit ON trips(permit_no);
CREATE INDEX ix_trips_src ON trips(source_location_raw);
CREATE INDEX ix_trips_dst ON trips(dest_location_raw);
CREATE TABLE location_balance (
  ore_stream TEXT, location TEXT, location_type TEXT,
  has_2024_baseline INTEGER,
  opening_2024 REAL,
  trips_in INTEGER, tons_in REAL, trips_out INTEGER, tons_out REAL,
  net_all REAL,
  min_running_balance REAL, min_balance_dt TEXT,
  reconstructed_2025 REAL, reported_2025 REAL, recon_diff REAL,
  goes_negative INTEGER
);
CREATE INDEX ix_locbal ON location_balance(ore_stream, location);
CREATE TABLE routes(
  source TEXT,
  destination TEXT,
  ore_stream TEXT,
  trips,
  tonnage,
  tonnage_src,
  delta,
  first_trip,
  last_trip
);
CREATE TABLE location_flows(
  location TEXT,
  ore_stream TEXT,
  trips_in,
  tonnage_in,
  trips_out,
  tonnage_out,
  net_tonnage
);
CREATE TABLE location_map(
  location TEXT,
  location_type TEXT,
  state TEXT,
  country TEXT,
  latitude REAL,
  longitude REAL,
  geocode_source TEXT,
  tonnage_in,
  tonnage_out
);
CREATE TABLE monthly(
  month,
  ore_stream TEXT,
  trips,
  tonnage
)