-- 1. Create the database with proper character set for Kurdish
DROP DATABASE IF EXISTS dictionary;
CREATE DATABASE dictionary 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 2. Select the database
USE dictionary;

-- 3. Create word types table (normalized)
CREATE TABLE IF NOT EXISTS word_types (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    INDEX idx_type (type_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Create main words table
CREATE TABLE IF NOT EXISTS words (
    word_id INT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(100) NOT NULL,
    type_id INT NOT NULL,
    meaning TEXT NOT NULL,
    pronunciation VARCHAR(150),
    example_usage TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word (word),
    FULLTEXT INDEX idx_meaning (meaning),
    FOREIGN KEY (type_id) REFERENCES word_types(type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Create synonyms table
CREATE TABLE IF NOT EXISTS synonyms (
    synonym_id INT AUTO_INCREMENT PRIMARY KEY,
    word_id INT NOT NULL,
    synonym_word VARCHAR(100) NOT NULL,
    FOREIGN KEY (word_id) REFERENCES words(word_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Insert initial word types
INSERT INTO word_types (type_name, description) VALUES
('ناو', 'ناوی کەس، شوێن، یان شت'),
('کردار', 'کرداری کردارەوە'),
('خاسیەت', 'تایبەتمەندییەکی کەس یان شت'),
('پێناس', 'پێناسەیەک بۆ شتێک');

-- 7. Insert sample words
INSERT INTO words (word, type_id, meaning, pronunciation, example_usage) VALUES
('کۆمپیوتەر', 1, 'ئامێرێکی ئەلیکترۆنی بۆ خەملاندنی زانیاری', 'kompyuter', 'کۆمپیوتەرەکەم نوێیە'),
('بڵاوکردنەوە', 2, 'کردنی شتێک بۆ خەڵک', 'bławkirdnewe', 'کتێبەکەم بڵاوکردەوە'),
('زمان', 1, 'سیستەمێکی پەیوەندی و ڕاگەیاندن', 'ziman', 'زمانی کوردی زۆر جوانە');

-- 8. Insert some synonyms
INSERT INTO synonyms (word_id, synonym_word) VALUES
(1, 'حاسوب'),
(3, 'زمانی');

-- 9. Create dedicated database user with secure password
DROP USER IF EXISTS 'dict_user'root'localhost';
CREATE USER 'dict_user'root'localhost' IDENTIFIED BY '';
GRANT SELECT, INSERT, UPDATE, DELETE ON dictionary.* TO 'dict_user'@'localhost';
FLUSH PRIVILEGES;