CREATE DATABASE IF NOT EXISTS voting_tally
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE voting_tally;

CREATE TABLE IF NOT EXISTS meetings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  meeting_at DATETIME NOT NULL,
  location VARCHAR(255) NOT NULL,
  chair VARCHAR(255) NOT NULL,
  returning_officer VARCHAR(255) NOT NULL,
  meeting_reconvene TINYINT(1) NOT NULL DEFAULT 0,
  has_open_ballot TINYINT(1) NOT NULL DEFAULT 0,
  is_locked TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meeting_owners (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  meeting_id INT UNSIGNED NOT NULL,
  owner_name VARCHAR(255) NOT NULL,
  unit VARCHAR(100) NOT NULL,
  lot VARCHAR(100) NOT NULL,
  csle DECIMAL(12,4) NULL,
  isle DECIMAL(12,4) NULL,
  representation VARCHAR(255) NULL,
  levy_balance DECIMAL(12,2) NULL,
  display_order INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT fk_meeting_owners_meeting
    FOREIGN KEY (meeting_id) REFERENCES meetings(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  meeting_id INT UNSIGNED NOT NULL,
  motion_number INT UNSIGNED NOT NULL,
  title VARCHAR(255) NULL,
  resolution_type ENUM('ordinary', 'special', 'resolution_without_dissent') NOT NULL DEFAULT 'ordinary',
  votes_locked TINYINT(1) NOT NULL DEFAULT 0,
  poll_enabled TINYINT(1) NOT NULL DEFAULT 0,
  motion_status ENUM('active', 'ruled_out_of_order', 'withdrawn') NOT NULL DEFAULT 'active',
  motion_status_note TEXT NULL,
  CONSTRAINT fk_motions_meeting
    FOREIGN KEY (meeting_id) REFERENCES meetings(id)
    ON DELETE CASCADE,
  UNIQUE KEY uniq_motion_number (meeting_id, motion_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS votes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  meeting_owner_id INT UNSIGNED NOT NULL,
  motion_id INT UNSIGNED NOT NULL,
  vote ENUM('yes', 'no', 'abstain') NOT NULL,
  CONSTRAINT fk_votes_owner
    FOREIGN KEY (meeting_owner_id) REFERENCES meeting_owners(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_votes_motion
    FOREIGN KEY (motion_id) REFERENCES motions(id)
    ON DELETE CASCADE,
  UNIQUE KEY uniq_owner_motion (meeting_owner_id, motion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meeting_open_ballot_results (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  meeting_id INT UNSIGNED NOT NULL,
  display_order INT UNSIGNED NOT NULL DEFAULT 0,
  position VARCHAR(100) NOT NULL,
  candidate_name VARCHAR(255) NOT NULL,
  candidate_unit_lot VARCHAR(255) NULL,
  vote_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_open_ballot_results_meeting
    FOREIGN KEY (meeting_id) REFERENCES meetings(id)
    ON DELETE CASCADE,
  UNIQUE KEY uniq_open_ballot_result (meeting_id, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meeting_open_ballot_votes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  meeting_id INT UNSIGNED NOT NULL,
  meeting_owner_id INT UNSIGNED NOT NULL,
  candidate_key INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_open_ballot_votes_meeting
    FOREIGN KEY (meeting_id) REFERENCES meetings(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_open_ballot_votes_owner
    FOREIGN KEY (meeting_owner_id) REFERENCES meeting_owners(id)
    ON DELETE CASCADE,
  UNIQUE KEY uniq_open_ballot_vote (meeting_owner_id, candidate_key),
  KEY idx_open_ballot_votes_meeting (meeting_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;