-- MySQL schema for Partner Portal
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('partner','admin') NOT NULL DEFAULT 'partner',
  company_name VARCHAR(190),
  lang VARCHAR(5) DEFAULT 'en',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE offers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50),
  user_id INT,
  status ENUM('new','ongoing','accepted','won','paid','cancelled') DEFAULT 'accepted',
  salutation VARCHAR(20),
  language VARCHAR(10),
  approx_volume_m3 DECIMAL(6,2),
  excl_boxes_m3 DECIMAL(6,2),
  recommended_total_m3 DECIMAL(6,2),
  move_date DATE,
  move_start TIME,
  postal_from VARCHAR(10),
  postal_to VARCHAR(10),
  payment_method VARCHAR(50),
  compensation_eur DECIMAL(10,2),
  winner_flag TINYINT(1) DEFAULT 0,
  last_updated_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE offer_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  offer_id INT NOT NULL,
  sender_type ENUM('admin','partner') NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (offer_id) REFERENCES offers(id) ON DELETE CASCADE
);

CREATE TABLE offer_documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  offer_id INT NOT NULL,
  filename VARCHAR(255),
  original_name VARCHAR(255),
  mime VARCHAR(100),
  size INT,
  uploaded_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (offer_id) REFERENCES offers(id) ON DELETE CASCADE
);

CREATE TABLE offer_expenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  offer_id INT NOT NULL,
  title VARCHAR(190),
  amount_eur DECIMAL(10,2) NOT NULL,
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (offer_id) REFERENCES offers(id) ON DELETE CASCADE
);

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(100) NOT NULL,
  user_id INT NOT NULL,
  offer_id INT NULL,
  email VARCHAR(190) NOT NULL,
  amount_eur DECIMAL(10,2) NOT NULL,
  date DATE NOT NULL,
  pdf_path VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE penalties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  points DECIMAL(6,2) NOT NULL DEFAULT 0,
  reason VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
