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

CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    tax_number VARCHAR(100),
    national_id VARCHAR(100),
    address VARCHAR(255),
    phone VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE declarations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    declaration_number VARCHAR(100) NOT NULL UNIQUE,
    company_id INT NOT NULL,
    user_id INT NOT NULL,
    port VARCHAR(100),
    status ENUM('draft','submitted','under_review','released','rejected') DEFAULT 'draft',
    total_value DECIMAL(18,2),
    currency VARCHAR(10) DEFAULT 'USD',
    risk_level ENUM('low','medium','high') DEFAULT 'low',
    has_sensitive_goods TINYINT(1) DEFAULT 0,
    is_high_risk_country TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (company_id) REFERENCES companies(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE declaration_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    declaration_id INT NOT NULL,
    hs_code VARCHAR(20) NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(18,3) NOT NULL,
    unit VARCHAR(50),
    unit_price DECIMAL(18,2),
    total_price DECIMAL(18,2),
    origin_country VARCHAR(100),
    FOREIGN KEY (declaration_id) REFERENCES declarations(id) ON DELETE CASCADE
);

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    declaration_id INT NOT NULL,
    doc_type VARCHAR(50),
    file_path VARCHAR(255),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (declaration_id) REFERENCES declarations(id) ON DELETE CASCADE
);

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    declaration_id INT NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    payment_method ENUM('bank','card','cash') DEFAULT 'bank',
    payment_ref VARCHAR(150),
    paid_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (declaration_id) REFERENCES declarations(id) ON DELETE CASCADE
);

CREATE TABLE activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
