USE mkconstruction;

CREATE TABLE IF NOT EXISTS blog_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS blog_post_tags (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES blog_tags(id) ON DELETE CASCADE
);

-- Insert some dummy tags if they don't exist
INSERT IGNORE INTO blog_tags (name, slug) VALUES 
('Tips & Tricks', 'tips-tricks'),
('Guides', 'guides'),
('Construction', 'construction'),
('Architecture', 'architecture'),
('Trends', 'trends'),
('Design', 'design'),
('Renovation', 'renovation');
