-- Song Sheets module tables

CREATE TABLE IF NOT EXISTS song_sheets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  owner_type ENUM('connexion','diocese','circuit') NOT NULL,
  owner_id INT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  file_path VARCHAR(255) NOT NULL,
  preview_image VARCHAR(255) NULL,
  status ENUM('draft','published') NOT NULL DEFAULT 'published',
  created_by INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (owner_type, owner_id),
  INDEX (status),
  INDEX (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS song_sheet_purchases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  song_sheet_id INT NOT NULL,
  user_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_status ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  payment_reference VARCHAR(100) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (song_sheet_id),
  INDEX (user_id),
  INDEX (payment_status),
  CONSTRAINT fk_purchase_song FOREIGN KEY (song_sheet_id) REFERENCES song_sheets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
