-- ========================================
-- CIVILIAN EMAIL SYSTEM - SYSTEM POCZTY DLA CYWILÓW
-- ========================================
-- Uruchom ten skrypt w phpMyAdmin lub MySQL CLI
-- ========================================

-- ========================================
-- USUNIĘCIE STARYCH TABEL (w odpowiedniej kolejności - FK)
-- ========================================
DROP TABLE IF EXISTS civilian_email_attachments;
DROP TABLE IF EXISTS civilian_contacts;
DROP TABLE IF EXISTS civilian_emails;

-- ========================================
-- GŁÓWNA TABELA EMAILI CYWILNYCH
-- ========================================
CREATE TABLE civilian_emails (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Nadawca
    from_user_id INT NOT NULL,
    from_email VARCHAR(150) NOT NULL,
    from_name VARCHAR(100) NOT NULL,
    
    -- Odbiorca
    to_user_id INT NULL COMMENT 'NULL jeśli email do nieistniejącego użytkownika',
    to_email VARCHAR(150) NOT NULL,
    to_name VARCHAR(100) NULL,
    
    -- Treść
    subject VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    
    -- Typ i priorytet
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    category ENUM('personal', 'work', 'official', 'spam', 'other') DEFAULT 'personal',
    
    -- Odpowiedź na inną wiadomość
    reply_to_id INT NULL,
    thread_id INT NULL COMMENT 'ID pierwszego emaila w wątku',
    
    -- Statusy dla nadawcy
    is_sent BOOLEAN DEFAULT TRUE,
    is_draft BOOLEAN DEFAULT FALSE,
    is_deleted_sender BOOLEAN DEFAULT FALSE,
    deleted_at_sender TIMESTAMP NULL,
    
    -- Statusy dla odbiorcy
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    is_starred BOOLEAN DEFAULT FALSE,
    is_archived BOOLEAN DEFAULT FALSE,
    is_deleted_receiver BOOLEAN DEFAULT FALSE,
    deleted_at_receiver TIMESTAMP NULL,
    
    -- Folder odbiorcy
    folder ENUM('inbox', 'sent', 'drafts', 'trash', 'spam', 'archive') DEFAULT 'inbox',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    scheduled_at TIMESTAMP NULL COMMENT 'Dla zaplanowanych wiadomości',
    
    -- Indeksy
    INDEX idx_from_user (from_user_id),
    INDEX idx_to_user (to_user_id),
    INDEX idx_from_email (from_email),
    INDEX idx_to_email (to_email),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at),
    INDEX idx_thread_id (thread_id),
    INDEX idx_folder (folder)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABELA ZAŁĄCZNIKÓW
-- ========================================
CREATE TABLE civilian_email_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email_id INT NOT NULL,
    
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL COMMENT 'Rozmiar w bajtach',
    mime_type VARCHAR(100) NOT NULL,
    
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_email_id (email_id),
    
    FOREIGN KEY (email_id) REFERENCES civilian_emails(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABELA KONTAKTÓW
-- ========================================
CREATE TABLE civilian_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    
    contact_email VARCHAR(150) NOT NULL,
    contact_name VARCHAR(100) NULL,
    nickname VARCHAR(50) NULL,
    
    is_favorite BOOLEAN DEFAULT FALSE,
    is_blocked BOOLEAN DEFAULT FALSE,
    
    notes TEXT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_contact_email (contact_email),
    UNIQUE KEY unique_contact (user_id, contact_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- PRZYKŁADOWE EMAILE TESTOWE
-- ========================================
-- Wstawiane tylko jeśli istnieją użytkownicy w tabeli civilian_users

INSERT IGNORE INTO civilian_emails (from_user_id, from_email, from_name, to_user_id, to_email, to_name, subject, content, priority, is_read, created_at) 
VALUES (
    1, 'jankowalski@LS.com', 'Jan Kowalski',
    2, 'annnowak@LS.com', 'Anna Nowak',
    'Spotkanie w sprawie projektu', 
    'Cześć Anna,\n\nChciałbym umówić się na spotkanie w sprawie naszego wspólnego projektu. Czy masz czas w tym tygodniu?\n\nPozdrawiam,\nJan',
    'normal', 0, NOW() - INTERVAL 2 DAY
);

INSERT IGNORE INTO civilian_emails (from_user_id, from_email, from_name, to_user_id, to_email, to_name, subject, content, priority, is_read, created_at) 
VALUES (
    2, 'annnowak@LS.com', 'Anna Nowak',
    1, 'jankowalski@LS.com', 'Jan Kowalski',
    'Re: Spotkanie w sprawie projektu', 
    'Cześć Jan,\n\nJasne, mam czas w czwartek po 14:00. Pasuje Ci ta godzina?\n\nPozdrawiam,\nAnna',
    'normal', 1, NOW() - INTERVAL 1 DAY
);

INSERT IGNORE INTO civilian_emails (from_user_id, from_email, from_name, to_user_id, to_email, to_name, subject, content, priority, is_starred, is_read, created_at) 
VALUES (
    3, 'piowisniewski@LS.com', 'Piotr Wiśniewski',
    1, 'jankowalski@LS.com', 'Jan Kowalski',
    'PILNE: Dokumenty do podpisu', 
    'Dzień dobry Panie Janie,\n\nPrzesyłam dokumenty które wymagają pilnego podpisu. Proszę o zwrot do końca dnia.\n\nZ poważaniem,\nPiotr Wiśniewski',
    'urgent', 1, 0, NOW() - INTERVAL 3 HOUR
);

INSERT IGNORE INTO civilian_emails (from_user_id, from_email, from_name, to_user_id, to_email, to_name, subject, content, priority, is_read, created_at) 
VALUES (
    4, 'marzielinska@LS.com', 'Maria Zielińska',
    1, 'jankowalski@LS.com', 'Jan Kowalski',
    'Zaproszenie na urodziny', 
    'Cześć Jan!\n\nZapraszam Cię serdecznie na moje urodziny w sobotę o 18:00. Będzie tort i dobra zabawa!\n\nCzekam na potwierdzenie.\n\nMaria',
    'normal', 0, NOW() - INTERVAL 5 HOUR
);

INSERT IGNORE INTO civilian_emails (from_user_id, from_email, from_name, to_user_id, to_email, to_name, subject, content, priority, is_read, created_at) 
VALUES (
    1, 'jankowalski@LS.com', 'Jan Kowalski',
    3, 'piowisniewski@LS.com', 'Piotr Wiśniewski',
    'Oferta współpracy', 
    'Dzień dobry Panie Piotrze,\n\nPiszę w sprawie potencjalnej współpracy biznesowej. Czy byłby Pan zainteresowany spotkaniem w celu omówienia szczegółów?\n\nZ poważaniem,\nJan Kowalski',
    'high', 0, NOW() - INTERVAL 1 DAY
);

-- ========================================
-- ZAKOŃCZENIE
-- ========================================
SELECT 'Civilian email system created successfully!' AS message;
SELECT COUNT(*) AS emails_count FROM civilian_emails;
