USE ilga;

-- Senate Bills Table
CREATE TABLE IF NOT EXISTS senate_bills (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bill_id VARCHAR(10) NOT NULL,  -- e.g., SB0001
    original_name TEXT NOT NULL,    -- e.g., "SB0001     $GEN ASSEMBLY-TECH"
    bill_name VARCHAR(10) NOT NULL, -- e.g., "SB0001"
    description TEXT,              -- e.g., "$GEN ASSEMBLY-TECH"
    category VARCHAR(50) NOT NULL,  -- Bills, Resolutions, etc.
    url TEXT NOT NULL,
    session_id INT NOT NULL,       -- 1 for Regular, 2 for Special
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_bill (bill_id, category, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- House Bills Table
CREATE TABLE IF NOT EXISTS house_bills (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bill_id VARCHAR(10) NOT NULL,  -- e.g., HB0001
    original_name TEXT NOT NULL,    
    bill_name VARCHAR(10) NOT NULL,
    description TEXT,
    category VARCHAR(50) NOT NULL,
    url TEXT NOT NULL,
    session_id INT NOT NULL,       -- 1 for Regular, 2 for Special
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_bill (bill_id, category, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Other Bills Table
CREATE TABLE IF NOT EXISTS other_bills (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bill_id VARCHAR(20) NOT NULL,  -- e.g., EO2501, JSR0001, AM1030246
    original_name TEXT NOT NULL,
    bill_name VARCHAR(20) NOT NULL,
    description TEXT,
    category VARCHAR(50) NOT NULL,  -- Executive Orders, Joint Session Resolutions, Appointment Messages
    url TEXT NOT NULL,
    session_id INT NOT NULL,       -- 1 for Regular, 2 for Special
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_bill (bill_id, category, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 