-- Signature Manager Schema
-- Land Title Texas - Self-hosted email signature system

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

CREATE TABLE sig_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE sig_employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    title VARCHAR(150) DEFAULT '',
    department VARCHAR(100) DEFAULT '',
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) DEFAULT '',
    mobile VARCHAR(50) DEFAULT '',
    photo_file VARCHAR(255) DEFAULT '',
    linkedin_url VARCHAR(500) DEFAULT '',
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE sig_admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default settings
INSERT INTO sig_settings (setting_key, setting_value) VALUES
('company_name', 'Land Title Texas'),
('company_website', 'https://landtitletexas.com'),
('company_phone', ''),
('company_address_line1', ''),
('company_address_line2', ''),
('logo_file', 'logo.png'),
('primary_color', '#1B3A5C'),
('secondary_color', '#C8A961'),
('base_url', 'https://landtitletexas.com/signatures'),
('facebook_url', ''),
('instagram_url', ''),
('linkedin_url', '');

-- Admin user created by setup.php (run after importing schema)
