-- Table: account
CREATE TABLE account (
 account_id SERIAL PRIMARY KEY,
 username VARCHAR(50) NOT NULL UNIQUE,
 password_hash TEXT NOT NULL,
 email VARCHAR(100),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 account_type VARCHAR(20), -- can be 'user', 'admin', or 'superadmin'
 is_active BOOLEAN NOT NULL DEFAULT TRUE,
 admin_id INT,
 FOREIGN KEY (admin_id) REFERENCES account (account_id) ON DELETE SET NULL
);
-- For bulk update
CREATE TYPE account_update AS (
 account_id INT,
 username VARCHAR(50),
 password_hash TEXT,
 email VARCHAR(100),
 account_type VARCHAR(20),
 is_active BOOLEAN,
 admin_id INT
);
-- For bulk update
CREATE OR REPLACE FUNCTION bulk_update_accounts(admin_id INT, accounts account_update[])
RETURNS VOID AS $$
BEGIN
 UPDATE account AS a
 SET
 username = COALESCE(u.username, a.username),
 password_hash = COALESCE(u.password_hash, a.password_hash),
 email = COALESCE(u.email, a.email),
 account_type = COALESCE(u.account_type, a.account_type),
 is_active = COALESCE(u.is_active, a.is_active)
 FROM UNNEST(accounts) AS u
 WHERE a.account_id = u.account_id
 AND a.admin_id = admin_id;
END;
$$ LANGUAGE plpgsql;

-- name: BulkUpdateAccounts :exec
SELECT bulk_update_accounts($1, $2);

const bulkUpdateAccounts = `-- name: BulkUpdateAccounts :exec
SELECT bulk_update_accounts(1,ドル 2ドル)
`
type BulkUpdateAccountsParams struct {
	AdminID int32 `json:"adminId"`
	Accounts string `json:"accounts"` // Account is of string type instead of a slice of struct
}
func (q *Queries) BulkUpdateAccounts(ctx context.Context, arg BulkUpdateAccountsParams) error {
	_, err := q.db.Exec(ctx, bulkUpdateAccounts, arg.AdminID, arg.Accounts)
	return err
}

-- name: BulkUpdateAccounts :exec
UPDATE account AS a
SET
username = COALESCE(u.username, a.username),
password_hash = COALESCE(u.password_hash, a.password_hash),
email = COALESCE(u.email, a.email),
account_type = COALESCE(u.account_type, a.account_type),
is_active = COALESCE(u.is_active, a.is_active),
admin_id = COALESCE(u.admin_id, a.admin_id)
FROM
UNNEST(sqlc.arg('accounts')::account_update[]) AS u
WHERE
a.account_id = u.account_id
AND a.admin_id = sqlc.arg('admin_id');