

-- Designs Table
CREATE TABLE IF NOT EXISTS designs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    design_name TEXT NOT NULL,
    design_data TEXT,
    home_jersey_data TEXT,
    away_jersey_data TEXT,
    pants_data TEXT,
    helmet_data TEXT,
    status TEXT DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER,
    last_modified_by INTEGER,
    is_public INTEGER DEFAULT 1,
    approved_by INTEGER,
    approval_date DATETIME,
    digital_signature TEXT,
    rejection_reason TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (last_modified_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES admins(id)
);

-- Templates Table
CREATE TABLE IF NOT EXISTS templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    svg_file_path TEXT NOT NULL,
    is_active INTEGER DEFAULT 1,
    is_default INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Fonts Table
CREATE TABLE IF NOT EXISTS fonts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    file_path TEXT NOT NULL,
    preview_text TEXT DEFAULT 'The Quick Brown Fox',
    is_active INTEGER DEFAULT 1,
    font_category TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Color Palettes Table
CREATE TABLE IF NOT EXISTS color_palettes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    colors TEXT NOT NULL,
    is_default INTEGER DEFAULT 0,
    category TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Uniform Styles Table
CREATE TABLE IF NOT EXISTS uniform_styles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    is_active INTEGER DEFAULT 1,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Order Forms Table
CREATE TABLE IF NOT EXISTS order_forms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    design_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    order_data TEXT,
    total_quantity INTEGER DEFAULT 0,
    is_locked INTEGER DEFAULT 0,
    locked_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    edit_history TEXT DEFAULT '[]',
    FOREIGN KEY (design_id) REFERENCES designs(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (locked_by) REFERENCES admins(id)
);

-- Disabled Numbers Table
CREATE TABLE IF NOT EXISTS disabled_numbers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    number INTEGER NOT NULL,
    disabled_by INTEGER,
    disabled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    reason TEXT,
    FOREIGN KEY (disabled_by) REFERENCES admins(id)
);

-- Messages Table
CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    from_user_id INTEGER,
    to_admin_id INTEGER,
    message_text TEXT NOT NULL,
    design_id INTEGER,
    is_read INTEGER DEFAULT 0,
    sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    read_at DATETIME,
    FOREIGN KEY (from_user_id) REFERENCES users(id),
    FOREIGN KEY (to_admin_id) REFERENCES admins(id),
    FOREIGN KEY (design_id) REFERENCES designs(id)
);

-- Design Votes Table
CREATE TABLE IF NOT EXISTS design_votes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    design_id INTEGER NOT NULL,
    share_link_id INTEGER,
    piece_type TEXT NOT NULL,
    vote_type TEXT NOT NULL,
    voter_identifier TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (design_id) REFERENCES designs(id),
    FOREIGN KEY (share_link_id) REFERENCES share_links(id)
);

-- Design Comments Table
CREATE TABLE IF NOT EXISTS design_comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    design_id INTEGER NOT NULL,
    share_link_id INTEGER,
    commenter_name TEXT,
    comment_text TEXT NOT NULL,
    is_visible INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (design_id) REFERENCES designs(id),
    FOREIGN KEY (share_link_id) REFERENCES share_links(id)
);

-- Notifications Log Table
CREATE TABLE IF NOT EXISTS notifications_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    admin_id INTEGER,
    type TEXT NOT NULL,
    method TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    message_content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (admin_id) REFERENCES admins(id)
);

-- Design Version History Table
CREATE TABLE IF NOT EXISTS design_versions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    design_id INTEGER NOT NULL,
    version_number INTEGER NOT NULL,
    design_data TEXT,
    saved_by INTEGER,
    saved_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (design_id) REFERENCES designs(id),
    FOREIGN KEY (saved_by) REFERENCES users(id)
);

-- Insert Default Uniform Styles
INSERT OR IGNORE INTO uniform_styles (name, is_active, description) VALUES 
    ('Tackle Football', 1, 'Traditional tackle football uniforms'),
    ('Air Raid 7s', 0, 'Air Raid 7s style uniforms (coming soon)');

