CREATE TABLE IF NOT EXISTS community_campaigns (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  target_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
  collected_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
  deadline DATE,
  status VARCHAR(50) DEFAULT 'active', -- active, closed
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS community_donations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID REFERENCES community_campaigns(id),
  member_id UUID REFERENCES members(id), -- NULL if anonymous from outside, but we assume internal for now
  amount DECIMAL(15, 2) NOT NULL,
  is_anonymous BOOLEAN DEFAULT false,
  message TEXT,
  payment_status VARCHAR(50) DEFAULT 'paid',
  journal_id UUID,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS community_dues_master (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  default_amount DECIMAL(15, 2) NOT NULL,
  billing_cycle VARCHAR(50) DEFAULT 'monthly',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS community_bills (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  due_master_id UUID REFERENCES community_dues_master(id),
  member_id UUID REFERENCES members(id),
  amount DECIMAL(15, 2) NOT NULL,
  billing_month VARCHAR(7), -- YYYY-MM
  status VARCHAR(50) DEFAULT 'unpaid', -- unpaid, paid, overdue
  payment_date TIMESTAMP,
  journal_id UUID,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS community_cash_ledger (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_type VARCHAR(20) NOT NULL, -- 'in', 'out'
  amount DECIMAL(15, 2) NOT NULL,
  balance_after DECIMAL(15, 2) NOT NULL,
  description TEXT,
  reference_id UUID, -- order_id, bill_id, campaign_id
  reference_type VARCHAR(50),
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);