PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS buildings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  address TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS building_settings (
  building_id INTEGER PRIMARY KEY REFERENCES buildings(id) ON DELETE CASCADE,
  late_fee_rate_percent NUMERIC NOT NULL DEFAULT 5 CHECK(late_fee_rate_percent >= 0 AND late_fee_rate_percent <= 100),
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS units (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
  unit_number TEXT NOT NULL,
  floor INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(building_id, unit_number)
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
  full_name TEXT NOT NULL,
  email TEXT UNIQUE,
  phone TEXT,
  password_hash TEXT NOT NULL,
  must_change_password INTEGER NOT NULL DEFAULT 0 CHECK(must_change_password IN (0, 1)),
  role TEXT NOT NULL DEFAULT 'RESIDENT' CHECK(role IN ('SUPER_ADMIN', 'MANAGER', 'RESIDENT')),
  failed_login_attempts INTEGER NOT NULL DEFAULT 0,
  lock_until TEXT,
  reset_token_hash TEXT,
  reset_token_expires TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS unit_memberships (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  unit_id INTEGER NOT NULL REFERENCES units(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  member_type TEXT NOT NULL CHECK(member_type IN ('OWNER', 'TENANT')),
  starts_on TEXT NOT NULL,
  ends_on TEXT,
  UNIQUE(unit_id, user_id, starts_on)
);

CREATE TABLE IF NOT EXISTS categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('INCOME', 'EXPENSE')),
  UNIQUE(building_id, name, type)
);

CREATE TABLE IF NOT EXISTS charges (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  unit_id INTEGER NOT NULL REFERENCES units(id) ON DELETE RESTRICT,
  charge_type TEXT NOT NULL CHECK(charge_type IN ('DUES', 'COMMON_EXPENSE', 'RESERVE', 'LATE_FEE', 'OTHER')),
  target_type TEXT NOT NULL CHECK(target_type IN ('TENANT', 'OWNER', 'UNIT')),
  period_month INTEGER,
  period_year INTEGER,
  description TEXT NOT NULL DEFAULT '',
  principal_amount NUMERIC NOT NULL CHECK(principal_amount >= 0),
  due_date TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'UNPAID' CHECK(status IN ('UNPAID', 'PARTIAL', 'PAID', 'CANCELLED')),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(unit_id, charge_type, period_month, period_year, target_type)
);

CREATE TABLE IF NOT EXISTS payments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER NOT NULL REFERENCES buildings(id) ON DELETE RESTRICT,
  payer_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  amount NUMERIC NOT NULL CHECK(amount > 0),
  payment_method TEXT NOT NULL CHECK(payment_method IN ('CASH', 'BANK_TRANSFER', 'CARD', 'OTHER')),
  paid_on TEXT NOT NULL,
  reference TEXT NOT NULL DEFAULT '',
  receipt_url TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payment_allocations (
  payment_id INTEGER NOT NULL REFERENCES payments(id) ON DELETE CASCADE,
  charge_id INTEGER NOT NULL REFERENCES charges(id) ON DELETE RESTRICT,
  amount NUMERIC NOT NULL CHECK(amount > 0),
  PRIMARY KEY(payment_id, charge_id)
);

CREATE TABLE IF NOT EXISTS transactions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER NOT NULL REFERENCES buildings(id) ON DELETE RESTRICT,
  category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
  type TEXT NOT NULL CHECK(type IN ('INCOME', 'EXPENSE')),
  amount NUMERIC NOT NULL CHECK(amount > 0),
  description TEXT NOT NULL DEFAULT '',
  receipt_url TEXT,
  transaction_date TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS management_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER NOT NULL REFERENCES buildings(id) ON DELETE RESTRICT,
  old_manager_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  new_manager_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  transferred_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS announcements (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  is_important INTEGER NOT NULL DEFAULT 0 CHECK(is_important IN (0, 1)),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER REFERENCES buildings(id) ON DELETE SET NULL,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  action TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id INTEGER,
  metadata_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS polls (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building_id INTEGER NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
  question TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS poll_options (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  poll_id INTEGER NOT NULL REFERENCES polls(id) ON DELETE CASCADE,
  option_text TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS poll_votes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  poll_id INTEGER NOT NULL REFERENCES polls(id) ON DELETE CASCADE,
  option_id INTEGER NOT NULL REFERENCES poll_options(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  unit_id INTEGER NOT NULL REFERENCES units(id) ON DELETE CASCADE,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(poll_id, unit_id)
);

CREATE INDEX IF NOT EXISTS idx_units_building ON units(building_id);
CREATE INDEX IF NOT EXISTS idx_users_building_role ON users(building_id, role);
CREATE INDEX IF NOT EXISTS idx_charges_unit_status ON charges(unit_id, status);
CREATE INDEX IF NOT EXISTS idx_payments_building_date ON payments(building_id, paid_on);
CREATE INDEX IF NOT EXISTS idx_audit_logs_building_created ON audit_logs(building_id, created_at);
CREATE INDEX IF NOT EXISTS idx_polls_building_expires ON polls(building_id, expires_at);