-- نظام دخول وخروج الوسائل البحرية
USE qanas_f;

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS movements;
DROP TABLE IF EXISTS vessels;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(100) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(255) DEFAULT NULL UNIQUE,
 role VARCHAR(30) NOT NULL DEFAULT 'admin',
 reset_token_hash VARCHAR(255) DEFAULT NULL,
 reset_expires DATETIME DEFAULT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE vessels (
 id INT AUTO_INCREMENT PRIMARY KEY,
 vessel_number VARCHAR(50) NOT NULL UNIQUE,
 vessel_name VARCHAR(150) NOT NULL,
 vessel_type VARCHAR(100) DEFAULT NULL,
 registration_number VARCHAR(100) DEFAULT NULL,
 owner_name VARCHAR(150) DEFAULT NULL,
 phone VARCHAR(50) DEFAULT NULL,
 barcode VARCHAR(100) NOT NULL UNIQUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE movements (
 id INT AUTO_INCREMENT PRIMARY KEY,
 vessel_id INT NOT NULL,
 movement_type ENUM('ENTRY','EXIT') NOT NULL,
 movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 user_id INT DEFAULT NULL,
 notes TEXT DEFAULT NULL,
 CONSTRAINT fk_movement_vessel FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE CASCADE,
 CONSTRAINT fk_movement_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;