-- FINAL schema (core)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL UNIQUE,
  role ENUM('ceo','accountant','dep_manager','admin_master','scenario','blogger','videographer','editor','customer') NOT NULL DEFAULT 'customer',
  full_name VARCHAR(128) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  k VARCHAR(64) PRIMARY KEY,
  v TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contracts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  service_code VARCHAR(32) NOT NULL,
  title VARCHAR(128) NOT NULL,
  amount BIGINT NOT NULL,
  status ENUM('draft','signed','cancelled') NOT NULL DEFAULT 'signed',
  file_path VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES users(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 NOT NULL,
  amount BIGINT NOT NULL,
  status ENUM('pending','partially_paid','paid','rejected') NOT NULL DEFAULT 'pending',
  due_date DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES users(id),
  FOREIGN KEY (contract_id) REFERENCES contracts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  method ENUM('card_to_card','gateway') NOT NULL DEFAULT 'card_to_card',
  receipt_file VARCHAR(255) DEFAULT NULL,
  amount BIGINT DEFAULT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id)
) 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','auto') NOT NULL,
  amount BIGINT NOT NULL,
  reason VARCHAR(255) DEFAULT NULL,
  status ENUM('pending','approved','rejected','auto_applied') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  manager_id INT NOT NULL,
  customer_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (manager_id) REFERENCES users(id),
  FOREIGN KEY (customer_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS team_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  member_id INT NOT NULL,
  role ENUM('dep_manager','scenario','blogger','videographer','editor','admin') NOT NULL,
  FOREIGN KEY (team_id) REFERENCES teams(id),
  FOREIGN KEY (member_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS offish (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  date DATE NOT NULL,
  time_from TIME NOT NULL,
  time_to TIME NOT NULL,
  location VARCHAR(255) DEFAULT NULL,
  status ENUM('scheduled','done','cancelled') NOT NULL DEFAULT 'scheduled',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings Defaults
INSERT IGNORE INTO settings (k,v) VALUES
('company_name','شرکت البرز مدیا دوراندیش (ویژن مدیا)'),
('company_reg_no','۴۲۵۶۳'),
('company_national_id','۱۴۰۱۱۱۲۸۲۲۲'),
('company_address','کرج ، جهانشهر، میدان سپاه ، مجتمع تجاری گلستان'),
('company_phone','09331903872,09981381737,02634425880,02191305883'),
('company_website','https://www.visionmedia.ir'),
('card_number','5859838820334401'),
('account_number','03590300021'),
('iban','IR410180000000103590300021'),
('penalty_public','250000'),
('penalty_manual_options','50000,100000,300000,500000,1000000'),
('payment_gateway_enabled','0'),
('sms_enabled','0');

-- Bootstrap Staff
INSERT IGNORE INTO users (telegram_id, role, full_name) VALUES
(894457439,'ceo','مدیرعامل'),
(403409192,'scenario','سیده زهرا مجیدی'),
(620344705,'videographer','آریا کمالی'),
(1945384893,'scenario','امیرحسین دانش نیا'),
(1055485720,'admin_master','شیدا سلیمی سیزکوهی'),
(287098219,'dep_manager','میثم امجدیان'),
(162160538,'dep_manager','آیدا لطفی');
