-- School Division Monitoring System — raw SQL schema
-- Matches the Laravel migrations in database/migrations/
-- Import this via phpMyAdmin if you don't want to run `php artisan migrate`.
-- Assumes MySQL 5.7+/8 with utf8mb4 support (default on XAMPP).

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

-- ---------------------------------------------------------
-- divisions
-- ---------------------------------------------------------
CREATE TABLE divisions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(255) NOT NULL UNIQUE,
  contact_person VARCHAR(255) NULL,
  contact_email VARCHAR(255) NULL,
  contact_phone VARCHAR(255) NULL,
  address TEXT NULL,
  logo_path VARCHAR(255) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- users
-- ---------------------------------------------------------
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  email_verified_at TIMESTAMP NULL,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','division_head') NOT NULL DEFAULT 'division_head',
  division_id BIGINT UNSIGNED NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  remember_token VARCHAR(100) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_users_division FOREIGN KEY (division_id) REFERENCES divisions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE password_reset_tokens (
  email VARCHAR(255) PRIMARY KEY,
  token VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sessions (
  id VARCHAR(255) PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  user_agent TEXT NULL,
  payload LONGTEXT NOT NULL,
  last_activity INT NOT NULL,
  INDEX sessions_user_id_index (user_id),
  INDEX sessions_last_activity_index (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- submission_types
-- ---------------------------------------------------------
CREATE TABLE submission_types (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  is_recurring TINYINT(1) NOT NULL DEFAULT 0,
  frequency ENUM('monthly','quarterly','annual','once') NOT NULL DEFAULT 'once',
  deadline_day TINYINT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- submissions
-- ---------------------------------------------------------
CREATE TABLE submissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  division_id BIGINT UNSIGNED NOT NULL,
  submission_type_id BIGINT UNSIGNED NULL,
  submitted_by BIGINT UNSIGNED NOT NULL,
  verified_by BIGINT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  period VARCHAR(255) NULL,
  disk VARCHAR(255) NOT NULL DEFAULT 'local',
  file_path VARCHAR(255) NOT NULL,
  original_filename VARCHAR(255) NOT NULL,
  mime_type VARCHAR(255) NULL,
  file_size BIGINT UNSIGNED NULL,
  status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  admin_remarks TEXT NULL,
  submitted_at TIMESTAMP NULL,
  verified_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX submissions_division_status_index (division_id, status),
  CONSTRAINT fk_sub_division FOREIGN KEY (division_id) REFERENCES divisions(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_type FOREIGN KEY (submission_type_id) REFERENCES submission_types(id) ON DELETE SET NULL,
  CONSTRAINT fk_sub_submitter FOREIGN KEY (submitted_by) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_verifier FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Seed data (matches database/seeders/DatabaseSeeder.php)
-- Password for both accounts below is: ChangeMe123!
-- Hash below is a real bcrypt hash of that password — change it immediately after first login.
-- ---------------------------------------------------------
INSERT INTO divisions (id, name, code, contact_person, contact_email, status, created_at, updated_at)
VALUES (1, 'Sample Division Office', 'DIV-001', 'Juan Dela Cruz', 'sample.division@yourdivision.gov.ph', 'active', NOW(), NOW());

INSERT INTO users (name, email, password, role, division_id, status, created_at, updated_at)
VALUES
('System Administrator', 'admin@yourdivision.gov.ph', '$2b$12$nCLpAJzSdKQu9aArUsoia.HGrFI5pWF4QHqrkkp28YHzcAprXImyS', 'admin', NULL, 'active', NOW(), NOW()),
('Juan Dela Cruz', 'sample.division@yourdivision.gov.ph', '$2b$12$nCLpAJzSdKQu9aArUsoia.HGrFI5pWF4QHqrkkp28YHzcAprXImyS', 'division_head', 1, 'active', NOW(), NOW());