CREATE DATABASE IF NOT EXISTS website_rt CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE website_rt;

CREATE TABLE IF NOT EXISTS residents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nik VARCHAR(255) NULL,
  name VARCHAR(255) NOT NULL,
  kk_number VARCHAR(255) NULL,
  address TEXT NULL,
  phone VARCHAR(255) NULL,
  email VARCHAR(255) NULL,
  is_head_of_family TINYINT(1) NOT NULL DEFAULT 0,
  status VARCHAR(255) NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS officials (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  position VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NULL,
  email VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dues (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  resident_id BIGINT UNSIGNED NOT NULL,
  period VARCHAR(7) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ronda_fine DECIMAL(12,2) NOT NULL DEFAULT 0,
  jimpitan_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  paid_at TIMESTAMP NULL,
  status ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
  note TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY dues_resident_period_unique (resident_id, period),
  CONSTRAINT dues_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS programs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  plan_detail LONGTEXT NULL,
  progress LONGTEXT NULL,
  report LONGTEXT NULL,
  budget DECIMAL(14,2) NOT NULL DEFAULT 0,
  status VARCHAR(255) NOT NULL DEFAULT 'planned',
  start_date DATE NULL,
  end_date DATE NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS letter_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tracking_code VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  nik VARCHAR(255) NOT NULL,
  address TEXT NOT NULL,
  phone VARCHAR(255) NOT NULL,
  email VARCHAR(255) NULL,
  letter_type VARCHAR(255) NOT NULL,
  purpose TEXT NOT NULL,
  status VARCHAR(255) NOT NULL DEFAULT 'submitted',
  admin_note TEXT NULL,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS complaints (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tracking_code VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  address TEXT NOT NULL,
  phone VARCHAR(255) NOT NULL,
  email VARCHAR(255) NULL,
  category VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  description LONGTEXT NOT NULL,
  status VARCHAR(255) NOT NULL DEFAULT 'submitted',
  admin_note TEXT NULL,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ronda_schedules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  resident_id BIGINT UNSIGNED NOT NULL,
  schedule_date DATE NOT NULL,
  shift VARCHAR(255) NOT NULL DEFAULT 'Malam',
  location VARCHAR(255) NULL,
  jimpitan_note TEXT NULL,
  status VARCHAR(255) NOT NULL DEFAULT 'scheduled',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT ronda_schedules_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;