-- install/schema_stage4.sql
-- Ensure all required tables exist

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL UNIQUE,
  full_name VARCHAR(200) NULL,
  role ENUM('ceo','accountant','dep_manager','scenario','editor','admin','customer') DEFAULT 'customer',
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contracts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  amount INT NOT NULL,
  status VARCHAR(32) DEFAULT 'waiting_sign',
  created_at DATETIME NOT NULL,
  INDEX(customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  contract_id INT NULL,
  amount INT NOT NULL,
  status VARCHAR(16) DEFAULT 'pending',
  due_date DATE NULL,
  created_at DATETIME NOT NULL,
  INDEX(customer_id), INDEX(contract_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_chat_id BIGINT NOT NULL,
  invoice_id INT NULL,
  file_id VARCHAR(200) NOT NULL,
  status VARCHAR(16) DEFAULT 'pending',
  created_at DATETIME NOT NULL,
  INDEX(invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS offish (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  date_fa VARCHAR(16) NOT NULL,
  hour VARCHAR(8) NOT NULL,
  status ENUM('pending','approved','rejected','done') DEFAULT 'pending',
  start_ts INT NULL,
  arrive_ts INT NULL,
  finish_ts INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  owner_user_id INT NOT NULL,
  role ENUM('scenario','editor','admin') NOT NULL,
  title VARCHAR(255) NOT NULL,
  status ENUM('open','done','late') DEFAULT 'open',
  due_ts INT NOT NULL,
  created_ts INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS penalties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('public','manual') NOT NULL,
  amount INT NOT NULL,
  status ENUM('need_acc','need_ceo','approved','rejected') DEFAULT 'need_acc',
  reason VARCHAR(500) DEFAULT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
