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

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  buyer_name VARCHAR(255) NOT NULL,
  address TEXT,
  phone VARCHAR(64),
  invoice_date DATE NOT NULL,
  invoice_no VARCHAR(64) NOT NULL,
  total_amount DECIMAL(15,2) DEFAULT 0,
  html_snapshot MEDIUMTEXT,
  pdf_blob LONGBLOB,
  pdf_mime VARCHAR(64),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  tax_rate DECIMAL(5,2) DEFAULT 0,
  discount DECIMAL(15,2) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  row_no INT NOT NULL,
  description VARCHAR(255) NOT NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 1,
  price DECIMAL(15,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(15,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- اگر کاربر admin وجود ندارد، ایجادش کن
INSERT INTO users (username, password_hash)
SELECT 'admin', '$2y$10$w.G/WF4S21ztIfGHr.Ja5eFPxMLL7V0kxlJ5fh.SjV/RMDi2HJqtS'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE username = 'admin'
);
