-- =====================================================================
-- MySujog — MySQL schema + seed data
-- Import this file via cPanel → phpMyAdmin → (select your database) → Import
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  bio TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- wallets
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS wallets (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL UNIQUE,
  balance_available BIGINT NOT NULL DEFAULT 0,   -- minor units (poisha)
  balance_pending BIGINT NOT NULL DEFAULT 0,
  currency VARCHAR(8) NOT NULL DEFAULT 'BDT',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_wallets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- wallet_transactions (insert-only ledger — application code never
-- UPDATEs the amount, only the status, and every status change should be
-- paired with an audit_logs row)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS wallet_transactions (
  id CHAR(36) PRIMARY KEY,
  wallet_id CHAR(36) NOT NULL,
  type ENUM('job_payment','withdrawal','refund','campaign_reward') NOT NULL,
  amount BIGINT NOT NULL,                          -- positive=credit, negative=debit
  status ENUM('pending','processing','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  reference_type VARCHAR(40) NULL,
  reference_id VARCHAR(64) NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_txn_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
  INDEX idx_txn_wallet (wallet_id),
  INDEX idx_txn_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- jobs
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS jobs (
  id CHAR(36) PRIMARY KEY,
  poster_id CHAR(36) NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  category VARCHAR(80) NOT NULL,
  budget_amount BIGINT NOT NULL,
  currency VARCHAR(8) NOT NULL DEFAULT 'BDT',
  deadline DATE NULL,
  location VARCHAR(120) NULL,
  remote TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('open','in_review','hired','completed','closed','removed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_jobs_poster FOREIGN KEY (poster_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_jobs_status (status),
  INDEX idx_jobs_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- job_applications
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS job_applications (
  id CHAR(36) PRIMARY KEY,
  job_id CHAR(36) NOT NULL,
  applicant_id CHAR(36) NOT NULL,
  cover_note TEXT NULL,
  status ENUM('applied','shortlisted','hired','rejected','withdrawn') NOT NULL DEFAULT 'applied',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_app_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_app_user FOREIGN KEY (applicant_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_app_job (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- reports
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reports (
  id CHAR(36) PRIMARY KEY,
  reporter_id CHAR(36) NULL,
  target_type VARCHAR(40) NOT NULL,
  target_id VARCHAR(64) NOT NULL,
  reason VARCHAR(500) NOT NULL,
  status ENUM('open','resolved','dismissed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL,
  resolved_at DATETIME NULL,
  CONSTRAINT fk_reports_user FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- disputes
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS disputes (
  id CHAR(36) PRIMARY KEY,
  job_id CHAR(36) NULL,
  raised_by CHAR(36) NULL,
  reason VARCHAR(500) NOT NULL,
  status ENUM('open','resolved') NOT NULL DEFAULT 'open',
  resolution VARCHAR(500) NULL,
  created_at DATETIME NOT NULL,
  resolved_at DATETIME NULL,
  CONSTRAINT fk_disputes_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL,
  CONSTRAINT fk_disputes_user FOREIGN KEY (raised_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- audit_logs (append-only; application code never UPDATEs/DELETEs rows here)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_logs (
  id CHAR(36) PRIMARY KEY,
  actor_id CHAR(36) NULL,
  action VARCHAR(80) NOT NULL,
  target_type VARCHAR(40) NULL,
  target_id VARCHAR(64) NULL,
  metadata TEXT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- cms_banner / cms_categories (content the admin dashboard controls)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cms_banner (
  id TINYINT PRIMARY KEY,
  greeting_bn VARCHAR(120) NULL,
  headline_bn VARCHAR(200) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_categories (
  id CHAR(36) PRIMARY KEY,
  name_bn VARCHAR(80) NOT NULL,
  color VARCHAR(9) NOT NULL DEFAULT '#FF5A2E',
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- Seed data
-- Demo admin login:  01900000000 / admin123
-- Demo user logins:  01711111111 / password123
--                    01722222222 / password123
--
-- Passwords below are bcrypt hashes of the plaintext passwords above,
-- generated with PHP's password_hash(). Change/remove these accounts
-- before a real launch.
-- =====================================================================

INSERT INTO users (id, name, phone, password_hash, role, status, bio, created_at, updated_at) VALUES
('a0000000-0000-4000-8000-000000000001', 'MySujog Admin', '01900000000', '$2b$12$H9LP9f5fI0ELApLn.9eO4O/2v8ZxhLfcbBNnIMpx9Y7pz7ZXZtMQC', 'admin', 'active', '', NOW(), NOW()),
('a0000000-0000-4000-8000-000000000002', 'রাফিয়া হক', '01711111111', '$2b$12$kYgKLKHDiM7J1OCCcF8hpeYWXd7uGzHAGGFC3.lP0lmIiNgOFpqHe', 'user', 'active', '', NOW(), NOW()),
('a0000000-0000-4000-8000-000000000003', 'আরিফ হোসেন', '01722222222', '$2b$12$3jibViBZsJws6j/H2dmmZOFXt7VZ7zziOJyeNqViAsnG.veOhjWze', 'user', 'active', '', NOW(), NOW());

INSERT INTO wallets (id, user_id, balance_available, balance_pending, currency, created_at, updated_at) VALUES
('b0000000-0000-4000-8000-000000000001', 'a0000000-0000-4000-8000-000000000001', 0, 0, 'BDT', NOW(), NOW()),
('b0000000-0000-4000-8000-000000000002', 'a0000000-0000-4000-8000-000000000002', 50000, 0, 'BDT', NOW(), NOW()),
('b0000000-0000-4000-8000-000000000003', 'a0000000-0000-4000-8000-000000000003', 0, 0, 'BDT', NOW(), NOW());

INSERT INTO jobs (id, poster_id, title, description, category, budget_amount, currency, deadline, location, remote, status, created_at, updated_at) VALUES
('c0000000-0000-4000-8000-000000000001', 'a0000000-0000-4000-8000-000000000003', 'প্রোডাক্ট রিভিউ লিখুন', 'একটি স্কিনকেয়ার প্রোডাক্ট ব্যবহার করে সৎ রিভিউ লিখুন এবং ২টি ছবি সংযুক্ত করুন।', 'প্রোডাক্ট রিভিউ', 50000, 'BDT', NULL, 'অনলাইন', 1, 'open', NOW(), NOW());

INSERT INTO job_applications (id, job_id, applicant_id, cover_note, status, created_at, updated_at) VALUES
('d0000000-0000-4000-8000-000000000001', 'c0000000-0000-4000-8000-000000000001', 'a0000000-0000-4000-8000-000000000002', 'আমি আগ্রহী, আজই শুরু করতে পারি।', 'applied', NOW(), NOW());

INSERT INTO wallet_transactions (id, wallet_id, type, amount, status, reference_type, reference_id, note, created_at, updated_at) VALUES
('e0000000-0000-4000-8000-000000000001', 'b0000000-0000-4000-8000-000000000002', 'job_payment', 50000, 'completed', 'job', 'c0000000-0000-4000-8000-000000000001', 'ডেমো পেমেন্ট', NOW(), NOW());

INSERT INTO cms_banner (id, greeting_bn, headline_bn, active, updated_at) VALUES
(1, 'শুভ সকাল 👋', 'আজ আপনার জন্য নতুন সুযোগ আছে', 1, NOW());

INSERT INTO cms_categories (id, name_bn, color, sort_order) VALUES
('f0000000-0000-4000-8000-000000000001', 'ডিজাইন', '#7C3AED', 0),
('f0000000-0000-4000-8000-000000000002', 'প্রোডাক্ট রিভিউ', '#DB2777', 1),
('f0000000-0000-4000-8000-000000000003', 'ডেটা এন্ট্রি', '#2563EB', 2),
('f0000000-0000-4000-8000-000000000004', 'কন্টেন্ট', '#059669', 3),
('f0000000-0000-4000-8000-000000000005', 'ফটোগ্রাফি', '#B45309', 4),
('f0000000-0000-4000-8000-000000000006', 'অনুবাদ', '#4F46E5', 5),
('f0000000-0000-4000-8000-000000000007', 'মার্কেটিং', '#E11D48', 6);