

CREATE TABLE IF NOT EXISTS daily_entries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  entry_date TEXT NOT NULL UNIQUE,
  reception_cash REAL NOT NULL DEFAULT 0,
  pharmacy_cash REAL NOT NULL DEFAULT 0,
  lab_cash REAL NOT NULL DEFAULT 0,
  upi_bank REAL NOT NULL DEFAULT 0,
  total_expenses REAL NOT NULL DEFAULT 0,
  agency_bills REAL NOT NULL DEFAULT 0,
  notes TEXT,
  created_by INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS agencies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  contact TEXT,
  notes TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS agency_bills (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agency_id INTEGER NOT NULL,
  bill_date TEXT NOT NULL,
  amount REAL NOT NULL,
  description TEXT,
  document_url TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agency_id) REFERENCES agencies(id)
);

CREATE TABLE IF NOT EXISTS agency_settlements (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agency_id INTEGER NOT NULL,
  settlement_date TEXT NOT NULL,
  amount REAL NOT NULL,
  settlement_month TEXT NOT NULL,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agency_id) REFERENCES agencies(id)
);

CREATE TABLE IF NOT EXISTS outflow_categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  category_type TEXT NOT NULL, -- 'operational', 'personal', 'fixed'
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS outflows (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  category_id INTEGER NOT NULL,
  outflow_date TEXT NOT NULL,
  amount REAL NOT NULL,
  notes TEXT,
  created_by INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES outflow_categories(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS documents (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  filename TEXT NOT NULL,
  original_name TEXT NOT NULL,
  file_type TEXT NOT NULL,
  file_size INTEGER,
  agency_id INTEGER,
  upload_date TEXT NOT NULL,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agency_id) REFERENCES agencies(id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_daily_entries_date ON daily_entries(entry_date);
CREATE INDEX IF NOT EXISTS idx_agency_bills_agency ON agency_bills(agency_id);
CREATE INDEX IF NOT EXISTS idx_agency_bills_date ON agency_bills(bill_date);
CREATE INDEX IF NOT EXISTS idx_agency_settlements_agency ON agency_settlements(agency_id);
CREATE INDEX IF NOT EXISTS idx_outflows_date ON outflows(outflow_date);
CREATE INDEX IF NOT EXISTS idx_outflows_category ON outflows(category_id);
CREATE INDEX IF NOT EXISTS idx_documents_agency ON documents(agency_id);

-- Seed default outflow categories
INSERT OR IGNORE INTO outflow_categories (name, category_type, sort_order) VALUES
  ('Salaries', 'operational', 1),
  ('Maintenance', 'operational', 2),
  ('Electricity (EB)', 'operational', 3),
  ('Home', 'personal', 10),
  ('Appa', 'personal', 11),
  ('Amma', 'personal', 12),
  ('Wife', 'personal', 13),
  ('Rent', 'fixed', 20),
  ('Maid', 'fixed', 21),
  ('Watchman', 'fixed', 22),
  ('Miscellaneous', 'fixed', 23);
