-- schema.sql – FeatureBoard V4


CREATE TABLE feature_statuses (
  id TINYINT UNSIGNED PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  sort_order TINYINT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO feature_statuses (id,name,sort_order) VALUES
 (1,'Backlog',1),
 (2,'Planned',2),
 (3,'In Progress',3),
 (4,'Released',4),
 (5,'Rejected',5)
ON DUPLICATE KEY UPDATE name=VALUES(name), sort_order=VALUES(sort_order);

CREATE TABLE categories (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL UNIQUE,
  sort_order INT UNSIGNED NOT NULL DEFAULT 100
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO categories (name,sort_order) VALUES
 ('General',10),
 ('UI/UX',20),
 ('Integrations',30)
ON DUPLICATE KEY UPDATE name=VALUES(name);

CREATE TABLE features (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(190) NOT NULL,
  description TEXT,
  author_id BIGINT UNSIGNED NOT NULL,
  status_id TINYINT UNSIGNED NOT NULL DEFAULT 1,
  category_id INT UNSIGNED NULL,
  duplicate_of BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status_id),
  INDEX idx_author (author_id),
  INDEX idx_category (category_id),
  INDEX idx_duplicate_of (duplicate_of),
  CONSTRAINT fk_features_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_features_status FOREIGN KEY (status_id) REFERENCES feature_statuses(id),
  CONSTRAINT fk_features_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_features_duplicate FOREIGN KEY (duplicate_of) REFERENCES features(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE feature_votes (
  feature_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (feature_id, user_id),
  CONSTRAINT fk_votes_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE,
  CONSTRAINT fk_votes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE feature_comments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  feature_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_comments_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

