-- 裝修工程管理系統資料庫結構
CREATE DATABASE IF NOT EXISTS renovation_mgmt DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE renovation_mgmt;

-- 管理員表
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'staff') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 員工表
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    id_card_path VARCHAR(255),
    safety_card_path VARCHAR(255),
    phone VARCHAR(20),
    salary_type ENUM('daily', 'monthly') DEFAULT 'daily',
    salary_amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 判工表
CREATE TABLE IF NOT EXISTS subcontractors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    id_card_path VARCHAR(255),
    safety_card_path VARCHAR(255),
    phone VARCHAR(20),
    salary_type ENUM('daily', 'monthly') DEFAULT 'daily',
    salary_amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 地盤表
CREATE TABLE IF NOT EXISTS sites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location VARCHAR(255) NOT NULL,
    unit VARCHAR(100),
    status ENUM('active', 'completed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 開工/判工登記表
CREATE TABLE IF NOT EXISTS work_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    worker_type ENUM('employee', 'subcontractor') NOT NULL,
    worker_id INT NOT NULL,
    site_id INT NOT NULL,
    work_date DATE NOT NULL,
    work_type VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 帳單報銷表
CREATE TABLE IF NOT EXISTS bills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_id INT NOT NULL,
    bill_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    image_path VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 地盤文件表
CREATE TABLE IF NOT EXISTS site_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_id INT NOT NULL,
    parent_id INT DEFAULT 0, -- 0 表示根目錄
    is_folder TINYINT(1) DEFAULT 0, -- 1 為資料夾, 0 為文件
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) DEFAULT NULL, -- 資料夾不需要路徑
    file_type VARCHAR(50) DEFAULT NULL,
    file_size BIGINT DEFAULT 0, -- 文件大小(bytes)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 報價公司表
CREATE TABLE IF NOT EXISTS quote_companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    logo_path VARCHAR(255),
    stamp_path VARCHAR(255),
    address TEXT,
    phone VARCHAR(20),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 報價類別表 (用於報價設定)
CREATE TABLE IF NOT EXISTS quote_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL, -- 如：保護、清拆、泥水等
    item_name VARCHAR(255), -- 子項目名稱
    unit_price DECIMAL(10, 2),
    unit_type VARCHAR(50), -- 如：一呎、一項、一個
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 報價單表
CREATE TABLE IF NOT EXISTS quotations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    client_address VARCHAR(255) NOT NULL,
    quote_no VARCHAR(50) NOT NULL UNIQUE,
    contact_person VARCHAR(100),
    phone VARCHAR(20),
    project_name VARCHAR(255),
    quote_date DATE NOT NULL,
    total_amount DECIMAL(12, 2) DEFAULT 0,
    status ENUM('draft', 'sent', 'accepted', 'rejected') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES quote_companies(id)
) ENGINE=InnoDB;

-- 報價單項目明細表
CREATE TABLE IF NOT EXISTS quotation_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quotation_id INT NOT NULL,
    category_name VARCHAR(100),
    description TEXT,
    quantity DECIMAL(10, 2),
    price DECIMAL(10, 2),
    subtotal DECIMAL(12, 2),
    FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 發票表
CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quotation_id INT NOT NULL,
    invoice_no VARCHAR(50) NOT NULL UNIQUE,
    amount DECIMAL(12, 2) NOT NULL,
    percentage INT NOT NULL CHECK (percentage BETWEEN 1 AND 100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 收據表
CREATE TABLE IF NOT EXISTS receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quotation_id INT NOT NULL,
    receipt_no VARCHAR(50) NOT NULL UNIQUE,
    amount DECIMAL(12, 2) NOT NULL,
    percentage INT NOT NULL CHECK (percentage BETWEEN 1 AND 100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 插入初始管理員帳號 (密碼: admin123)
INSERT INTO admins (username, password) VALUES ('admin', '$2y$10$8u.S.fI6uO8.V.mG8z.G.O.G.O.G.O.G.O.G.O.G.O.G.O.G.O.G.O'); -- 密碼為 admin123 (雜湊值會由 reset.php 重新生成更準確的)
